10.7 Discover All Instances of SQL Server 2000 on Your Network

Problem

You need to obtain a list of all instances of SQL Server 2000 that are accessible on the network.

Solution

Use COM interop to access the functionality of the Microsoft SQLDMO Object Library. Create an Application object, and call its ListAvailableSQLServers method. ListAvailableSQLServers returns a NameList object, which is an enumerable string collection containing the name of each SQL Server 2000 instance discovered on the network.

Discussion

The .NET Framework class library doesn't include the functionality to find unknown SQL Servers; however, this task is straightforward using the Microsoft SQLDMO Object Library accessed through COM interop. Recipe 15.6 details how to create an interop assembly that provides access to a COM component. If using Microsoft Visual Studio .NET, add a reference to the Microsoft SQLDMO Object Library listed in the COM tab of the Add Reference dialog box. If you don't have Visual Studio .NET, use the Type Library Importer (Tlbimp.exe) to create an interop assembly for the sqldmo.dll file, which is usually located in the Tools\Binn folder below your SQL Server installation.

Note  

There is a known problem with the original SQLDMO Object Library that will cause the sample project in this recipe to fail. To run the project, you need to have installed SQL Server Service Pack 2 or higher.

Assuming you use default settings when generating your interop assembly, you first need to import the SQLDMO namespace. To obtain the list of available SQL Servers, instantiate a SQLDMO.Application object and call its ListAvailableSQLServers method. Each string in the returned SQLDMO.NameList object is the name of an accessible SQL Server. You can use the names in connection strings or display them in a list for a user to select. Here is an example that displays the names of all accessible SQL Servers to the console.

 using System; using SQLDMO; public class SQLDMOExample {     public static void Main() {         // Obtain a list of all available SQL Servers.         SQLDMO.Application app = new SQLDMO.Application();         SQLDMO.NameList names = app.ListAvailableSQLServers();         // Process the NameList collection.         if (names.Count == 0) {             Console.WriteLine("No SQL Servers visible on the network.");         } else {             // Display a list of available SQL Servers.             Console.WriteLine("SQL Servers visible : " + names.Count);             foreach (string name in names) {                 Console.WriteLine("  Name : " + name);             }         }         // Wait to continue.         Console.ReadLine();     } }