Finding Database Servers / SQL Server Instances on Local Network - GetDataSources - DbDataSourceEnumerator

Source: David Hayden ( .NET Developer ), Filed: ADO.NET Tutorial

Have you ever known the answer to a problem, but could never get it to work yourself? :) I feel like that right now.

One of the common questions in the MSDN public forums is how to find a list of database servers or SQL Server instances on your local network so a user can choose which instance to connect to.

I have 3 ways one is supposed to be able to find local instances of SQL Server on the network, but for the life of me I cannot get them to work. I have spent an hour re-configuring an instance of SQL Server on my network to get these code samples to work and I always get nothing :(

Here is a more generic example using DbProviderFactories and DbProviderFactory:

 

DbProviderFactory factory =
   DbProviderFactories.GetFactory
    ("System.Data.SqlClient");

if (factory.CanCreateDataSourceEnumerator)
{
    DbDataSourceEnumerator dataSourceEnumerator =
              factory.CreateDataSourceEnumerator();
    
    if (dataSourceEnumerator != null)
    {
      // Here is the list of SQL Servers
      DataTable dt =
          dataSourceEnumerator.GetDataSources();
    }
}

 

Here is an example using SqlDataSourceEnumerator.Instance:

 

// List of SQL Server Instances on Local Network
DataTable dt = SqlDataSourceEnumerator.
                     Instance.GetDataSources();

 

Here is an example using SQL Server Management Objects:

 

// List of SQL Server Instances on Local Network
DataTable dt = SmoApplication.EnumAvailableSqlServers();

 

Hopefully you will have better luck than I with these examples. I am sure it is some configuration setting in SQL Server that allows one to browse a list of SQL Server Instances on the network, but I'll be dang if I know what combination of settings or services allows one to do this.

Source: David Hayden ( Sarasota .NET Developer )

 

posted on Friday, April 14, 2006 6:40 PM