Determine whether a SQL Server exists ..

Posted on 9/17/2006 @ 11:57 AM in #Vanilla .NET by | Feedback | 4874 views

"Does this SQL Server exist or not"? That is a good question.

I noticed this question come up in the ADO.NET public newsgroup recently. Most of the answers are "DUhh .. try connecting to it". Of course, That is the straightforward approach to this question is - lets try and connect to it, if it connects - it must exist eh?

I feel that approach is incorrect for two reasons:

a) Your SQL Server may not accept your credentials - or the pool may be full. And yet the SQL Server did exist, but didn't feel like talking with you.
b) You will be stuck due to connection timeout issues. Thus creating an unresponsive application.

So you need to answer the real question - "Does this SQL Server exist on my network or not?"

Here is how you do it. If you can live with the assumption that your server is UDP pingable, you can enumerate through the server's list and find a match. If the server is not UDP pimgable, but is TCP connectable - you will need to connect over 1433 and run a sample TDS (sorry that is the only reliable way - though in most circumstances you won't have to do it).

So the Q is, how can you enumerate SQL Servers on a network?

Basically In .NET 1.x, you have no choice but to run interop calls to server using SQLDMO. Here is some code fer ya -

Dim i As Integer
Dim
oNames As
SQLDMO.NameList
Dim oSQLApp As
SQLDMO.Application
Set oSQLApp = New
SQLDMO.Application

Set
oNames = oSQLApp.ListAvailableSQLServers()
List1.Clear
For i = 1 To
oNames.Count
   Console.WriteLine oNames.Item(i)
Next i

The above, was unmanaged code because SQLDMO is COM based. In .NET 2.x you have a somewhat of a better choice. You can simply use System.Data.Sql to enumerate over various servers available - and you can get this information as a DataTable (and thus execute DataTable.Select)

SqlDataSourceEnumerator sqldatasourceenumerator1 = SqlDataSourceEnumerator.Instance;
DataTable datatable1 = sqldatasourceenumerator1.GetDataSources();

foreach
(DataRow row in datatable1.Rows)
{
   Console.WriteLine(
"Server Name:"+row["ServerName"]);
   Console.WriteLine(
"Instance Name:"+row["InstanceName"]);
}
 

Neat eh? :-)

Sound off but keep it civil:

Older comments..