Example code for database configuration(MSDE)

[David]

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

[John Bell]

Re: Example code for database configuration(M SDE)

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/default.asp?url=/library/en-us/coprompt/cp_osql_1wxl.asp

John

"David" <david@orbitcom s.com> wrote in message
news:lu0Fc.7430 7$sj4.50620@new s-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 Guzman]

Re: Example code for database configuration(M SDE)

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 ConnectionStrin g As String, _
ByVal DatabaseName As String, _
ByVal FileList As String())

Try
Dim connection As New SqlConnection(C onnectionString )
connection.Open ()
Dim command As New SqlCommand("sp_ attach_db", connection)
command.Command Type = CommandType.Sto redProcedure

command.Paramet ers.Add( _
New SqlParameter("@ dbname", DatabaseName))
Dim fileNumber As Integer
Dim fileName As String
For fileNumber = 1 To FileList.Length
command.Paramet ers.Add( _
New SqlParameter(St ring.Format("@f ilename{0}",
fileNumber.ToSt ring), _
FileList(fileNu mber - 1)))
Next fileNumber

command.Execute NonQuery()
connection.Clos e()

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

End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <david@orbitcom s.com> wrote in message
news:lu0Fc.7430 7$sj4.50620@new s-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]

[David]

Re: Example code for database configuration(M SDE)

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@nosp am-earthlink.net> wrote in message
news:qDcFc.2086 $oD3.43@newsrea d1.news.pas.ear thlink.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 ConnectionStrin g As String, _
> ByVal DatabaseName As String, _
> ByVal FileList As String())
>
> Try
> Dim connection As New SqlConnection(C onnectionString )
> connection.Open ()
> Dim command As New SqlCommand("sp_ attach_db", connection)
> command.Command Type = CommandType.Sto redProcedure
>
> command.Paramet ers.Add( _
> New SqlParameter("@ dbname", DatabaseName))
> Dim fileNumber As Integer
> Dim fileName As String
> For fileNumber = 1 To FileList.Length
> command.Paramet ers.Add( _
> New SqlParameter(St ring.Format("@f ilename{0}",
> fileNumber.ToSt ring), _
> FileList(fileNu mber - 1)))
> Next fileNumber
>
> command.Execute NonQuery()
> connection.Clos e()
>
> Catch ex As SqlException
> Throw New ApplicationExce ption("Attach database failed: " + _
> ex.ToString())
> End Try
>
> End Sub
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "David" <david@orbitcom s.com> wrote in message
> news:lu0Fc.7430 7$sj4.50620@new s-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]

[Erland Sommarskog]

Re: Example code for database configuration(M SDE)

David (david@orbitcom s.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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Dan Guzman]

Re: Example code for database configuration(M SDE)

> 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\MyData base.mdf"
fileList(1) = "C:\temp\MyData base_Log.ldf"
DbUtility.Attac hDb( _
"Data Source=MyServer ;" + _
"Initial Catalog=master; " + _
"Integrated Security=SSPI", _
databaseName, _
fileList)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"David" <david@orbitcom s.com> wrote in message
news:gwuFc.7646 1$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]

[David]

Re: Example code for database configuration(M SDE) - THANKS

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@nosp am-earthlink.net> wrote in message
news:hkCFc.4609 $oD3.2855@newsr ead1.news.pas.e arthlink.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\MyData base.mdf"
> fileList(1) = "C:\temp\MyData base_Log.ldf"
> DbUtility.Attac hDb( _
> "Data Source=MyServer ;" + _
> "Initial Catalog=master; " + _
> "Integrated Security=SSPI", _
> databaseName, _
> fileList)
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "David" <david@orbitcom s.com> wrote in message
> news:gwuFc.7646 1$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]

[David]

Re: Example code for database configuration(M SDE)

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@nosp am-earthlink.net> wrote in message
news:hkCFc.4609 $oD3.2855@newsr ead1.news.pas.e arthlink.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\MyData base.mdf"
> fileList(1) = "C:\temp\MyData base_Log.ldf"
> DbUtility.Attac hDb( _
> "Data Source=MyServer ;" + _
> "Initial Catalog=master; " + _
> "Integrated Security=SSPI", _
> databaseName, _
> fileList)
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "David" <david@orbitcom s.com> wrote in message
> news:gwuFc.7646 1$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 Guzman]

Re: Example code for database configuration(M SDE)

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_cmds hell 'OSQL -L'

SELECT
CASE LTRIM(SqlServer Name)
WHEN '(local)' THEN @@SERVERNAME
ELSE LTRIM(SqlServer Name) 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 SQLBrowseConnec t: 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_SCH EMA.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@orbitcom s.com> wrote in message
news:seMFc.7748 2$sj4.56090@new s-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]

[David]

Re: Example code for database configuration(M SDE)

Dan,

Thanks heaps for your help.

Much appreciated.

Regards

David Huisman


"Dan Guzman" <danguzman@nosp am-earthlink.net> wrote in message
news:aLTFc.5144 $oD3.4730@newsr ead1.news.pas.e arthlink.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_cmds hell 'OSQL -L'
>
> SELECT
> CASE LTRIM(SqlServer Name)
> WHEN '(local)' THEN @@SERVERNAME
> ELSE LTRIM(SqlServer Name) 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 SQLBrowseConnec t: 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_SCH EMA.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@orbitcom s.com> wrote in message
> news:seMFc.7748 2$sj4.56090@new s-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]

[Dan Guzman]

Re: Example code for database configuration(M SDE)

Glad it helped.

--
Dan Guzman
SQL Server MVP