Re: Problem with ListAvailableSQLServers in vb 6
From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 01/03/05
- Next message: Gautam Lad: "Running/Starting MSDE Server as Power User automatically"
- Previous message: Allcomp: "Problem with ListAvailableSQLServers in vb 6"
- In reply to: Allcomp: "Problem with ListAvailableSQLServers in vb 6"
- Next in thread: Allcomp: "Re: Problem with ListAvailableSQLServers in vb 6"
- Reply: Allcomp: "Re: Problem with ListAvailableSQLServers in vb 6"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 3 Jan 2005 16:32:59 +0100
hi Marc,
"Allcomp" <marc@nospam.allcomp.be> ha scritto nel messaggio
news:41d95753$0$329$ba620e4c@news.skynet.be
> Hello,
>
> I have got a very strange problem...
>
> In VB6, in the beginning of my application, I make a
> ListAvailableSQLServers to see what MSDE servers are on the network
> for my application.
> The problem is that if a computer is disconnected from the network,
> but has MSDE working (a laptop) then the ListAvailableSQLServers will
> return no server, even if there is a local server.
> Is there something to do to have it?
>
> PS : In VB6, I use
>
> dim ListSvr as collection
> Set ListSvr = SQLDMO.ListAvailableSQLServers
>
> and the count is 0
>
> I have also tried with
>
> Dim oServer As New SQLDMO.SQLServer2
> Dim oNameList As SQLDMO.NameList
>
> Set oNameList = oServer.Application.ListAvailableSQLServers '
>
> and oNameList.Count is still 0
>
can you please verify your local MSDE instance has network protocols enabled
via Server Network Utility (svrnetcn.exe)?
if network protocols are disabled (default for MSDE installation), the
relative instance will not be enlisted in the broadcast call for server
enumeration..
as regard ListAvailableServer method provided by SQL-DMO object model, I've
found during my (little) experience, this sort of rules:
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
.ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
Courtesy of Mr. Gert E.R. Drapers
further Information at
http://sqldev.net/misc.htm
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
The latest problem has been added by Windows XP service pack 2, which
implements a strong protection of the local computer closing all ports for
incoming and outgoing connections, requiring to manually open the desired IP
port in order to allow external remote connections
-- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
- Next message: Gautam Lad: "Running/Starting MSDE Server as Power User automatically"
- Previous message: Allcomp: "Problem with ListAvailableSQLServers in vb 6"
- In reply to: Allcomp: "Problem with ListAvailableSQLServers in vb 6"
- Next in thread: Allcomp: "Re: Problem with ListAvailableSQLServers in vb 6"
- Reply: Allcomp: "Re: Problem with ListAvailableSQLServers in vb 6"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
