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.