enumerating registered sqlservers does not fill datatable complete

I've tried to enumerate sqlservers using two different methods and have the
same results... only the server name is returned, I am unable to get the
instance name so user will be unable to connect to selected server if it is
a
named instance (i.e. "Server"\SQLEXPRESS)...
Dim dt As DataTable
dt = SmoApplication.EnumAvailableSqlServers(False)
OR
Dim dt As DataTable
Dim instance As Sql.SqlDataSourceEnumerator =
Sql.SqlDataSourceEnumerator.Instance
dt = instance.GetDataSources
However I can go into SQL Server Management Studio, browse for servers and
every instance for every server (local and network) is listed... I assume
(foolishly) that MS Apps utilize thier own sdk's, if so why does it work
there but not for meOK I think I've found the culprit... the "Windows Firewall/Internet
Connection Sharing (ICS)" service
When I stop this service the enumerations return the Instance name and
Version of the SQL Servers... Hooray but what if Users have this service
running during my application installation? I'm suprised this isn't more of
a
known issue... I'm able to duplicate the issue on 2 machines both running XP
sp2 where one has SQL Server Express 2005 then other has SQL Server Express
2005 SP1, same results. Has anyone heard or exprienced this?

> I've tried to enumerate sqlservers using two different methods and have th
e
> same results... only the server name is returned, I am unable to get the
> instance name so user will be unable to connect to selected server if it i
s a
> named instance (i.e. "Server"\SQLEXPRESS)...
> Dim dt As DataTable
> dt = SmoApplication.EnumAvailableSqlServers(False)
> OR
> Dim dt As DataTable
> Dim instance As Sql.SqlDataSourceEnumerator =
> Sql.SqlDataSourceEnumerator.Instance
> dt = instance.GetDataSources
> However I can go into SQL Server Management Studio, browse for servers and
> every instance for every server (local and network) is listed... I assume
> (foolishly) that MS Apps utilize thier own sdk's, if so why does it work
> there but not for me|||On Tue, 13 Jun 2006 14:17:02 -0700, rqcoder
<rqcoder@.discussions.microsoft.com> wrote:
in <EF3759A0-3FE7-4839-B10E-E892752C02AB@.microsoft.com>

>OK I think I've found the culprit... the "Windows Firewall/Internet
>Connection Sharing (ICS)" service
>When I stop this service the enumerations return the Instance name and
>Version of the SQL Servers... Hooray but what if Users have this service
>running during my application installation? I'm suprised this isn't more of
a
>known issue... I'm able to duplicate the issue on 2 machines both running X
P
>sp2 where one has SQL Server Express 2005 then other has SQL Server Express
>2005 SP1, same results. Has anyone heard or exprienced this?
It happens on any machine that's running the firewall. It looks like
the tolerance window for the enumerations is extremely small - less than
a second. If anything hampers that initial query in the slightest then
the instance names don't show up. And I'm describing SQLDMO behavior
using VB6 but the enumerations code is most likely the same or extremely
similar.
What works well for me is Kerio Personal Firewall with specific rules
but that doesn't always wash well in the general user community.
Stefan Berglund

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