Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?
You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.c om...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...
|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.c om...

Create procedure in target servers

I am creating a job in master server where in one step, it creates
stored procedure in target server. The proc text is exceeding the
limit to directly paste in job scheduler. What is the best way to push
procedure to target servers?You can either split the sproc into smaller ones to bypass the text size
limit, or save the proc in a text file and use osql in the job to call the
input file.
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407130929.62a38b86@.posting.google.com...
> I am creating a job in master server where in one step, it creates
> stored procedure in target server. The proc text is exceeding the
> limit to directly paste in job scheduler. What is the best way to push
> procedure to target servers?|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...|||Thanks for the reply. OSQL could be used, but I need to copy the sql
to every server. It doesn't take if it is located at central server.
Any ideas?
"Richard Ding" <rding@.acadian-asset.com> wrote in message news:<eJ6GcnQaEHA.3664@.TK2MSFTNGP1
2.phx.gbl>...[vbcol=seagreen]
> You can either split the sproc into smaller ones to bypass the text size
> limit, or save the proc in a text file and use osql in the job to call the
> input file.
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407130929.62a38b86@.posting.google.com...

Create new database as a user instance

Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...

hi,

yse you can, but this does not mean the created database is detached... it's attached and available...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;")

con.Open()

Dim cmd As New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "CREATE DATABASE UserInstanceDatabase;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

con.ChangeDatabase("UserInstanceDatabase")

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "SELECT 1 AS [Id], 'Test' AS [Name] INTO dbo.Table1;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "SELECT * FROM dbo.Table1;"

.Connection = con

End With

Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

While rdr.Read

For iField As Integer = 0 To rdr.FieldCount - 1

Debug.WriteLine(rdr(iField))

Next

Debug.WriteLine("")

End While

rdr.Close()

rdr = Nothing

cmd.Dispose()

cmd = Nothing

con.ChangeDatabase("master")

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "DROP DATABASE UserInstanceDatabase;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

con.Dispose()

con = Nothing

End Sub

you can use the created database as soon as you created it..

BTW, the here used CREATE DATABASE syntax does not use the full CREATE DATABASE syntax to specify the actual files position, so they are created in the "standard" Data folder of the SQLExpress instance and not in the user's folder... you have to use the full syntax accordingly to your needs is you like the database to be "placed" in your account's folder..

regards

|||

hi and thanks for your help.

Your sample does not create a user instance but a plain attached database.

This means that is there is another database with the same name, I will get an error.

Aren't I right?

Isn't there a way to create the database without having it attached?

Thanks again...

|||

hi,

papadi wrote:

hi and thanks for your help.

Your sample does not create a user instance but a plain attached database.

nope... it creates a user instance bound to an existing database, in this case the master system database...

This means that is there is another database with the same name, I will get an error.

Aren't I right?

yep... you're rigth... but if you already have a database with the required name, you should not worry about creating it... you can just co right ahead and use it...

so, in a kinda of scenario, you start your application in your application's master database context (not the system master database), using the AttachDbFileName=|DataDirectory|\yourMasterApplicationDatabase.Mdf syntax of your connection string... you then verify the "alternate" database is available and eventually attach it (as at user instance start up it will be eventually available, but not attached) or create it if the physical files are not available...

at next application start up, you use again the AttachDbFileName=|DataDirectory|\yourMasterApplicationDatabase.Mdf syntax for the main connection and AttachDbFileName=Directory_Of\yourAdditionalDatabase.Mdf syntax for the additional database... I'd check for it's presence before opening the connection, but this should be the way to go..

Isn't there a way to create the database without having it attached?

nope..

SQL Server databases are not just files, they are a set of files bound to a logical, registered database in the master (instance system database) database.. you eventually have to create one and detach it ...

regards

|||

papadi wrote:

Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...

why u need this....r u sure u want to create database per user ?..... or u want tables per user....

Madhu

|||

Dimitrius,

Could you give a bit more information about what you are trying to accomplish and why you want to use User Instances? User Instances are only available to a single user and you cannon connect to them remotely. If you are storing data for a single user application then User Instances may be right for you, but if you want to share your data, then you don't want to use User Instances.

One way to create a User Instance is simple to create a database as part of a VS project. Use the Add New Item functionality to create a database. This automatically creates the database in a User Instance and saves the database into your application project so that it gets deployed along with your code.

Mike

|||

Hi Mike,

I'm creating a database installer for a client/server application. The installer will run on server and I want to provide the end user (administrator of the application) with the option to create a database registered under an sql server instance (the classic way) or create a database by providing a path for an mdf file where the database will be created as a user instance. I know this database is accessible only by the local machine. This is no problem since only server code (ASP.NET Web Services app) can access the database.

So... one option would be to create an empty database and copy it if the end-user selects the option to create a user instance. The I could appy sql scripts to create my database. I dont want the database to already contain anything since I want my installer to be generic.

But what I'm actually looking for is... that code that creates the mdf/ldf pair from scratch, just like visual studio does when you add a database to an application using the 'Add New Item' functionality.

Do you think it's possible?

|||

hi,

papadi wrote:

But what I'm actually looking for is... that code that creates the mdf/ldf pair from scratch, just like visual studio does when you add a database to an application using the 'Add New Item' functionality.

Do you think it's possible?

a database is not just a pair of files... it is created as a logical "object" (in master database system tables) during the "creation" of the physical files and the operation is atomic... the created database is generated reflecting the destination server's "model" database, thus inheriting all settings and saved objects present in this system database... so you can not "just create" the files only...

so, theoretically, your "installer", depending on the user's choices, could connect to the master database of the "user Instance" or "standard instance"... you can then query the master database's physical position via the sys.master_files catalog view, which can return the actual storage file of the db.. you then strip the file name and get the actual folder...

SET NOCOUNT ON;

SELECT REPLACE(physical_name, 'master.mdf','') AS [Data Folder]

FROM sys.master_files

WHERE database_id = 1 AND type = 0;

--<--

Data Folder

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\

--or, for a User Instance, something similar to

C:\Documents and Settings\Andrea\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\

but this is even not mandatory, as when connecting to a user instance you already are in the "relative" "folder" scope, and actually the following is the result..

Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;User Instance=true;")

con.Open()

Dim cmd As New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "CREATE DATABASE UserInstanceDatabase;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "SELECT physical_name FROM sys.master_files WHERE name = 'UserInstanceDatabase';"

.Connection = con

End With

Dim DataFolder As String = cmd.ExecuteScalar()

cmd.Dispose()

cmd = Nothing

Debug.WriteLine(DataFolder)

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "DROP DATABASE UserInstanceDatabase;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

con.Dispose()

con = Nothing

--<-

C:\Documents and Settings\Andrea\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\UserInstanceDatabase.mdf

and the db is created in the actual "User Instance"'s folder... having the User Instance full permissions on that folder you should not experiment permissions problem as well..

if you "turn" to "traditional instances", again, the data folder is respected with the traditional SQLExpress/SQL Server default data folder...

regards

|||

Hi Dimitrius,

What Andrea says is correct with one point of clarification...

The location where a User Instance database is stored changes depending on how you create it. If you handle the creation via SQL tools or using the User Instance connection string into master as Andrea describes above, the database will be created in the folder he specifies. If you create the database using the VS data tools to put the database in your project (Add New Item) the database is actually created in your project folder and deployed along with your application to a per-user file cache created by the VS ClickOnce installer.

Generally this distinction is academic as long as you are not using the |DataDirectory| keyword in your connection string. Using |DataDirectoy| indicates the per user file cache created during a ClickOnce installation, so if you use Andrea's script above, and then call |DataDirectory| as the file path for AttachDbFilename, you will get an error as the file doesn't not exist in that location.

I'm still not certain that User Instances are what you want based on your description. From your description I'm infering that you are creating a Web Service that will read data out of your database. The web service will be running on the same computer where SQL Express is running, so local access should not be an issue. My question is: What advantage do you believe going with User Instances will offer you? Here are my concerns:

A User Instance causes a second process of SQL Express to be running on your computer. When your web service is running, there will be two complete SQL Express instances running on your computer.

Create new database as a user instance

Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...

hi,

yse you can, but this does not mean the created database is detached... it's attached and available...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;")

con.Open()

Dim cmd As New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "CREATE DATABASE UserInstanceDatabase;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

con.ChangeDatabase("UserInstanceDatabase")

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "SELECT 1 AS [Id], 'Test' AS [Name] INTO dbo.Table1;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "SELECT * FROM dbo.Table1;"

.Connection = con

End With

Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

While rdr.Read

For iField As Integer = 0 To rdr.FieldCount - 1

Debug.WriteLine(rdr(iField))

Next

Debug.WriteLine("")

End While

rdr.Close()

rdr = Nothing

cmd.Dispose()

cmd = Nothing

con.ChangeDatabase("master")

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "DROP DATABASE UserInstanceDatabase;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

con.Dispose()

con = Nothing

End Sub

you can use the created database as soon as you created it..

BTW, the here used CREATE DATABASE syntax does not use the full CREATE DATABASE syntax to specify the actual files position, so they are created in the "standard" Data folder of the SQLExpress instance and not in the user's folder... you have to use the full syntax accordingly to your needs is you like the database to be "placed" in your account's folder..

regards

|||

hi and thanks for your help.

Your sample does not create a user instance but a plain attached database.

This means that is there is another database with the same name, I will get an error.

Aren't I right?

Isn't there a way to create the database without having it attached?

Thanks again...

|||

hi,

papadi wrote:

hi and thanks for your help.

Your sample does not create a user instance but a plain attached database.

nope... it creates a user instance bound to an existing database, in this case the master system database...

This means that is there is another database with the same name, I will get an error.

Aren't I right?

yep... you're rigth... but if you already have a database with the required name, you should not worry about creating it... you can just co right ahead and use it...

so, in a kinda of scenario, you start your application in your application's master database context (not the system master database), using the AttachDbFileName=|DataDirectory|\yourMasterApplicationDatabase.Mdf syntax of your connection string... you then verify the "alternate" database is available and eventually attach it (as at user instance start up it will be eventually available, but not attached) or create it if the physical files are not available...

at next application start up, you use again the AttachDbFileName=|DataDirectory|\yourMasterApplicationDatabase.Mdf syntax for the main connection and AttachDbFileName=Directory_Of\yourAdditionalDatabase.Mdf syntax for the additional database... I'd check for it's presence before opening the connection, but this should be the way to go..

Isn't there a way to create the database without having it attached?

nope..

SQL Server databases are not just files, they are a set of files bound to a logical, registered database in the master (instance system database) database.. you eventually have to create one and detach it ...

regards

|||

papadi wrote:

Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...

why u need this....r u sure u want to create database per user ?..... or u want tables per user....

Madhu

|||

Dimitrius,

Could you give a bit more information about what you are trying to accomplish and why you want to use User Instances? User Instances are only available to a single user and you cannon connect to them remotely. If you are storing data for a single user application then User Instances may be right for you, but if you want to share your data, then you don't want to use User Instances.

One way to create a User Instance is simple to create a database as part of a VS project. Use the Add New Item functionality to create a database. This automatically creates the database in a User Instance and saves the database into your application project so that it gets deployed along with your code.

Mike

|||

Hi Mike,

I'm creating a database installer for a client/server application. The installer will run on server and I want to provide the end user (administrator of the application) with the option to create a database registered under an sql server instance (the classic way) or create a database by providing a path for an mdf file where the database will be created as a user instance. I know this database is accessible only by the local machine. This is no problem since only server code (ASP.NET Web Services app) can access the database.

So... one option would be to create an empty database and copy it if the end-user selects the option to create a user instance. The I could appy sql scripts to create my database. I dont want the database to already contain anything since I want my installer to be generic.

But what I'm actually looking for is... that code that creates the mdf/ldf pair from scratch, just like visual studio does when you add a database to an application using the 'Add New Item' functionality.

Do you think it's possible?

|||

hi,

papadi wrote:

But what I'm actually looking for is... that code that creates the mdf/ldf pair from scratch, just like visual studio does when you add a database to an application using the 'Add New Item' functionality.

Do you think it's possible?

a database is not just a pair of files... it is created as a logical "object" (in master database system tables) during the "creation" of the physical files and the operation is atomic... the created database is generated reflecting the destination server's "model" database, thus inheriting all settings and saved objects present in this system database... so you can not "just create" the files only...

so, theoretically, your "installer", depending on the user's choices, could connect to the master database of the "user Instance" or "standard instance"... you can then query the master database's physical position via the sys.master_files catalog view, which can return the actual storage file of the db.. you then strip the file name and get the actual folder...

SET NOCOUNT ON;

SELECT REPLACE(physical_name, 'master.mdf','') AS [Data Folder]

FROM sys.master_files

WHERE database_id = 1 AND type = 0;

--<--

Data Folder

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\

--or, for a User Instance, something similar to

C:\Documents and Settings\Andrea\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\

but this is even not mandatory, as when connecting to a user instance you already are in the "relative" "folder" scope, and actually the following is the result..

Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;User Instance=true;")

con.Open()

Dim cmd As New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "CREATE DATABASE UserInstanceDatabase;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "SELECT physical_name FROM sys.master_files WHERE name = 'UserInstanceDatabase';"

.Connection = con

End With

Dim DataFolder As String = cmd.ExecuteScalar()

cmd.Dispose()

cmd = Nothing

Debug.WriteLine(DataFolder)

cmd = New SqlClient.SqlCommand

With cmd

.CommandType = CommandType.Text

.CommandTimeout = 5

.CommandText = "DROP DATABASE UserInstanceDatabase;"

.Connection = con

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

con.Dispose()

con = Nothing

--<-

C:\Documents and Settings\Andrea\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\UserInstanceDatabase.mdf

and the db is created in the actual "User Instance"'s folder... having the User Instance full permissions on that folder you should not experiment permissions problem as well..

if you "turn" to "traditional instances", again, the data folder is respected with the traditional SQLExpress/SQL Server default data folder...

regards

|||

Hi Dimitrius,

What Andrea says is correct with one point of clarification...

The location where a User Instance database is stored changes depending on how you create it. If you handle the creation via SQL tools or using the User Instance connection string into master as Andrea describes above, the database will be created in the folder he specifies. If you create the database using the VS data tools to put the database in your project (Add New Item) the database is actually created in your project folder and deployed along with your application to a per-user file cache created by the VS ClickOnce installer.

Generally this distinction is academic as long as you are not using the |DataDirectory| keyword in your connection string. Using |DataDirectoy| indicates the per user file cache created during a ClickOnce installation, so if you use Andrea's script above, and then call |DataDirectory| as the file path for AttachDbFilename, you will get an error as the file doesn't not exist in that location.

I'm still not certain that User Instances are what you want based on your description. From your description I'm infering that you are creating a Web Service that will read data out of your database. The web service will be running on the same computer where SQL Express is running, so local access should not be an issue. My question is: What advantage do you believe going with User Instances will offer you? Here are my concerns:

A User Instance causes a second process of SQL Express to be running on your computer. When your web service is running, there will be two complete SQL Express instances running on your computer.

Create new database as a user instance

Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...

hi,

yse you can, but this does not mean the created database is detached... it's attached and available...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;") con.Open() Dim cmd As New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "CREATE DATABASE UserInstanceDatabase;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing con.ChangeDatabase("UserInstanceDatabase") cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "SELECT 1 AS [Id], 'Test' AS [Name] INTO dbo.Table1;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "SELECT * FROM dbo.Table1;" .Connection = con End With Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess) While rdr.Read For iField As Integer = 0 To rdr.FieldCount - 1 Debug.WriteLine(rdr(iField)) Next Debug.WriteLine("") End While rdr.Close() rdr = Nothing cmd.Dispose() cmd = Nothing con.ChangeDatabase("master") cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "DROP DATABASE UserInstanceDatabase;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing con.Dispose() con = Nothing End Sub

you can use the created database as soon as you created it..

BTW, the here used CREATE DATABASE syntax does not use the full CREATE DATABASE syntax to specify the actual files position, so they are created in the "standard" Data folder of the SQLExpress instance and not in the user's folder... you have to use the full syntax accordingly to your needs is you like the database to be "placed" in your account's folder..

regards

|||

hi and thanks for your help.

Your sample does not create a user instance but a plain attached database.

This means that is there is another database with the same name, I will get an error.

Aren't I right?

Isn't there a way to create the database without having it attached?

Thanks again...

|||

hi,

papadi wrote:

hi and thanks for your help.

Your sample does not create a user instance but a plain attached database.

nope... it creates a user instance bound to an existing database, in this case the master system database...

This means that is there is another database with the same name, I will get an error.

Aren't I right?

yep... you're rigth... but if you already have a database with the required name, you should not worry about creating it... you can just co right ahead and use it...

so, in a kinda of scenario, you start your application in your application's master database context (not the system master database), using the AttachDbFileName=|DataDirectory|\yourMasterApplicationDatabase.Mdf syntax of your connection string... you then verify the "alternate" database is available and eventually attach it (as at user instance start up it will be eventually available, but not attached) or create it if the physical files are not available...

at next application start up, you use again the AttachDbFileName=|DataDirectory|\yourMasterApplicationDatabase.Mdf syntax for the main connection and AttachDbFileName=Directory_Of\yourAdditionalDatabase.Mdf syntax for the additional database... I'd check for it's presence before opening the connection, but this should be the way to go..

Isn't there a way to create the database without having it attached?

nope..

SQL Server databases are not just files, they are a set of files bound to a logical, registered database in the master (instance system database) database.. you eventually have to create one and detach it ...

regards

|||

papadi wrote:

Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...

why u need this....r u sure u want to create database per user ?..... or u want tables per user....

Madhu

|||

Dimitrius,

Could you give a bit more information about what you are trying to accomplish and why you want to use User Instances? User Instances are only available to a single user and you cannon connect to them remotely. If you are storing data for a single user application then User Instances may be right for you, but if you want to share your data, then you don't want to use User Instances.

One way to create a User Instance is simple to create a database as part of a VS project. Use the Add New Item functionality to create a database. This automatically creates the database in a User Instance and saves the database into your application project so that it gets deployed along with your code.

Mike

|||

Hi Mike,

I'm creating a database installer for a client/server application. The installer will run on server and I want to provide the end user (administrator of the application) with the option to create a database registered under an sql server instance (the classic way) or create a database by providing a path for an mdf file where the database will be created as a user instance. I know this database is accessible only by the local machine. This is no problem since only server code (ASP.NET Web Services app) can access the database.

So... one option would be to create an empty database and copy it if the end-user selects the option to create a user instance. The I could appy sql scripts to create my database. I dont want the database to already contain anything since I want my installer to be generic.

But what I'm actually looking for is... that code that creates the mdf/ldf pair from scratch, just like visual studio does when you add a database to an application using the 'Add New Item' functionality.

Do you think it's possible?

|||

hi,

papadi wrote:

But what I'm actually looking for is... that code that creates the mdf/ldf pair from scratch, just like visual studio does when you add a database to an application using the 'Add New Item' functionality.

Do you think it's possible?

a database is not just a pair of files... it is created as a logical "object" (in master database system tables) during the "creation" of the physical files and the operation is atomic... the created database is generated reflecting the destination server's "model" database, thus inheriting all settings and saved objects present in this system database... so you can not "just create" the files only...

so, theoretically, your "installer", depending on the user's choices, could connect to the master database of the "user Instance" or "standard instance"... you can then query the master database's physical position via the sys.master_files catalog view, which can return the actual storage file of the db.. you then strip the file name and get the actual folder...

SET NOCOUNT ON; SELECT REPLACE(physical_name, 'master.mdf','') AS [Data Folder] FROM sys.master_files WHERE database_id = 1 AND type = 0; --<-- Data Folder C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\ --or, for a User Instance, something similar to C:\Documents and Settings\Andrea\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\

but this is even not mandatory, as when connecting to a user instance you already are in the "relative" "folder" scope, and actually the following is the result..

Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;User Instance=true;") con.Open() Dim cmd As New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "CREATE DATABASE UserInstanceDatabase;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "SELECT physical_name FROM sys.master_files WHERE name = 'UserInstanceDatabase';" .Connection = con End With Dim DataFolder As String = cmd.ExecuteScalar() cmd.Dispose() cmd = Nothing Debug.WriteLine(DataFolder) cmd = New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "DROP DATABASE UserInstanceDatabase;" .Connection = con End With cmd.ExecuteNonQuery() cmd.Dispose() cmd = Nothing con.Dispose() con = Nothing --<- C:\Documents and Settings\Andrea\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\UserInstanceDatabase.mdf

and the db is created in the actual "User Instance"'s folder... having the User Instance full permissions on that folder you should not experiment permissions problem as well..

if you "turn" to "traditional instances", again, the data folder is respected with the traditional SQLExpress/SQL Server default data folder...

regards

|||

Hi Dimitrius,

What Andrea says is correct with one point of clarification...

The location where a User Instance database is stored changes depending on how you create it. If you handle the creation via SQL tools or using the User Instance connection string into master as Andrea describes above, the database will be created in the folder he specifies. If you create the database using the VS data tools to put the database in your project (Add New Item) the database is actually created in your project folder and deployed along with your application to a per-user file cache created by the VS ClickOnce installer.

Generally this distinction is academic as long as you are not using the |DataDirectory| keyword in your connection string. Using |DataDirectoy| indicates the per user file cache created during a ClickOnce installation, so if you use Andrea's script above, and then call |DataDirectory| as the file path for AttachDbFilename, you will get an error as the file doesn't not exist in that location.

I'm still not certain that User Instances are what you want based on your description. From your description I'm infering that you are creating a Web Service that will read data out of your database. The web service will be running on the same computer where SQL Express is running, so local access should not be an issue. My question is: What advantage do you believe going with User Instances will offer you? Here are my concerns:

A User Instance causes a second process of SQL Express to be running on your computer. When your web service is running, there will be two complete SQL Express instances running on your computer.

Create Dynamic table from CSV

Hi,

I'm trying to import a csv file directly to my database every month. The contents of the file stays the same, however the format of the columns may vary. For example, 1 month I can have the following:

Time, Probe1, Probe2, Probe3, Probe4

Whereas the next month I can have something like this

Time, Probe2, Probe3, Probe1, Probe4

The "Time" column will always be on the left side, but the probes may vary in their placement.

I'm importing this csv to a temp table, where I then run a query to select any new data and enter that in my main tables. The problem is that when i import the csv, if the placement of the columns has changed, the data gets entered in the wrong locations.

Is there any way to create this temp table dynamically, based on what the header columns of the csv file are?

Any help appreciated!

I think the best approach would be to configure your flat file connection manager to read each row as a single column. This column would then be input to a script transformation component which would parse the line into columns and, based on the column order in the header row, put the values into the appropriate output columns.
|||Here is a code sample for a transformation script to illustrate the idea. The Row.Line is the input column that contains an entire line of a text file. This line is taken and split into an array by the comma delimiter. The first time we see the line, it is a header with column names. A boolean flag is used to detect the header condition and we save the column names in a variable. For every subsequent row, we iterate through the column names and match them to an output column that has been defined for the script, assigning the value parsed from the file into the correct column. Using this method, the order of the columns in the text file is irrelevant.

Code Snippet

Dim Line As String = Row.Line
Dim LineTokens As String() = Line.Split(","c)

If IsHeader Then
HeaderColumns = LineTokens
IsHeader = False
Else
' match input columns to output columns
With Output0Buffer
.AddRow()

For i As Integer = 0 To HeaderColumns.Length - 1
Select Case HeaderColumns(i)
Case "Time"
.Time = LineTokens(i)
Case "Probe1"
.Probe1 = LineTokens(i)
Case "Probe2"
.Probe2 = LineTokens(i)
Case "Probe3"
.Probe3 = LineTokens(i)
Case "Probe4"
.Probe4 = LineTokens(i)
End Select
Next

End With
End If

|||

Thanks a lot for the help...

Looks like it'll solve my problem

Thanks!

|||Just make sure you revist the thread and marks it as answered |||

Ok, Since i'm not very proficient with SSIS...

here's what I have going right now.

Under Data Flow, I have a Flat File Source which uses my Flat File connection manager...there's only 1 column, just like you said.

Then I have it going to the transformation script component.

Under Input Columns, I only have Column 0, and its output alias is the same.

Under Inputs & Outputs, I didn't change anything...I didn't add any output columns since I want this to be done dynamically...

Then for the script,

I put in the script you gave me, and this is where i'm having problems...

I renamed Row.Line to Row.Column0,

but there's still a few undefined...such as IsHeader, HeaderColumns, and OutputBuffer.

Where do I define these?!

Thanks!

|||IsHeader and HeaderColumns are global variables that I didn't show in the example. They are shown below would go right under "Inherits UserComponents" in the script.

Code Snippet

Dim IsHeader As Boolean = True
Dim HeaderColumns() As String

Output0Buffer (don't forget the "0") is built for you to the definition that you supply on "Inputs and Outputs". This object will have the same name as your output (without spaces), but with "Buffer" appended to it. You should have one now, but likely just forgot to add the "0" (the default output is "Output 0" ).

You can't leave your output columns blank as they can't be created dynamically. You said your columns were constant, but could appear in different orders in the source. The script will worry about the order of the input columns, but you still need to define the constant output columns.

|||

Beauty.

It's working now.

Thanks a lot for the help.