Create reporting services project in VS 2003

I've installed instance of SQL Server 2005 with reporting services on
my Windows 2003
computer .I've had Visual studio 2003 .After installation i can't
create new reporting
service project in my VS 2003 IDE,but i have now VS 2005 with only
reporting services
project types evalible.I need to work on reporting services in VS
2003.Please tell me how
can i do this.
ThanksHi,
I dont think it is possible in VS 2003. If you have installed Sql server
2005 and their tools you can go through "SQL Server Business Intelligent
studio" you can do report creation.
Regards
Amarnath
"gbletel@.gmail.com" wrote:
> I've installed instance of SQL Server 2005 with reporting services on
> my Windows 2003
> computer .I've had Visual studio 2003 .After installation i can't
> create new reporting
> service project in my VS 2003 IDE,but i have now VS 2005 with only
> reporting services
> project types evalible.I need to work on reporting services in VS
> 2003.Please tell me how
> can i do this.
> Thanks
>

Create replication for SQL2000

Hi,
I've a SQL2000 server with an instance.
I wanna replicate this istance in another server on my lan.
How can I do this?
Thanks
We'd need to know a lot more to fully understand the requirements, but for
creating and maintaining a copy of user databases, have a look in BOL for log
shipping.
Cheers,
Paul Ibison

create new sql server instance

is possible to create new sql server instance without using the setup disk?
No.
A new istance is a full install and then you need to apply the appropriate
SP to the instance.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Oren101" wrote:

> is possible to create new sql server instance without using the setup disk?
|||No, that won′t work.
"Oren101" wrote:

> is possible to create new sql server instance without using the setup disk?
|||Hi,
No.
New SQL Server instance is totally independant of exiting SQL Server. So you
need to use the SETUP program to install.
But you could create multiple databases in existing instance.
Thanks
Hari
SQL Server MVP
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:E9AEA272-5D78-4D66-974D-273CF5990E70@.microsoft.com...
> is possible to create new sql server instance without using the setup
> disk?

create new sql server instance

is possible to create new sql server instance without using the setup disk?No.
A new istance is a full install and then you need to apply the appropriate
SP to the instance.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Oren101" wrote:
[vbcol=seagreen]
> is possible to create new sql server instance without using the setup disk?[/vbcol
]|||No, that won′t work.
"Oren101" wrote:
[vbcol=seagreen]
> is possible to create new sql server instance without using the setup disk?[/vbcol
]|||Hi,
No.
New SQL Server instance is totally independant of exiting SQL Server. So you
need to use the SETUP program to install.
But you could create multiple databases in existing instance.
Thanks
Hari
SQL Server MVP
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:E9AEA272-5D78-4D66-974D-273CF5990E70@.microsoft.com...
> is possible to create new sql server instance without using the setup
> disk?

create new sql server instance

is possible to create new sql server instance without using the setup disk?No.
A new istance is a full install and then you need to apply the appropriate
SP to the instance.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Oren101" wrote:
> is possible to create new sql server instance without using the setup disk?|||No, that won´t work.
"Oren101" wrote:
> is possible to create new sql server instance without using the setup disk?|||Hi,
No.
New SQL Server instance is totally independant of exiting SQL Server. So you
need to use the SETUP program to install.
But you could create multiple databases in existing instance.
Thanks
Hari
SQL Server MVP
"Oren101" <Oren101@.discussions.microsoft.com> wrote in message
news:E9AEA272-5D78-4D66-974D-273CF5990E70@.microsoft.com...
> is possible to create new sql server instance without using the setup
> disk?

create new instance

How can I create new instance on installed sql server 2000. (without new
install)
For each instance you want, you need to run the setup program to install that instance...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dolphin" <d@.d.com> wrote in message news:%23wRgnYNPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> How can I create new instance on installed sql server 2000. (without new
> install)
>
>

create new instance

How can I create new instance on installed sql server 2000. (without new
install)For each instance you want, you need to run the setup program to install that instance...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dolphin" <d@.d.com> wrote in message news:%23wRgnYNPEHA.1160@.TK2MSFTNGP09.phx.gbl...
> How can I create new instance on installed sql server 2000. (without new
> install)
>
>

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 Instance of Notification Service

Hi,

I am getting the following error.

Event ID:XmlValidationError Source:NSEventStrings

while creating Instance of Notification Service.

Hi,

This is probably becuase your instance definition file or application definition file is not compatible with SQL NS schemas.

Following link contains complete template of instance and application definition file. Make sure your xml adheres to it.

http://msdn2.microsoft.com/en-us/library/ms145917.aspx
http://msdn2.microsoft.com/en-US/library/ms145313.aspx

Shamir

|||As Shamir mentioned, both the ICF and ADF must be well-formed and valid XML documents. They are validated against an XSD schema in your SSNS installation folder.

Keep in mind that XML is case-sensitive.

HTH...

Joe

Create Instance of Notification Service

Hi,

I am getting the following error.

Event ID:

XmlValidationError

Source:

NSEventStrings

while creating Instance of Notification Service.

Hi,

This is probably becuase your instance definition file or application definition file is not compatible with SQL NS schemas.

Following link contains complete template of instance and application definition file. Make sure your xml adheres to it.

http://msdn2.microsoft.com/en-us/library/ms145917.aspx
http://msdn2.microsoft.com/en-US/library/ms145313.aspx

Shamir|||As Shamir mentioned, both the ICF and ADF must be well-formed and valid XML documents. They are validated against an XSD schema in your SSNS installation folder.

Keep in mind that XML is case-sensitive.

HTH...

Joe

Create Instance of Notification Service

Hi,

I am getting the following error.

Event ID:XmlValidationError Source:NSEventStrings

while creating Instance of Notification Service.

Hi,

This is probably becuase your instance definition file or application definition file is not compatible with SQL NS schemas.

Following link contains complete template of instance and application definition file. Make sure your xml adheres to it.

http://msdn2.microsoft.com/en-us/library/ms145917.aspx
http://msdn2.microsoft.com/en-US/library/ms145313.aspx

Shamir

|||As Shamir mentioned, both the ICF and ADF must be well-formed and valid XML documents. They are validated against an XSD schema in your SSNS installation folder.

Keep in mind that XML is case-sensitive.

HTH...

Joe

Create Instance

How do I create my first instance for an MSDE database?To install a default instance, type in the following command at the dos
prompt. It also requires an strong password for sa account.
setup SAPWD=<someStrongPassword>
For a named instance, you will need to type in the following command at the
dos command prompt.
setup INSTANCENAME=<instance name you input> SECURITYMODE=SQL
SAPWD=<someStrongPassword>
- Mac
"Arne" <arnenospam@.garvander.com> wrote in message
news:01b401c35d07$1dcdd9d0$a301280a@.phx.gbl...
> How do I create my first instance for an MSDE database?

CREATE ENDPOINT to Expose a single DB instance

I have been looking at this statement for allowing access from a remote location to a specific database on my SQL Server (2005) from a client application.

Is it possible to do this using this method? I see lots of examples using this to expose Web Services of DB objects, however I need the client application to be able to authenticate to and access all objects in the database. If so, could some one provide a simple example of the arguments to use? For example, which protocol arguments are valid for this? I would like to use SQL Server Authentication. Is this possible?

Regards,

-Troy

I think the answer is to create a new instance of SQL Server on a non-standard port and move the DB to that instance. At least that is what I've chosen to do. If anyone knows of a better way, let me know.

Create Directory in Instance Sql Server of another machine

My Sql Server is in Windows Server 2003 machine the name: Machine1

I want create a new directory in machine1 over in machine2 (my machine)

The code is:

Dim srv As New Server("SqlInstance")

Dim sFolder As String = srv.Settings.DefaultFile & "\NewDirectory"

IO.Directory.CreateDirectory(sFolder)

This not work because the sFolder report = D:\SqlData\NewDirectory and the Io.Directory try create in my local machine (I not have a drive D:\ in machine local).

How can I create a subdirectory in srv.settings.defaulfile Sql Server?

thanks,

Marsenne

Hi,

do you want to create a folder on a remote machine ?

you can either use a share to create this file (an administrative share perhpas, if you have the appropiate permissions:

System.IO.Directory.CreateDirectory(@.\\m2-jenss\hierrein\Test);

Or you can use WMI to create a folder on the remote machine:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/win32_createfolderaction.asp

HTH, JEns Suessmeyer.

http://www.sqlserver2005.de

Create Database with Filegroups restore Database without Filegroup

I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster with
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database. Can
I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,
Restore will give you (essentially) a binary image of what is inside the database. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before the backup or after the
restore...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster with
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database. Can
> I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,

Create Database with Filegroups restore Database without Filegroup

I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster with
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database. Can
I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,Restore will give you (essentially) a binary image of what is inside the database. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before the backup or after the
restore...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster with
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database. Can
> I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,

Create Database with Filegroups restore Database without Filegroup

I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
server name and REPT as the name instance. This is Active\Active Cluster wit
h
Windows 2000 Advanced Server as the OS.
I have a new server and would like to have filegroups for the database. Can
I create filegroups on the new server and restore the backup from
Austin_Sales\REPT in the database with filegroups?
Please help me with this task.
Thanks,Restore will give you (essentially) a binary image of what is inside the dat
abase. I.e., you cannot
change filegroup layout through backup/restore. You'd have to do that before
the backup or after the
restore...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:CFB09E6E-E210-4D53-B26E-98FFAC443E70@.microsoft.com...
> I have a SQL Server 2000 virtual server with Austin_Sales as the virtual
> server name and REPT as the name instance. This is Active\Active Cluster w
ith
> Windows 2000 Advanced Server as the OS.
> I have a new server and would like to have filegroups for the database. C
an
> I create filegroups on the new server and restore the backup from
> Austin_Sales\REPT in the database with filegroups?
> Please help me with this task.
> Thanks,