Listing of SQL Servers

[espylacopa]

I am writing a program in VB6 where the user can select the sql server name and the database. Using ADO how would I get a dropdown of available servers and their databases (for the user to select during runtime)

Thanks

[RobDog888]

If you have the SQL CLient tools installed on your system then you will have the SQLDMO Object library which you can use to do it.

Option Explicit
'Add a reference to M$ SQLDMO Object Library.
Private Sub Command1_Click()
 
    Dim oSQLApp As SQLDMO.Application
    Dim oNames As SQLDMO.NameList
    Dim i as integer
 
    Set oSQLApp = New SQLDMO.Application
    'POPULATE CBO ONLY WITH SQL SERVERS
    Set oNames = oSQLApp.ListAvailableSQLServers
    cboServer.Clear
    For i = 1 To oNames.Count
        cboServer.AddItem oNames.Item(i)
    Next i
    If oNames.Count = 0 Then
        cboServer.AddItem "NO SQL SERVERS FOUND"
    EndIf
 
End Sub
 
Private Sub Command2_Click()
 
    Set goSQL = New SQLDMO.SQLServer
    goSQL.Connect CStr(cboServer), CStr(txtLoginName), CStr (txtPassword) 'oSQL.HostName
    If goSQL.ConnectionID <= 0 Then
        MessageBox.Show("CONNECTION FAILED!!!")
        Exit Sub
    End If
    sRc = goSQL.ExecuteWithResults("USE master SELECT * FROM     sysdatabases", SQLDMOExec_Default).Rows
    sRs = " "
    i = 0
    cboDatabase.Clear
    Do Until i = sRc
        i = i + 1
        sRs = goSQL.ExecuteWithResults("USE master SELECT * FROM sysdatabases", SQLDMOExec_Default).GetColumnString(i, 1)
        cboDatabase.AddItem sRs
    Loop
    cboDatabase = cboDatabase.List(0)
 
End Sub

[luca90]

If you have the SQL CLient tools installed on your system then you will have the SQLDMO Object library which you can use to do it.

Option Explicit
'Add a reference to M$ SQLDMO Object Library.
Private Sub Command1_Click()
 
    Dim oSQLApp As SQLDMO.Application
    Dim oNames As SQLDMO.NameList
    Dim i as integer
 
    Set oSQLApp = New SQLDMO.Application
    'POPULATE CBO ONLY WITH SQL SERVERS
    Set oNames = oSQLApp.ListAvailableSQLServers
    cboServer.Clear
    For i = 1 To oNames.Count
        cboServer.AddItem oNames.Item(i)
    Next i
    If oNames.Count = 0 Then
        cboServer.AddItem "NO SQL SERVERS FOUND"
    EndIf
 
End Sub
 
Private Sub Command2_Click()
 
    Set goSQL = New SQLDMO.SQLServer
    goSQL.Connect CStr(cboServer), CStr(txtLoginName), CStr (txtPassword) 'oSQL.HostName
    If goSQL.ConnectionID <= 0 Then
        MessageBox.Show("CONNECTION FAILED!!!")
        Exit Sub
    End If
    sRc = goSQL.ExecuteWithResults("USE master SELECT * FROM     sysdatabases", SQLDMOExec_Default).Rows
    sRs = " "
    i = 0
    cboDatabase.Clear
    Do Until i = sRc
        i = i + 1
        sRs = goSQL.ExecuteWithResults("USE master SELECT * FROM sysdatabases", SQLDMOExec_Default).GetColumnString(i, 1)
        cboDatabase.AddItem sRs
    Loop
    cboDatabase = cboDatabase.List(0)
 
End Sub

OK TKS !!!
Work but have error(on the line with arrow), if you see the image attached.

[RobDog888]

Does your login have permissions in SQL?

[luca90]

Does your login have permissions in SQL?

I dont know.... but assuming i wolud want to know only the name of server have SQL, need a password?

[RobDog888]

Well if you just want to list the SQL Servers on your network then no. You dont need a sql login to list the servers but you will if you want to list the databases on a particular sql server.