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.

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

Discuss this article: 7 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

Sum where date is between two dates (2 Replies)

adding a date seems to remove any null values (0 Replies)

7 day sales analysis query (1 Reply)

Calculated Columns based on multiple columns (4 Replies)

Jobs canceled abruptly in a SQL server Client (5 Replies)

problems with crosstab (3 Replies)

Curruption of SQL + error (2 Replies)

Job execution problem (SQLSERVER 2012) (2 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -