Example code for database configuration(MSDE)

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

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" <david@orbitcoms.com> wrote in message
news:lu0Fc.74307$sj4.50620@news-server.bigpond.net.au...[color=blue]
> 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[/color]
using[color=blue]
> osql scipts.
> (I think SQL-DMO is typically used)
>
> Thanks in advance
>
>[/color]

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" <david@orbitcoms.com> wrote in message
news:lu0Fc.74307$sj4.50620@news-server.bigpond.net.au...[color=blue]
> 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[/color]
using[color=blue]
> osql scipts.
> (I think SQL-DMO is typically used)
>
> Thanks in advance
>
>[/color]

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" <danguzman@nospam-earthlink.net> wrote in message
news:qDcFc.2086$oD3.43@newsread1.news.pas.earthlin k.net...[color=blue]
> 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" <david@orbitcoms.com> wrote in message
> news:lu0Fc.74307$sj4.50620@news-server.bigpond.net.au...[color=green]
> > 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[/color]
> using[color=green]
> > osql scipts.
> > (I think SQL-DMO is typically used)
> >
> > Thanks in advance
> >
> >[/color]
>
>[/color]

David (david@orbitcoms.com) writes:[color=blue]
> Thanks for the reply. I notice before you attach, you connect using a
> database name.[/color]

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.
[color=blue]
> Is this one of the system databases (Master,TempDb....) ?, as at this
> point we have not attached the db of interest ?[/color]

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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

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

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" <david@orbitcoms.com> wrote in message
news:gwuFc.76461$sj4.4982@news-server.bigpond.net.au...[color=blue]
> 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[/color]
point[color=blue]
> we have not attached the db of interest ?
>
> Thanks
>
>[/color]

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" <danguzman@nospam-earthlink.net> wrote in message
news:hkCFc.4609$oD3.2855@newsread1.news.pas.earthl ink.net...[color=blue][color=green]
> > 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[/color]
> point[color=green]
> > we have not attached the db of interest ?[/color]
>
> The DatabaseName argument is used only as the sp_attach_db @dbname
> parameter. You can specify the desired database context via the[/color]
connection[color=blue]
> 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" <david@orbitcoms.com> wrote in message
> news:gwuFc.76461$sj4.4982@news-server.bigpond.net.au...[color=green]
> > 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[/color]
> point[color=green]
> > we have not attached the db of interest ?
> >
> > Thanks
> >
> >[/color]
>
>[/color]

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" <danguzman@nospam-earthlink.net> wrote in message
news:hkCFc.4609$oD3.2855@newsread1.news.pas.earthl ink.net...[color=blue][color=green]
> > 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[/color]
> point[color=green]
> > we have not attached the db of interest ?[/color]
>
> The DatabaseName argument is used only as the sp_attach_db @dbname
> parameter. You can specify the desired database context via the[/color]
connection[color=blue]
> 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" <david@orbitcoms.com> wrote in message
> news:gwuFc.76461$sj4.4982@news-server.bigpond.net.au...[color=green]
> > 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[/color]
> point[color=green]
> > we have not attached the db of interest ?
> >
> > Thanks
> >
> >[/color]
>
>[/color]

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" <david@orbitcoms.com> wrote in message
news:seMFc.77482$sj4.56090@news-server.bigpond.net.au...[color=blue]
> 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[/color]
enable[color=blue]
> 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.
>[/color]

Dan,

Thanks heaps for your help.

Much appreciated.

Regards

David Huisman

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:aLTFc.5144$oD3.4730@newsread1.news.pas.earthl ink.net...[color=blue]
> 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[/color]
applications.[color=blue]
>
> Below is SQL script that will enumerate SQL Servers. Of course, you'll[/color]
need[color=blue]
> 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[/color]
to[color=blue]
> 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" <david@orbitcoms.com> wrote in message
> news:seMFc.77482$sj4.56090@news-server.bigpond.net.au...[color=green]
> > 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[/color]
> enable[color=green]
> > 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[/color][/color]
to[color=blue][color=green]
> > ensure it only attaches files not already attached.
> >[/color]
>
>[/color]