Enumerating Instances of SQL Server 

Both SQL Server 2000 and SQL Server 2005 permit applications to find SQL Server instances within the current network. The SqlDataSourceEnumerator class exposes this information to the application developer, providing a DataTable containing information about all the visible servers. This returned table contains a list of server instances available on the network that matches the list provided when a user attempts to create a new connection, and expands the drop-down list containing all the available servers on the Connection Properties dialog box. The results displayed are not always complete.

Note

As with most Windows services, it is best to run the SQL Browser service with the least possible privileges. See SQL Server Books Online for more information on the SQL Browser service, and how to manage its behavior.

Retrieving an Enumerator Instance

In order to retrieve the table containing information about the available SQL Server instances, you must first retrieve an enumerator, using the shared/static Instance property:

Dim instance As System.Data.Sql.SqlDatSourceEnumerator = _
   System.Data.Sql.SqlDataSourceEnumerator.Instance
System.Data.Sql.SqlDataSourceEnumerator instance = 
   System.Data.Sql.SqlDataSourceEnumerator.Instance

Once you have retrieved the static instance, you can call the GetDataSources method, which returns a DataTable containing information about the available servers:

Dim dataTable As System.Data.DataTable = instance.GetDataSources()
System.Data.DataTable dataTable = instance.GetDataSources();

The table returned from the method call contains the following columns, all of which contain string values:

Column Description

ServerName

Name of the server.

InstanceName

Name of the server instance. Blank if the server is running as the default instance.

IsClustered

Indicates whether the server is part of a cluster.

Version

Version of the server (8.00.x for SQL Server 2000, and 9.00.x for SQL Server 2005).

Enumeration Limitations

All of the available servers may or may not be listed. The list can vary depending on factors such as timeouts and network traffic. This can cause the list to be different on two consecutive calls. Only servers on the same network will be listed. Broadcast packets typically won't traverse routers, which is why you may not see a server listed, but it will be stable across calls.

Listed servers may or may not have additional information such as IsClustered and version. This is dependent on how the list was obtained. Servers listed through the SQL Server browser service will have more details than those found through the Windows infrastructure, which will list only the name.

Note

Server enumeration is only available when running in full-trust. Assemblies running in a partially-trusted environment will not be able to use it, even if they have the SqlClientPermission Code Access Security (CAS) permission.

SQL Server 2000 provides information for the SqlDataSourceEnumerator internally. SQL Server 2005, however, provides the information through the use of an external Windows service named SQL Browser. This service is enabled by default, but administrators may turn it off or disable it, making the server instance invisible to this class. This service applies only to SQL Server 2005, and has no effect on the behavior of SQL Server 2000.

Note

Hardware or software may limit the ability to browse for SQL Server instances. For more information on opening ports for browsing, see the Knowledge Base article, How to use a script to programmatically open ports for SQL Server to use on systems that are running Windows XP Service Pack 2.

Example

The following console application retrieves information about all of the visible SQL Server instances and displays the information in the console window.

Imports System.Data.Sql

Module Module1
  Sub Main()
    ' Retrieve the enumerator instance and then the data.
    Dim instance As SqlDataSourceEnumerator = _
     SqlDataSourceEnumerator.Instance
    Dim table As System.Data.DataTable = instance.GetDataSources()

    ' Display the contents of the table.
    DisplayData(table)

    Console.WriteLine("Press any key to continue.")
    Console.ReadKey()
  End Sub

  Private Sub DisplayData(ByVal table As DataTable)
    For Each row As DataRow In table.Rows
      For Each col As DataColumn In table.Columns
        Console.WriteLine("{0} = {1}", col.ColumnName, row(col))
      Next
      Console.WriteLine("============================")
    Next
  End Sub
End Module
using System.Data.Sql;

class Program
{
  static void Main()
  {
    // Retrieve the enumerator instance and then the data.
    SqlDataSourceEnumerator instance =
      SqlDataSourceEnumerator.Instance;
    System.Data.DataTable table = instance.GetDataSources();

    // Display the contents of the table.
    DisplayData(table);

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
  }

  private static void DisplayData(System.Data.DataTable table)
  {
    foreach (System.Data.DataRow row in table.Rows)
    {
      foreach (System.Data.DataColumn col in table.Columns)
      {
        Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
      }
      Console.WriteLine("============================");
    }
  }
}

See Also

Other Resources

Using the .NET Framework Data Provider for SQL Server