Listing of SQL Servers

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

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.

VB Code:

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

Quote:

---

Originally Posted by 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.

VB Code:

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.

Does your login have permissions in SQL?

Quote:

---

Originally Posted by RobDog888

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?

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.