Listing SQL Servers

Problem

You need to obtain a list of SQL Servers available on the network.

Solution

Use SQL Server Distributed Management Objects (SQL-DMO) to retrieve a list of available SQL Servers.

You'll need a reference to the Microsoft SQLDMO Object Library from the COM tab in Visual Studio .NET's Add Reference Dialog.

The sample code retrieves and displays a list of all SQL Servers running on a local network segment by using SQL-DMO through COM interop.

The C# code is shown in Example 10-1.

Example 10-1. File: ServerListForm.cs

// Namespaces, variables, and constants
using System;

// . . . 

serverListListBox.Items.Clear( );

// Create a SQL Distributed Management Objects (SQL-DMO)
// application object.
SQLDMO.Application dmo = new SQLDMO.Application( );
// Retrieve the available servers.
SQLDMO.NameList serverNameList = dmo.ListAvailableSQLServers( );

// Iterate over the collection of available servers.
for(int i = 0; i < serverNameList.Count; i++)
{
 if (serverNameList.Item(i) != null)
 serverListListBox.Items.Add(serverNameList.Item(i));
}
serverListListBox.Items.Add("End of list.");

Discussion

SQL Server Distributed Management Objects (SQL-DMO) is a collection of objects that encapsulate SQL Server database and replication management. SQL-DMO is used to automate SQL Server tasks , create and administer SQL Server objects, and install and configure replication. You can use SQL-DMO from a .NET application through COM interop. For more information about SQL-DMO, see Microsoft SQL Server Books Online.

The ListAvailableSQLServers( ) method of the SQL-DMO Application object returns a NameList object that enumerates all running servers that listen on named pipes and are located in the same domain. Any servers running on Windows 9 x will not be reported because they do not listen on named pipes. The discovery is based on a network broadcast, so if you are disconnected from a network, local servers will not be enumerated.

This procedure does not return desktop (MSDE) instances.