Re: Problem with ListAvailableSQLServers in vb 6
From: Allcomp (marc_at_nospam.allcomp.be)
Date: 01/04/05
- Next message: Gert E.R. Drapers: "Re: Problem with ListAvailableSQLServers in vb 6"
- Previous message: Pluno: "setup failed to configure server"
- In reply to: Andrea Montanari: "Re: Problem with ListAvailableSQLServers in vb 6"
- Next in thread: Gert E.R. Drapers: "Re: Problem with ListAvailableSQLServers in vb 6"
- Reply: Gert E.R. Drapers: "Re: Problem with ListAvailableSQLServers in vb 6"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 04 Jan 2005 08:22:19 +0100
Hello,
My enabled networks protocols are Named pipes and Tcp/IP
With EnumSQLSvr.exe, it doesn't work (when I remove the network cable, I
have no server). I have exactly the same error with Osql -L
I use WinXP SP2 without firewall for my tests.
With SQL Server 2000, a Win98 computer is in the list
I have also seen something strange...
I make the ListAvailableSQLServers
If I have a return count of 0, then I use
the object
SQLDMO.SQLServer2
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
will return the local computer when no network is enabled.
Thank you
Marc Allard
Allcomp
Andrea Montanari wrote:
> 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
- Next message: Gert E.R. Drapers: "Re: Problem with ListAvailableSQLServers in vb 6"
- Previous message: Pluno: "setup failed to configure server"
- In reply to: Andrea Montanari: "Re: Problem with ListAvailableSQLServers in vb 6"
- Next in thread: Gert E.R. Drapers: "Re: Problem with ListAvailableSQLServers in vb 6"
- Reply: Gert E.R. Drapers: "Re: Problem with ListAvailableSQLServers in vb 6"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
