Find Local SQL Servers when Off Network

We use SQLDMO.Application.ListAvailableSQLServers to show a list of all visible SQL Servers. This works great, until the user disconnects his network cable. Now I'd expect to lose the servers on other computers, but we also can't see the servers running on the local box.

After some googling, I'm aware that this is a known issue with ListAvailableSQLServers. Fine. However, if I use Enterprise Manager on the disconnected box, it can see the local instances just fine.

So, anybody here know the magic API call or other technique for listing the SQL Servers on the local box when the network is disconnected?

i don't know the enterprise manager magic api call...but i think what u are trying should work.

Couple of questions.

What version of mdac are u running?

if you go to one of the local servers and type
select @@servername
in query analyzer do you get null returned?

STRONGBAD ROCKS!!!

I'm running MDAC 2.7 SP1 on a WinXP SP1 box. The local instance is SQL Server 2000 SP3a.

The "select @@servername" returns my hostname, whether I'm connected to the network or not.

This isn't a big problem for me at my desk, but we have users with laptops who are irritated that their (local) instance disappears from our app when they undock.

>"STRONGBAD ROCKS!!!"

I know, I know. Can you believe it?

barogers wrote:

I'm running MDAC 2.7 SP1 on a WinXP SP1 box. The local instance is SQL Server 2000 SP3a.

The "select @@servername" returns my hostname, whether I'm connected to the network or not.

This isn't a big problem for me at my desk, but we have users with laptops who are irritated that their (local) instance disappears from our app when they undock.

hmmm...couple more questions ( as i have my support head on apparently ...

1 ) the users who see this problem are running mdac 7.1 on xp sp1 also? and if u do select @@servername on a failing machine u get the machine name back ok

2 ) if u do osql -L from a cmd prompt on a failing machine, does this return the full set ok?

3 ) The (local) name isn't being reported as the machine name in this case is it. Sounds dumb but i've seen it happen b4...

SQL Enterprise Manager can accomplish this because it looks at two different things.

SQL Servers announce themselves on the network using a special broadcast. SEM asks the computer through a Win32 API what announcement has it seen from a particular service. Next SEM then looks in the registry for any Alias you've created.

The SQLDMO.Application.ListAvailableSQLServers tries to wrap this functionality but from what you describe it seems to fail when disconnected from the network.

One thing to see how screwed up SQLDMO is is to use the loopback adapter to make sure you have the network stack loaded. If ListAvailableSQLServers works then it will tell you that ListAvailableSQLServers just won't work when the network stack isn't loaded.

You can replicate the behavior of SEM by using the following calls which you might be able to call from a CLR language.

Look at the NetServerEnum API. This call is looking for any Servers on the local net that indicate they support SV_TYPE_SQLSERVER. Then you would need to look in the registry under HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo to see what aliases are registered.

PS.
Another benefit of this approach is that it doesn't require SQLDMO to be installed.