Finding SQL Servers running on a network

By Lance Harra on 15 August 2001 | Tags: Administration , DMO

Jerald writes "Dear SQL Team, I am working on SQL Server 7 and also on SQL 2000. Now our company has many SQL Servers running on the Network and also in different domains. Now is there a way where I can find out which SQL Servers are currently running on the Network and list them in the Front end (using VB, to display the list of servers avalible in a combo)"

Here are the ways I know of to enumerate a list of sql servers.

OSQL/ISQL

You can use isql or osql with the -L option as shown below.

Using isql -L

Locally configured servers:
    SQLSRV14

Announced network servers:
    SQLSRV1
    SQLSRV11
    SQLSRV13
    SQLSRV14
    SQLSRV23
    SQLSRV4
    SQLSRV8

and using osql -L

Servers:
    SQLSRV1
    SQLSRV13\DONORDB
    SQLSRV13\DONORDBDEV
    SQLSRV13\REPORTINGDB
    SQLSRV14
    SQLSRV17\REPORTDB
    SQLSRV18\TRANSDB

Note osql was able to find the instance names while isql only found the physical server name

SQLPing

SQLPING.EXE is available at www.sqlsecurity.com/faq.asp and is handly little utiltiy to check for the existance of a SQL Server. (Graz: And www.sqlsecurity.com is a great site for the security conscious)

Using sqlping

SQL-Pinging 255.255.255.255
Listening....

ServerName:SQLSRV14
InstanceName:MSSQLSERVER
IsClustered:No
Version:8.00.194
tcp:1433
np:\\SQLSRV14\pipe\sql\query

ServerName:SQLSRV17
InstanceName:REPORTDB
IsClustered:Yes
Version:8.00.194
tcp:1433
np:\\SQLSRV17\pipe\MSSQL$REPORTDB\sql\query

I only included a partial listing from sqlping to save space

SQL-DMO Objects

SQL-DMO is an API level interface into many areas of SQL Server. This knowlege base article describes using calls to SQL-DMO to list running servers. (Graz: The code looks something like this:)

Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Set oSQLApp = New SQLDMO.Application
   
Set oNames = oSQLApp.ListAvailableSQLServers()
List1.Clear
For i = 1 To oNames.Count
    List1.AddItem oNames.Item(i)
Next i

(Graz: You can find more information on SQL-DMO here.)