Get list of attached SSQl DB's in VB 1

[Varco]

Can someone point me to a source for VB code to build a list of currently attached SQL DB's. This would be the same list as shown in ODBC Administrator in the "Change the default database to:" dropdown list for SQL Server connections.

Jim Varco
jimv@varcconsulting.com

 

[zemp]

Set a reference to the Microsoft SQLDMO object library. Then use code similar to the folowing.

   Dim i As Integer
   Dim objServers As SQLDMO.NameList
   Dim objSQLApp As SQLDMO.Application

   '// Determine the list of servers on the target.
   Set objSQLApp = New SQLDMO.Application
   Set objServers = objSQLApp.ListAvailableSQLServers()
   
   Combo1.Clear
   If objServers.Count <= 0 Then
      MsgBox "No SQL Servers available."
   Else
      For i = 1 To objServers.Count
          Combo1.AddItem objServers.Item(i)
      Next i
   End If

zemp

 

[Varco]

Zemp,

Thanks for the code, it worked great to build a list of servers. I was looking to build a list of attached SQL DB's, such as "Master", etc., can you help with that?

Jim Varco
jimv@varcconsulting.com

 

[zemp]

Sorry, my mistake. Try something like this.

   Dim l_sngTime As Single, l_sngPauseTime As Single
   Dim objDatabases As SQLDMO.Database
   Dim objServer As SQLDMO.SQLServer
       
   '// Determine the list of servers on the target.
   Set objDatabases = New SQLDMO.Database
   Set objServer = New SQLDMO.SQLServer

   '// Makes sure the server is running.
   objServer.Name = Trim(lstServer.Text)
   If objServer.Status = SQLDMOSvc_Stopped Then
      objServer.Start True, Trim(<Servername>), "", ""
      DoEvents
      l_sngTime = Timer
      l_sngPauseTime = (60 * 3)
      Do
         If Timer > l_sngTime + l_sngPauseTime Then Exit Do
      Loop Until objServer.Status = SQLDMOSvc_Running
   Else
      objServer.LoginSecure = True
      objServer.Connect Trim(<ServerName>)
   End If
   
   For Each objDatabases In objServer.Databases
      Combo1.AddItem objDatabases.Name
   Next

zemp

 

[Varco]

Zemp,

Thanks, this is perfect.

Jim Varco
jimv@varcconsulting.com