SQL Server Registry Info 1

[RBCDS]

I'm using ado.net and i need to create a connection wizard that will have drop-downs for all of the current sql server connection, and the related tables for the selected sql server. With this I will create my connection string.

ConnString = "Data Source=SQLServer;" & _
"Initial Catalog=Database;" & _
"User ID=sa;" & _
"Password=password;"

I have found part of what I am looking for in the registry. Below is the registry key I access to find the SQL Server names.

myReg.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect")

What I can't seem to find is a list of databases??? If anyone can help me it would be appreciated, I've been searching the web for awhile and can't seem to get anywhere.


Dan

 

[ThatRickGuy]

The easiest way is probrably to use SQLDMO. I just went through this a few weeks back to write some code generation tools.

Add a reference to SQLDMO to your project.

then you can use this code to populate a combo box with all of the SQLServers on the network:

    Dim oSQLApp As New SQLDMO.Application()
    Dim oNames As SQLDMO.NameList = oSQLApp.ListAvailableSQLServers
    Dim oSQLServer As New SQLDMO.SQLServer()

    oSQLServer.LoginSecure = True
    oSQLServer.LoginTimeout = 5

    For i = 1 To oNames.Count
      Try
        Me.cmbServer.Items.Add(oNames.Item(i))
      Catch exc As Exception
        Debug.WriteLine("Failure: " & oNames.Item(i) & "   " & exc.Message)
      End Try
    Next

Once you have a server name, you can populate all of the databases it has with this:

    Dim oSQLServer As New SQLDMO.SQLServer()
    Dim i As Integer
    Try
      cmbDatabase.Items.Clear()
      cmbDatabase.Text = ""
      oSQLServer.LoginSecure = True
      oSQLServer.Connect(cmbServer.Text, Nothing, Nothing)
      For i = 1 To oSQLServer.Databases.Count
        If Not oSQLServer.Databases.Item(i).SystemObject Then
          cmbDatabase.Items.Add(oSQLServer.Databases.Item(i).Name)
        End If
      Next
    Catch exc As Exception
      MsgBox("Server is unavailable." & ControlChars.Cr & exc.Message)
    End Try

And once you select a server and database, you can get a list of tables with this:

    Dim oSQLServer As New SQLDMO.SQLServer()
    Dim oDatabase As SQLDMO.Database
    Dim oTable As SQLDMO.Table
    Dim i As Integer
    Dim j As Integer
    oSQLServer.LoginSecure = True
    oSQLServer.Connect(cmbServer.Text, Nothing, Nothing)
    oDatabase = oSQLServer.Databases.Item(cmbDatabase.Text)

    cmbTable.Items.Clear()
    cmbTable.Text = ""
    For Each oTable In oDatabase.Tables()
      Try
        If Not oTable.SystemObject Then cmbTable.Items.Add(oTable.Name)
      Catch
      End Try
    Next

And once you have the Server, Database, and Table selected, you can use this code to get all of the fields and descriptions and bind the list to a data grid:

      Dim oSQLServer As New SQLDMO.SQLServer()
    Dim oDatabase As SQLDMO.Database
    Dim oTable As SQLDMO.Table
    Dim i As Integer
    Dim dtFields As New DataTable()
    Dim dr As DataRow

    dtFields.Columns.Add("Name")
    dtFields.Columns.Add("Type")
    dtFields.Columns.Add("Size")
    dtFields.Columns.Add("PrimaryKey", GetType(Boolean))
    dtFields.Columns.Add("Include", GetType(Boolean))
    dtFields.Columns.Add("Description")

    oSQLServer.LoginSecure = True
    oSQLServer.Connect(cmbServer.Text, Nothing, Nothing)
    oDatabase = oSQLServer.Databases.Item(cmbDatabase.Text)
    oTable = oDatabase.Tables.Item(cmbTable.Text)

    For i = 1 To oTable.Columns.Count
      dr = dtFields.NewRow
      dr.Item("Name") = oTable.Columns.Item(i).Name
      dr.Item("Type") = oTable.Columns.Item(i).Datatype
      dr.Item("Size") = oTable.Columns.Item(i).Length
      dr.Item("PrimaryKey") = oTable.Columns.Item(i).InPrimaryKey
      dr.Item("Include") = True

      dr.Item("Description") = GetDescription(oTable.Columns.Item(i).Name)

      dtFields.Rows.Add(dr)
    Next

    Me.dgFields.DataSource = dtFields

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[RBCDS]

Thanks Rick, There's a lot more control over what I want to do using sqldmo than going into the registry.

Dan

 

[chrissie1]

I think the sql-servers need to have sp3 installed for this to work.

Databases can be found by executing sp_databases on the master database (or something like that.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]