Finding SQL Servers running on a network
By Lance Harra
on 15 August 2001
| 7 Comments
| 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.
You can use isql or osql with the -L option as shown below.
Using isql -L
Locally configured servers:
Announced network servers:
and using osql -L
Note osql was able to find the instance names while isql only found the physical server name
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)
I only included a partial listing from sqlping to save space
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()
For i = 1 To oNames.Count
(Graz: You can find more information on SQL-DMO here.)