Checking Existence of Available SQL Server

Of late, I had read an article about determining list of SQL Servers that are available to your application.

.NET 1.1
There is no way but you need to use COM-based SQLDMO to have an interop call.

using SQLDMO;

try
{
NameList objSQLList;
ApplicationClass objSQLApp = new SQLDMO.ApplicationClass();

objSQLList = objSQLApp.ListAvailableSQLServers();

foreach(string name in objSQLList)
Response.Write(name + "
");
}
catch(Exception ex)
{

}
finally
{
objSQLApp.Quit();
}

* You have to reference the SQLDMO.dll, which located at :\Program Files\Microsoft SQL Server\\Tools\Binn by default.


.NET 2.0
It is much easier. You can retrieve the list of servers available using System.Data.Sql namespace, by enumerating each of the server instance.

SqlDataSourceEnumerator enumerator = SqlDataSourceEnumerator.Instance;
DataTable datatable1 = enumerator.GetDataSources();
foreach (DataRow row in datatable1.Rows)
{
Response.Write("Server Name:" + row["ServerName"] + "
");
Response.Write("Instance Name:" + row["InstanceName"] + "
");
}

Sometimes, these methods are better trying to connect the DB Server to see whether it is succesfully connected, but the output not always accurate due to :

1. Invalid Login / User credentials
2. Timeout due to network congestions


Neat !