EnumAvailableSqlServers() returns nothing when there is no network connection

I am having a weird issue. I am using SMO's EnumAvailableSqlServers() method to fill a drop-down box. If I am connected to a network, it shows all networked Sql Servers, including the local instance. When I am not connected to any network, it shows nothing. Even if I connect to an ad-hoc wireless network, it works correctly, showing only the local instance, but if the wireless card is removed (and no other networks are enabled) it is blank. SQL Server Browser is running. I am running both SQL Server and SQL Browser services under a local administrator account (for testing).

The problem is that these are handheld devices and sometimes they will be on a network, and other times they will be inside secure facilities in which no network devices are ever allowed. So, it needs to work in either condition. And really, it's absurd that the method cannot work in an unnetworked environment - a major slip-up by MS that is about to force us to embedded linux.

So, two questions:

1. Is there a way to fix this such that it will fallback correctly and look for local instances when no network is available?

2. If not, is there a known way to "trick" a machine into thinking a network is there so this will run correctly, such as some sort of network driver that emulates a connected network?

Hello David,

The EnumAvailableSqlServers is based off of the ADO.NET SqlDataSourceEnumerator class. The implementation is based on UDP broadcast, with a timeout, so you need a network connection to run the enumeration. Here are some other good things to know about the method: it may not reliably see all servers respond before the timeout; it will not find SQL Server if the SQLBrowser switched off, or the server is marked as hidden; and the method will also fail if the local instance blocks TCP/IP Port 1433 and UDP 1434.
But all is not lost. Here is code snippet that uses SMO Wmi to retrieve a reliable list the local SQL Server 2005 instances on a machine without network access.

ManagedComputer mc = new ManagedComputer();

// Setup the collection of servers

foreach (ServerInstance i in mc.ServerInstances)

{

string servername = i.Name;

if (i.Name == "MSSQLSERVER") // This is the default instance

{

servername = ".";

} else {

servername = ".\\" + i.Name;

}

Console.WriteLine(servername);

}

Good luck and let us know if you have any more questions,

Jennifer

This posting is provided "as is" with no warranties, and confers no rights.

|||Thanks, I will attempt to implement that tomorrow. It will make everyone very happy if it works out.

The million dollar question, where is this type of thing documented so I don't run into such problems down the road? Reminds me of using API calls back in VB5 days.
|||

MSDN documents the SMO class library at

http://msdn2.microsoft.com/en-us/library/bb283710.aspx

The EnumAvailableSqlServers() is at

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.enumavailablesqlservers.aspx

And ServerInstance is at

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.serverinstance.aspx

Best of luck,

Jennifer Beckmann

|||

Hi David,

I have been down this road many times and what surprises me is that the answer is the same no matter where I look.

As you have found the EnumAvailableSqlServers() is not very reliable and requires too many stipulations to work such as SQLBrowser, Firewall settings, Network connection etc. Even if you get all that right you will find that it is still hit or miss. And good luck getting any earlier versions of SQL Server to show up including SQL Express. Jennifer's solution is great if you are only dealing with SQL 2005.

However I have discovered a slightly different approach that has not failed me yet:

Code Snippet

Dim SQLServers As RegisteredServers.RegisteredServerCollection = SmoApplication.SqlServerRegistrations.RegisteredServers

This will pick up SQL 2005, SQL Express, SQL 2000 and MSDE instances without any stipulations. No firewall issues, no network issues, no SQL Browser issues. You get the same complete list every time. And correctly named too I might add.

Hope this helps you and many more frustrated folks,

Jamie

|||Hi David,
I think the easiest solution is that you need to make sure that your SQL Browser Service is up and running and set to auto start. good luck.
Chan