Can I Insert Line Numbers?

Hi. We are creating long reports that need to have unique reference numbers
assigned to each row. For example, the values 1-99 below would be computed at
report run time:
1 Transaction-A
2 Transaction-B
:
99 Transaction-x
I thought I could solve this by using a global variable and function within
the report code block such as:
Private Dim itemCount As Integer
Public Function GetCount() As Integer
itemCount += 1
Return itemCount
End Function
And then create a calculated field (Called LineNumber) that calls the
function:
=Code.GetCount()
But what I get is a seemingly random assignment of line numbers; I assume
due to ReptSvcs resolving the rows in a non-linear fashion.
Any thoughts on how I can get ordered line numbers?Try using static variables in the code
>--Original Message--
>Hi. We are creating long reports that need to have unique
reference numbers
>assigned to each row. For example, the values 1-99 below
would be computed at
>report run time:
>1 Transaction-A
>2 Transaction-B
> :
>99 Transaction-x
>I thought I could solve this by using a global variable
and function within
>the report code block such as:
>Private Dim itemCount As Integer
>Public Function GetCount() As Integer
> itemCount += 1
> Return itemCount
>End Function
>And then create a calculated field (Called LineNumber)
that calls the
>function:
>=Code.GetCount()
>But what I get is a seemingly random assignment of line
numbers; I assume
>due to ReptSvcs resolving the rows in a non-linear
fashion.
>Any thoughts on how I can get ordered line numbers?
>
>
>.
>|||The problem with statics in embedded code is that they are shared
among all instances of the report that are running. If two of the
reports using the static execute at the same time the results could
interleave.
--
Scott
http://www.OdeToCode.com
On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
wrote:
>Try using static variables in the code|||Thanks for the tips, but I think I got it working.
I used the same code as I mentioned below, but added an "ORDER BY"
quailifier to the dataset to sort the data in the same sequence as the report
displayed. This gave me sequentual numbers.
"Scott Allen" wrote:
> The problem with statics in embedded code is that they are shared
> among all instances of the report that are running. If two of the
> reports using the static execute at the same time the results could
> interleave.
> --
> Scott
> http://www.OdeToCode.com
> On Sat, 4 Sep 2004 11:51:17 -0700, "Ravi" <ravikantkv@.rediffmail.com>
> wrote:
> >Try using static variables in the code
>sql

Can I create a database schema from a DataSet and/or .xsd file?

I have some questions about creating SQL Server CE databases. Based on my experiments and what I've read on these forums, it looks like there are a couple ways to create a database schema. I can edit the database schema via the Server Explorer in Visual Studio, or use an external program like SQL Server Management Studio and somehow convert those files to .sdf files.

I find Visual Studio's built in tools to be cumbersome to use and limited in functionality, and using SSMS seems like a roundabout way of approaching the problem. I understand Microsoft will be releasing better tools with Orcas, but in the meantime, I'm wondering if there are alternative ways to generate database schemas.

For instance, I find Visual Studio's DataSet designer fairly easy to use. The DataSet designer generates schema definitions (.xsd files), and an instantiated DataSet can both read and write schema definitions via Read/WriteXMLSchema. Furthermore, DataAdapter's Fill and FillSchema methods can be used to push a schema from a database to a DataSet. So, can I somehow go the other direction and push a schema from a DataSet to a database? It seems like all the tools are there...

For example, if I create the DataSet schema, could I use a small app to create a new .sdf file, instantiate a DataSet, write the schema from the DataSet to the database, and then save the .sdf file? Or given the generated .xsd file, is there any way to create a SQL database from that?

Thanks in advance for any replies.

You can do that by loading schema into DataSet and looping through DataSet's tables, columns, relations, etc., generating respective SQL command and executing them. E.g. you loop through tables in DataSet and execute "Create Table" command for each.

I would not say it’s easier than using designers but by all means you can do it.

|||

Thank you for the reply, Ilya. I agree that doesn't necessarily sound easier than using designers.

If you would indulge me, perhaps you can answer a related and probably more appropriate question. What options are available for creating databases at run time?

The samples I've seen in the SDK and online assume that an application will have access to an existing database. But what if the application is responsible for creating the database? For example, I was using Microsoft Money a few minutes ago, and it occurred to me that such an application would be well suited for SQL Server CE. At its heart, the content is just a relational database. I can imagine Money's .mny files just being rebranded .sdf files. But Money doesn't ship with an existing database. Rather, it allows you to create your own via a traditional File->New command. I imagine the code would look something like the following in CE:

String connString = "Data Source='NewDatabaseFile.sdf'";

SqlCeEngine engine = new SqlCeEngine( connString );

engine.CreateDatabase();

But then what? You have an emtpy file and a database with no schema. If the answer is to run a bunch of CREATE TABLE commands or loop through a DataSet like your example above, then so be it. It would be a shame, however, if there were no way to leverage the database design tools available when creating databases at runtime.

I apologize if I am being naive with these questions. I am new to CE, and am just trying to get a sense of what can be done with it.

Thanks.

|||

Yes, you need to run appropriate SQL command to create schema at runtime after you’ve created the database.

If you need runtime designers then you'd need to implement these designers yourself. They would translate the schema in whatever form to... yes, SQL commands.

sql

Can I change from Integrated Security?

When creating my database, I initially set it up to use Integrated Security (Windows Login), however I am having a hard time logging into the database on a consistent basis using this method. Can I now change to a specified user id and password? How would I do that? Would I lose any data in my database? Does the user id and password need to be the same user id and password used when logging into SQL Server?

Thank you.

The Authetication method is global to the server and not specific to the database.

When you change your authentication to mixed mode

The SA account becomes the most powerful account in the Sql server box

and can login in to all the databases in the server by default.

there are no data loss in the change.

reset the password of your SA account and makeuse of it.

|||

You can change the SQL Server security model to allow a SQL Server login

In the Object Explorer window, right-click on the Server, and then click on the [Security] page. Change to SQL Server and Windows Authentication mode. Then click [OK].

However, it is not conisdered a good practice to use the [sa] account. It represents a significant security risk. It is preferred to create a login for your purposes, and provide just enough permissions for that login to do its work.

Lesson 7: Understanding Security and Network Connectivity -from this series may be of help in understanding how to use SQL Server security.

SQL Server 2005 Express Video Learning
http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

Can create access form layout based on table from sql?

Hi,

Can this be done? Please advise. Thanks.Is there any template available in sql for creating forms?

YOu will have to use any coding language for this like the .NET language. If you are aware of that, which platform will you use for accessing the data (Windows Forms, Web Forms, etc.). SQL Server does ot provide such a feature beside the table editor.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Can any body help me for creating user datatype

I am new to sql2005... how can i create user defined datatype containing different types
ex : address with
name as varchar.
id as integeryou can find the details regarding your question here or here
Hope that solves your problem.

Calling Visual Foxpro 7 COM

Dear Experts,

We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).

We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.

The Automation manager connection doesn't release all the connections.

What effects is this having to our SQL Server 2005 installation and how can we avoid it?

We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.

Thank you for your time.

Regards,

Spyros Christodoulou

You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,

Thanks for your reply.

Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.

The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)

For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.

Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?

Regards,

Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,

Thanks for your reply,

I found a post you made at microsoft.public.sqlserver.programming on august of 2001.

"I have used this. It works perfectly fine. Are you making a EXE? Does it

have UI? Make sure it doesn't have any of those. Here is one sample:

NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE

NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE

-- Now run this on the SQL Server

declare @.o int, @.h int, @.p varchar( 255 )

exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.o

exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.p

exec sp_oadestroy @.o"

I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)

Do you know how to make it an out of process and called it based on the following scenario:

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

Any help will be greatly appreciated.

Thanks again,

Spyros Christodoulou

|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||

Hi Umachandar and Spyros:

I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.

The remaining problem is also similar as the one Spyros described:

"(...)

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

(...)"

In server 1, I have the COM+ application o package correctly installed and working since years

In Clients computers various front end consume the server classes via, for example in VB

CreateObject('AppMastervs.cOrga', mtsServer1)

or in VFP

CreateObjectEx('AppMastervs.cOrga', mtsServer1)

These clients has registered the corresponding .vbr & tlb via CliReg32

I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5

Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.

So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.

Thanks in advance;

Claudio Facundo Lacivita

Calling Visual Foxpro 7 COM

Dear Experts,

We are creating instances of COM Objects written in Visual FoxPro 7 in our SQL Server 2005 stored procedures.
The problem is that VFP Automation Manager which shows number of connections to number of objects is showing 3 connections to 3 objects(instead 1 to 1).

We are releasing the object in SQL Server 2005 using the sp_OADestroy @.Object but the Automation Manager leaves 1 connection to 1 object.

The Automation manager connection doesn't release all the connections.

What effects is this having to our SQL Server 2005 installation and how can we avoid it?

We noticed that when we stop the SQL Service, connections to the Automation Manager are also released.

Thank you for your time.

Regards,

Spyros Christodoulou

You could stop the OLE Automation execution environment itself by calling sp_OAStop. Note that this affects the execution environment at the server-level. Generally, you should avoid using OLE automation extensively since it is resource intensive and running these in-proc can destabilize the server. Try creating the ole server out-of-proc for better reliability with some performance penalty. What is the reason for calling the VFP object from TSQL? Is this absoultely necessary? Note that you can perform queries against VFP databases/tables using linked servers. This is something you should look at also.|||Dear Umachandar,

Thanks for your reply.

Unfortunately we cannot stop OLE Automation execution environment because other users may executing OLE Methods as well.

The reason we are calling VFP from TSQL is that we need VFP to export some files.
I know that we could use a linked server, but in our case it doesn't apply because of the following:
1) VFP database and tables are changing a lot and we don't want the SQL team to be involved every time the VFP team changes tables required for the Import in SQL.
2) The export process is very complicated and it may need to call 2 or 3 VFP methods which means on a change we need to look into these methods as well. (Both VFP and SQL Teams)

For these reasons and the fact that it is much faster to develop the export method in VFP it is better to call VFP method to export data for SQL server.

Have you tried calling VFP out-of-proc COM objects? Does it properly release memory when you call sp_OADestroy @.Object ?

Regards,

Spyros Christodoulou
|||I was suggesting out-of-proc instantiation for better reliability/isolation on the server-side. Some of this might be just behavior of OLE automation. Can you try similar test from say VB and see if the connections are released properly after the OLE object is destroyed?|||Hi Umachandar,

Thanks for your reply,

I found a post you made at microsoft.public.sqlserver.programming on august of 2001.

"I have used this. It works perfectly fine. Are you making a EXE? Does it

have UI? Make sure it doesn't have any of those. Here is one sample:

NOTE -- Save this to a file called "VFPOLE.PRG" in "C:\TEMP"
DEFINE CLASS vfpole AS Custom OLEPUBLIC
prop1 = 'VFPOLE'
ENDDEFINE

NOTE Run these in the VFP command window
CD C:\Temp
BUILD PROJECT VFPOLE FROM VFPOLE.PRG
BUILD DLL VFPOLE FROM VFPOLE

-- Now run this on the SQL Server

declare @.o int, @.h int, @.p varchar( 255 )

exec @.h = sp_OACreate 'VFPOLE.VFPOLE', @.o out

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.o

exec @.h = sp_OAGetProperty @.o, 'Prop1', @.p OUT

if @.@.error|@.h <> 0 exec sp_displayoaerrorinfo @.o, @.h

print @.p

exec sp_oadestroy @.o"

I tried it and it works as an in-process OLE server. It seems that it concumes some SQL Server memory every time I run it. How to register and call the dll if it resides on a different server. (like when you register the VFP .vbr file using clireg32.exe)

Do you know how to make it an out of process and called it based on the following scenario:

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

Any help will be greatly appreciated.

Thanks again,

Spyros Christodoulou

|||The third parameter to sp_OACreate specifies the context for the OLE server. If you specify it as 4 then out-of-proc activation will take place. See Books Online for more details on the parameter.|||

Hi Umachandar and Spyros:

I am trying to do something very similar in SQL Server 2000 with VFP 8 objects.

The remaining problem is also similar as the one Spyros described:

"(...)

Server 1: VFP Database
Server 2: SQL Server

I need to run (SP on Server 2) the dll or exe using Ole Automation on Server 1. (Server 2 will have share access on Server 1)

(...)"

In server 1, I have the COM+ application o package correctly installed and working since years

In Clients computers various front end consume the server classes via, for example in VB

CreateObject('AppMastervs.cOrga', mtsServer1)

or in VFP

CreateObjectEx('AppMastervs.cOrga', mtsServer1)

These clients has registered the corresponding .vbr & tlb via CliReg32

I also registered the server classes in the Server 2 (the one with SQLServer) and it work fine with the Front ends, but was impossible to me to create the class in SQLServer using

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,1

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,4

EXEC @.hr = sp_OACreate 'AppMastervs.cOrga', @.object OUT ,5

Then I copied and registered the class as local in the the Server 2 (as COM+ app in Component services) and then it worked fine (with the 3rd parameter in 4 or 5), but the problem is that must not be done beacuse this carges the sqlServer, generate unnecesary traffic and dificults the server update process.

So, please let me know if is really possible to create, in SQL Server 2000, a server class working as COM+ application in other server, without register it locally as COM+ Application.

Thanks in advance;

Claudio Facundo Lacivita