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.)


Related Articles

Advanced SQL Server 2008 Extended Events with Examples (25 May 2009)

Introduction to SQL Server 2008 Extended Events (19 May 2009)

Monitoring SQL Server Agent with Powershell (24 March 2009)

SQL Server Version (29 January 2009)

Scheduling Jobs in SQL Server Express - Part 2 (1 December 2008)

Alerts for when Login Failures Strike (14 July 2008)

Using xp_ReadErrorLog in SQL Server 2005 (12 May 2008)

Moving the tempdb database (5 November 2007)

Other Recent Forum Posts

Master DB 2019 problem (13h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (21h)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

Adding a SQL connection to Microsoft Visual Studio (5d)

Update with Inner Join Statement (5d)

- Advertisement -