I have a script to create a db. it only uses features available to sql2k. I only have sqlexpress, but want to distribute a db to someone with sql2k. Can I set a setting or something to allow me to detach my db and attach it to the sql2k server, or create the db as sqk2k compat.?
thanks
Databases created against SQL Server 2005 cannot be detached and attached to SQL Server 2000. You should be able do an export/import.Dan
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.ClickDim 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
|||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,
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.
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.ClickDim 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
|||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,
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.
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 Subyou 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,
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
|||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,
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.mdfand 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.Hi,
In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?
Cheers,
Daniel
Take a look at this thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320987&SiteID=1
|||
Or just use “generate scripts wizard”:
Right-click on the database,
choose “TASKS-> Generate Scripts” and simply follow the wizard steps.
Regards,
Alfred.
Hi,
In most books on ADO.NET programming, a sample database is given as a series of sql instructions (create database, create table, insert into table values (..), etc ), thereby creating the complete mdf/database file. The question arises: how does one create such a SQL script file from an existing .mdf using SSMSEE/SQL Server 2005 Express?
Cheers,
Daniel
Take a look at this thread
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320987&SiteID=1
|||
Or just use “generate scripts wizard”:
Right-click on the database,
choose “TASKS-> Generate Scripts” and simply follow the wizard steps.
Regards,
Alfred.
Hello,
After creating a new SSMSExpress Login username account, Iuse it as the Database User of the attached database (aspnetdb.mdf), but Ireceive this error.
...
Additional information:
->An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
->The login already has an account under a different user name. (Microsoft SQL Server,
Error: 15063)
...
I am sure such username account is not yet members of that database(aspnetdb.mdf) for this are the users present
-dbo
-guest
-INFORMATION_SCHEMA
-sys
-COMPUTERNAME\ASPNET
-CONPUTERNAME\IUSR_COMPUTERNAME
cheers,
imperialx
Logins and users are two different things.
Logins are defined at global level. Users are simply a mapping from a login to a username.
Try opening SSMSE and open the security folder. Under logins you will see all the "logins" (users) that have already been created. I bet the user you are trying to create is in that list.
Hope it points you in the right direction
can i run such a transact SQL script with VB
use master
go
create database Ayhandeneme
on (Name=AyhanDeneme_Dat, FileName='c:ayhandeneme.mdf')
go
You cannot execute the GO, as it is a batch seperator defined by the SQL Server tools. You will have to set the context in your connection command, or simply run it in two different batches.
But you should be able to run any T-SQL command from VB.
|||Use ADO in VB6, ADO.net with VB.net.
As previously mentioned by MSVP, run the db creation in your command text object
Adamus
|||Wait...you can't USE a db to CREATE a db?can i run such a transact SQL script with VB
use master
go
create database Ayhandeneme
on (Name=AyhanDeneme_Dat, FileName='c:ayhandeneme.mdf')
go
What are you trying to do?
Adamus
CREATE DATABASE [db1] ON PRIMARY
( NAME = N'db1', FILENAME = N'C:\Databases\Main\db1.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'db1_log', FILENAME = N'C:\Databases\Main\db1_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
However, I always get the following error:
CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database.
I don't have a clue what's going wrong? The very same command is working fine on other machines. Its only one particular machine where it fails with the error message. Strangely enough, it worked on the "problem machine" too until I had to format the hard disk of the machine and re-install everything from scratch.
Any idea what could be going wrong? Please note that I am using SQL Server 2005.
On the server where this fails, verify the size of the Model database. I suspect it has inadvertently become larger than expected.
All newly created databases use the Model database as a 'template'. The new databases will start out no smaller than the Model database.
As this message indicates, since the Model database is about 3 MB in size, you MUST have a minimum size of 3 MB.
And you can reduce the size of the Model database. See Books Online, Topics: DBCC Shrinkdatabase, DBCC Shrinkfile
|||Thanks for your response. I checked the size of model database and found that it has a size of 3MB on that particular server as you said.You are correct, removing the [SIZE] parameter will ensure that you don't run into a similar problem.
I would suggest that immediately after the CREATE DATABASE statement, if your needs dictate a certain size to start with, that your code check the defined size and issue a ALTER DATABASE statement to increase if necessary.
|||I do not have any such requirement as of now so I might not be needing an 'alter database'Anyway, thanks a lot for your suggestions.
