By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,620 Members | 1,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,620 IT Pros & Developers. It's quick & easy.

Example code for database configuration(MSDE)

P: n/a
Does anyone know where I can find example code for VB.NET that I can use
to create an application that attaches and detaches databases to MSDE using
osql scipts.
(I think SQL-DMO is typically used)

Thanks in advance
Jul 20 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Hi

The following shows example of attaching a database.
http://support.microsoft.com/?id=224071

The following shows the parameters to the osql utility.
http://msdn.microsoft.com/library/de..._osql_1wxl.asp

John

"David" <da***@orbitcoms.com> wrote in message
news:lu*******************@news-server.bigpond.net.au...
Does anyone know where I can find example code for VB.NET that I can use
to create an application that attaches and detaches databases to MSDE using osql scipts.
(I think SQL-DMO is typically used)

Thanks in advance

Jul 20 '05 #2

P: n/a
To add to John's response, another method is to execute sp_attach_db
directly from your application rather than shelling out to OSQL. Example
below.
Public Shared Sub AttachDb( _
ByVal ConnectionString As String, _
ByVal DatabaseName As String, _
ByVal FileList As String())

Try
Dim connection As New SqlConnection(ConnectionString)
connection.Open()
Dim command As New SqlCommand("sp_attach_db", connection)
command.CommandType = CommandType.StoredProcedure

command.Parameters.Add( _
New SqlParameter("@dbname", DatabaseName))
Dim fileNumber As Integer
Dim fileName As String
For fileNumber = 1 To FileList.Length
command.Parameters.Add( _
New SqlParameter(String.Format("@filename{0}",
fileNumber.ToString), _
FileList(fileNumber - 1)))
Next fileNumber

command.ExecuteNonQuery()
connection.Close()

Catch ex As SqlException
Throw New ApplicationException("Attach database failed: " + _
ex.ToString())
End Try

End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <da***@orbitcoms.com> wrote in message
news:lu*******************@news-server.bigpond.net.au...
Does anyone know where I can find example code for VB.NET that I can use
to create an application that attaches and detaches databases to MSDE using osql scipts.
(I think SQL-DMO is typically used)

Thanks in advance

Jul 20 '05 #3

P: n/a
Dan,

Thanks for the reply. I notice before you attach, you connect using a
database name.
Is this one of the system databases (Master,TempDb....) ?, as at this point
we have not attached the db of interest ?

Thanks
"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message
news:qD***************@newsread1.news.pas.earthlin k.net...
To add to John's response, another method is to execute sp_attach_db
directly from your application rather than shelling out to OSQL. Example
below.
Public Shared Sub AttachDb( _
ByVal ConnectionString As String, _
ByVal DatabaseName As String, _
ByVal FileList As String())

Try
Dim connection As New SqlConnection(ConnectionString)
connection.Open()
Dim command As New SqlCommand("sp_attach_db", connection)
command.CommandType = CommandType.StoredProcedure

command.Parameters.Add( _
New SqlParameter("@dbname", DatabaseName))
Dim fileNumber As Integer
Dim fileName As String
For fileNumber = 1 To FileList.Length
command.Parameters.Add( _
New SqlParameter(String.Format("@filename{0}",
fileNumber.ToString), _
FileList(fileNumber - 1)))
Next fileNumber

command.ExecuteNonQuery()
connection.Close()

Catch ex As SqlException
Throw New ApplicationException("Attach database failed: " + _
ex.ToString())
End Try

End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <da***@orbitcoms.com> wrote in message
news:lu*******************@news-server.bigpond.net.au...
Does anyone know where I can find example code for VB.NET that I can use
to create an application that attaches and detaches databases to MSDE

using
osql scipts.
(I think SQL-DMO is typically used)

Thanks in advance


Jul 20 '05 #4

P: n/a
David (da***@orbitcoms.com) writes:
Thanks for the reply. I notice before you attach, you connect using a
database name.
Dan is using a connection string, but he did not specify one. If
there is no "Intial Catalog" or "Database" in the connection string,
you will run the command from your default database. Since you can
access system procedures from any database, it does not matter, with
one qualification: if your default database is the one you are about
to attach, or some one other non-accessible database, then the
connection string must inlucde a database.
Is this one of the system databases (Master,TempDb....) ?, as at this
point we have not attached the db of interest ?


After having attached the database, you could issue a USE command to
make that your current database. Or simply re-connect with a different
connection string.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

P: n/a
> Thanks for the reply. I notice before you attach, you connect using a
database name.
Is this one of the system databases (Master,TempDb....) ?, as at this point we have not attached the db of interest ?
The DatabaseName argument is used only as the sp_attach_db @dbname
parameter. You can specify the desired database context via the connection
string, which I didn't include in my code example.. Here is an example of
calling the method:

Dim databaseName As String = "MyDatabase"
Dim fileList(2) As String
fileList(0) = "C:\temp\MyDatabase.mdf"
fileList(1) = "C:\temp\MyDatabase_Log.ldf"
DbUtility.AttachDb( _
"Data Source=MyServer;" + _
"Initial Catalog=master;" + _
"Integrated Security=SSPI", _
databaseName, _
fileList)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <da***@orbitcoms.com> wrote in message
news:gw******************@news-server.bigpond.net.au... Dan,

Thanks for the reply. I notice before you attach, you connect using a
database name.
Is this one of the system databases (Master,TempDb....) ?, as at this point we have not attached the db of interest ?

Thanks

Jul 20 '05 #6

P: n/a
Thanks for the help,

I tried using SQLDBO and although it worked on the development machine,
when I ran it on the target I got "File or assembly name
iinterop.SQLDMO.DLL, or one of its dependencies not found" (Even though I
set the SQLDMO refernce in VB.net and coppied SQLDMO.DLL and SQLDMO.RLL
files to app directory with setup solution.

I'll try the method you suggest as it is not dependant on the interop.SQLDMO
"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message
news:hk*****************@newsread1.news.pas.earthl ink.net...
Thanks for the reply. I notice before you attach, you connect using a
database name.
Is this one of the system databases (Master,TempDb....) ?, as at this point
we have not attached the db of interest ?


The DatabaseName argument is used only as the sp_attach_db @dbname
parameter. You can specify the desired database context via the

connection string, which I didn't include in my code example.. Here is an example of
calling the method:

Dim databaseName As String = "MyDatabase"
Dim fileList(2) As String
fileList(0) = "C:\temp\MyDatabase.mdf"
fileList(1) = "C:\temp\MyDatabase_Log.ldf"
DbUtility.AttachDb( _
"Data Source=MyServer;" + _
"Initial Catalog=master;" + _
"Integrated Security=SSPI", _
databaseName, _
fileList)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <da***@orbitcoms.com> wrote in message
news:gw******************@news-server.bigpond.net.au...
Dan,

Thanks for the reply. I notice before you attach, you connect using a
database name.
Is this one of the system databases (Master,TempDb....) ?, as at this

point
we have not attached the db of interest ?

Thanks


Jul 20 '05 #7

P: n/a
Dan,

Can I use a similar method as shown in your code to List the available
servers and databases so that I do not require DBO at all ? This will enable
me to send the users updates of the programs that may have additional
databases added. The user can select a server if they already have a non
MSDE server installed. Also, the code can scan the server db files list to
ensure it only attaches files not already attached.

"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message
news:hk*****************@newsread1.news.pas.earthl ink.net...
Thanks for the reply. I notice before you attach, you connect using a
database name.
Is this one of the system databases (Master,TempDb....) ?, as at this point
we have not attached the db of interest ?


The DatabaseName argument is used only as the sp_attach_db @dbname
parameter. You can specify the desired database context via the

connection string, which I didn't include in my code example.. Here is an example of
calling the method:

Dim databaseName As String = "MyDatabase"
Dim fileList(2) As String
fileList(0) = "C:\temp\MyDatabase.mdf"
fileList(1) = "C:\temp\MyDatabase_Log.ldf"
DbUtility.AttachDb( _
"Data Source=MyServer;" + _
"Initial Catalog=master;" + _
"Integrated Security=SSPI", _
databaseName, _
fileList)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <da***@orbitcoms.com> wrote in message
news:gw******************@news-server.bigpond.net.au...
Dan,

Thanks for the reply. I notice before you attach, you connect using a
database name.
Is this one of the system databases (Master,TempDb....) ?, as at this

point
we have not attached the db of interest ?

Thanks


Jul 20 '05 #8

P: n/a
Much of DMO functionality can be accomplished via directly via SQL. BTW,
many DMO methods execute SQL Server stored procedures internally but
undocumented procs shouldn't be called directly by production applications.

Below is SQL script that will enumerate SQL Servers. Of course, you'll need
to connect to a SQL Server to execute this so this is a catch-22 situation
when you need to list servers before connecting. Also, this is intended to
be run only by sysadmin role members.

CREATE TABLE #SqlServers
(
SqlServerName nvarchar(255)
)

INSERT #SqlServers
EXEC master..xp_cmdshell 'OSQL -L'

SELECT
CASE LTRIM(SqlServerName)
WHEN '(local)' THEN @@SERVERNAME
ELSE LTRIM(SqlServerName) END AS SqlServerName
FROM #SqlServers
WHERE SqlServerName <> 'NULL' AND
SqlServerName <> 'Servers:'

DROP TABLE #SqlServers
GO

You can find code examples to list SQL Servers without DMO at the links
below. These are written in C++ but it shouldn't be difficult to use the
same API calls in VB.NET.

ODBC SQLBrowseConnect: http://www.sqldev.net/misc/ListSQLSvr.htm
LAN Manager NetServerEnum(): http://www.sqldev.net/misc/EnumSQLSvr.htm

One method to list databases is Transact-SQL:

SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY CATALOG_NAME
GO

To list files for the current database:

SELECT filename
FROM sysfiles
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <da***@orbitcoms.com> wrote in message
news:se*******************@news-server.bigpond.net.au...
Dan,

Can I use a similar method as shown in your code to List the available
servers and databases so that I do not require DBO at all ? This will enable me to send the users updates of the programs that may have additional
databases added. The user can select a server if they already have a non
MSDE server installed. Also, the code can scan the server db files list to
ensure it only attaches files not already attached.

Jul 20 '05 #9

P: n/a
Dan,

Thanks heaps for your help.

Much appreciated.

Regards

David Huisman
"Dan Guzman" <da*******@nospam-earthlink.net> wrote in message
news:aL*****************@newsread1.news.pas.earthl ink.net...
Much of DMO functionality can be accomplished via directly via SQL. BTW,
many DMO methods execute SQL Server stored procedures internally but
undocumented procs shouldn't be called directly by production applications.
Below is SQL script that will enumerate SQL Servers. Of course, you'll need to connect to a SQL Server to execute this so this is a catch-22 situation
when you need to list servers before connecting. Also, this is intended to be run only by sysadmin role members.

CREATE TABLE #SqlServers
(
SqlServerName nvarchar(255)
)

INSERT #SqlServers
EXEC master..xp_cmdshell 'OSQL -L'

SELECT
CASE LTRIM(SqlServerName)
WHEN '(local)' THEN @@SERVERNAME
ELSE LTRIM(SqlServerName) END AS SqlServerName
FROM #SqlServers
WHERE SqlServerName <> 'NULL' AND
SqlServerName <> 'Servers:'

DROP TABLE #SqlServers
GO

You can find code examples to list SQL Servers without DMO at the links
below. These are written in C++ but it shouldn't be difficult to use the
same API calls in VB.NET.

ODBC SQLBrowseConnect: http://www.sqldev.net/misc/ListSQLSvr.htm
LAN Manager NetServerEnum(): http://www.sqldev.net/misc/EnumSQLSvr.htm

One method to list databases is Transact-SQL:

SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
ORDER BY CATALOG_NAME
GO

To list files for the current database:

SELECT filename
FROM sysfiles
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <da***@orbitcoms.com> wrote in message
news:se*******************@news-server.bigpond.net.au...
Dan,

Can I use a similar method as shown in your code to List the available
servers and databases so that I do not require DBO at all ? This will

enable
me to send the users updates of the programs that may have additional
databases added. The user can select a server if they already have a non
MSDE server installed. Also, the code can scan the server db files list to ensure it only attaches files not already attached.


Jul 20 '05 #10

P: n/a
Glad it helped.

--
Dan Guzman
SQL Server MVP
Jul 20 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.