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

Enterprise Manager vs. INSTEAD OF triggers?

Hello all, i seem to have run into something weird...
I have a view which joins several tables (one main, and several relational
lookups). The view is simple-updateable, but i overrode this with an INSTEAD
OF UPDATE trigger to prevent people from changing the key fields, or the
lookup values. This works fine from Query Analyzer, or Access. If i try to
update one of the other fields, it simply doesnt take but the fields which
do get handled by the trigger do... all works fine.
However, if i open the view in Ent. Mgr. and type in changes to one of the
"non-updatable" fields, it actually updates it. It appears to be that when
making changes to data through the Enterprise Manager data views, it
bypasses any triggers (or at least INSTEAD OF triggers).
Is this actually the case? It seems kind of screwey, but that would seem to
be borne out by my little experiments here.
Cheers,
- Arthur.
When you open a view and update the data from EM, the UPDATE statemenet is
issued directly to the table, not to the view. That's why the trigger is
bypassed.
"Arthur Dent" wrote:

> Hello all, i seem to have run into something weird...
> I have a view which joins several tables (one main, and several relational
> lookups). The view is simple-updateable, but i overrode this with an INSTEAD
> OF UPDATE trigger to prevent people from changing the key fields, or the
> lookup values. This works fine from Query Analyzer, or Access. If i try to
> update one of the other fields, it simply doesnt take but the fields which
> do get handled by the trigger do... all works fine.
> However, if i open the view in Ent. Mgr. and type in changes to one of the
> "non-updatable" fields, it actually updates it. It appears to be that when
> making changes to data through the Enterprise Manager data views, it
> bypasses any triggers (or at least INSTEAD OF triggers).
> Is this actually the case? It seems kind of screwey, but that would seem to
> be borne out by my little experiments here.
> Cheers,
> - Arthur.
>
>
|||Arthur Dent wrote:
> Hello all, i seem to have run into something weird...
> I have a view which joins several tables (one main, and several
> relational lookups). The view is simple-updateable, but i overrode
> this with an INSTEAD OF UPDATE trigger to prevent people from
> changing the key fields, or the lookup values. This works fine from
> Query Analyzer, or Access. If i try to update one of the other
> fields, it simply doesnt take but the fields which do get handled by
> the trigger do... all works fine.
> However, if i open the view in Ent. Mgr. and type in changes to one
> of the "non-updatable" fields, it actually updates it. It appears to
> be that when making changes to data through the Enterprise Manager
> data views, it bypasses any triggers (or at least INSTEAD OF
> triggers).
> Is this actually the case? It seems kind of screwey, but that would
> seem to be borne out by my little experiments here.
> Cheers,
> - Arthur.
I'm not seeing that behavior here. I created an instead of update
trigger on a table and SQL EM spits out an error when the trigger is
violated. Here's the code. What do you see when you change a date to
value greater than today?
create table test (col1 int identity not null, col2 datetime not null)
go
insert into test values (getdate())
insert into test values (getdate() - 1)
insert into test values (getdate() - 2)
go
create trigger test_ioi on test
instead of update
as
begin
If Not Exists (Select * from inserted where col2 > getdate())
Update test
Set col2 = i.col2
From test, inserted i
Where test.col1 = i.col1
Else
RAISERROR ('Date provided is greater than today'' date. Update
failed.', 16, 1)
end
go
David Gugick
Imceda Software
www.imceda.com
|||My trigger is on a VIEW, not on a table. Jack answered above though, and
apparently explained why i get this behaviour.
In EM, when you open a view, its not actually opening the view, but the
underlying tables, only using the view definition. So triggers on the view
DO get bypassed.
|||Just an FYI, there is not such thing as "opening" a table or a view. EM does SELECT against the view
but issue the UPDATE/INSERT/DELETE against the table. Check out the WITH VIEW_METADATA option when
you create the view to handle this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:egeVjksOFHA.2468@.tk2msftngp13.phx.gbl...
> My trigger is on a VIEW, not on a table. Jack answered above though, and apparently explained why
> i get this behaviour.
> In EM, when you open a view, its not actually opening the view, but the underlying tables, only
> using the view definition. So triggers on the view DO get bypassed.
>