Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    New York

    Unanswered: Severs & Databases list

    Hello, I am working in Visual Basic .NET and I am trying to populate a combo box with all the server names available locally. Once a server is selected from a combo box I want to populate another combo box with all the databases available on that server. Furthermore, upon the selection of one database I want to populate a third combobox with the fields available in that database.

    As the third is not difficult thru dataset usage, the first and the second pose problems as I cannot find any function that would allow me to query the system for available SQL servers and also to query the server for all available databases....

    Any help would be appreciated,

    Thank you!

  2. #2
    Join Date
    Feb 2002
    You can use a system stored procedure (sp_databases) to return the databases for an instance. However, I have not seen any remedy for the instance dilemna. Even enterprise manager and query analyzer require that you know what you want to connect to or register (which is stored in the clients registry) Since instances of sql server will probably be a rather static event, I would maintain all sql server instances in a table and pull the information from this.

  3. #3
    Join Date
    Sep 2002
    New York
    Thank you!! It worked!! And I also solved the other problem by looking into the registry... Here are the two routines, the first one loads the server list in a combo-box upon form load and the other one populates the combo box with the database names provided that a server was selected in the list... I have only one worry: I wonder if the registry key is the same for all versions of the SQL server?


    Private Sub BinderEditorForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim r As Microsoft.Win32.Registry
    Dim cu As RegistryKey, lm As RegistryKey
    Dim key As String = "Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X\"
    Dim crtkey As RegistryKey, ck As RegistryKey
    Dim skn() As String
    Dim v() As String
    Dim f As Integer, g As Integer

    cu = r.CurrentUser

    crtkey = cu.OpenSubKey(key, False)
    skn = crtkey.GetSubKeyNames()

    For f = 0 To skn.Length - 1
    ck = crtkey.OpenSubKey(skn(f), False)
    v = ck.GetValueNames()
    For g = 0 To v.Length - 1

    End Try

    End Sub

    Private Sub cbDatabase_DropDown(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbDatabase.DropDown
    Dim strsp = "sp_databases()"
    Dim strServerName = Me.cbServer.Text
    Dim strSecurity As String
    Dim strServer As String
    Dim sc As SqlConnection

    If strServerName = "" Then Exit Sub

    If Me.rbWindows.Checked Then
    strSecurity = "Integrated Security=SSPI;Persist Security Info=False;"
    If Me.cbBlankPassword.Checked Then
    strSecurity = "Persist Security Info=False;User ID=" & Me.tbUsername.Text & ";"
    strSecurity = "Password=" & Me.tbPassword.Text & ";Persist Security Info=True;User ID=" & _
    Me.tbUsername.Text & ";"
    End If
    End If

    strServer = "Data Source=" & strServerName

    Dim cs As String
    cs = strSecurity & strServer

    sc = New SqlConnection(cs)


    Dim scom As SqlCommand = New SqlCommand("sp_databases", sc)
    scom.CommandType = CommandType.StoredProcedure

    Dim da As SqlDataAdapter
    da = New SqlDataAdapter(scom)

    Dim ds As DataSet = New DataSet("Tables")

    Dim dv As DataView = ds.Tables(0).DefaultView
    Dim f As Integer
    Dim dvr As DataRowView


    For f = 0 To dv.Table.Rows.Count - 1
    dvr = dv(f)

    Catch ee As SqlException
    MsgBox(ee.Message & " - " & ee.LineNumber)
    End Try

    End Sub


    Thanks again!

  4. #4
    Join Date
    Feb 2002
    Houston, TX
    OSQL.EXE -L will give you a listing of servers although I suspect there is more than one way to hide a server from this utility.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts