VB 6.0:Get the list of all SQL Servers in the Network

[Shuja Ali]

Sometimes we need a list of all the SQL Servers present on the network. There are lot of code snippets on the internet to do just that, however I did not find anything in the VBF Codebank.

So here is a function that will return an array f all the SQL Servers present in the network. In order to run this function you will have to set reference to Microsoft SQLDMO Object Library (Goto Projects-->References and select Microsoft SQLDMO Object Library)

'Function : listSQLServers
'Input Parameter : None
'Return Value:  Returns an Array that consists of the
'               names of the SQLServers available
Public Function listSQLServers() As String()
    
    'set reference to Microsoft SQLDMO Object Library
    'prior to using this code
    Dim sqlApp As New SQLDMO.Application
    Dim serverNameList As SQLDMO.NameList
    'an empty array that will hold all the servernames
    Dim serverNames() As String
    Dim loopCounter As Long
            
    'get all the SQL Servers available
    Set serverNameList = sqlApp.ListAvailableSQLServers
    'if no servers exist then return to the calling function
    If serverNameList.Count = 0 Then
        Set sqlApp = Nothing
        Set serverNameList = Nothing
        Exit Function
    End If
    'Redim the array with the proper count
    ReDim serverNames(serverNameList.Count)
        
    'loop through all the servers
    For loopCounter = 0 To serverNameList.Count
        'add to the array
        serverNames(loopCounter) = serverNameList.Item(loopCounter)
    Next
       
    listSQLServers = serverNames
        
    'release objects
    Set sqlApp = Nothing
    Set serverNameList = Nothing
    Exit Function
 
End Function

[Pavemen]

This is not working for me. Its not even returning my (local) server (SQl Server and SQl Server Agent are both started and running)

Hwoever, I can find the server buried way down in servernameList.Application.ServerGroups.Item(1).RegisteredServers.Item(1).Name even though the serverNameList.Count = 0.

Any thoughts?

Using VB6, SQL2000, XP Pro SP2

[Shuja Ali]

Works perfectly for me.

If serverNameList.Count = 0 then that means there are no servers in your network.

[RobDog888]

Yes, I have coded this way for years and its always worked.

http://vbforums.com/showpost.php?p=1437973&postcount=6

[Pavemen]

For some reason its not working on my machine. I the only SQL Server on the network right now and it is running. I can see and connect through ADO, but not DMO. I have the DMO Object Lib and SQLNamespace references added and moved above ADO actually.

[RobDog888]

Your SQL is running on an XP SP-2 system or an actual server?

[Pavemen]

its running on the XP SP2 system. Here at work we run all local installs but allow connections from other users on other machines to access data. I can see my machine and others in the ODBC tool, using the ADO login prompt, etc.

is there another setup I should be using to list available 'servers'?

[RobDog888]

So your not running a Domain?

[Pavemen]

the office is running on a domain, but i am remote 99% of the time. i log into my laptop using a domain account, but unless I VPN into the office, I am using cached credientials (though I need to log in to the machine before I can even launch VPN)

the SQL installs we have accept either windows authentication or a SQL user/pass

I am not running a domain controller on this machine if that is what you are asking.

[RobDog888]

so if your system is running sql on it and you vpn in to your companies domain then your local sql may not return as its not officially "on" the network. Its more of a secure mapped drive.