MSDE not showing up in SQLDMO.ListAvailableServers

I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
server. I installed a copy of MSDE on the users' machine (this machine did
not have MSDE previously so it was a new copy including latest SP3), then
installed my app. When I start my app, the local computer name does not
show up in the list of available servers. This has happened twice now on
separate machines. Is there something I need to do after installing MSDE
for it to register as a server? Note: I know MSDE is installed and running
because my app creates and properly fills databases on the local machine, it
just doesnt show up in the list of available servers.
Thanks
Dave Taylor
I had the same problem. SQL-DMO does two things when it enumerates SQL
Servers:
1. It creates a list of SQL Servers using the Win32 API NetServerEnum
function
2. It creates a list of SQL Servers that respond to a broadcast on Port
1433
3. It combines these two lists.
If you're not connected to a network, NetServerEnum doesn't seem to return
any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
won't pick up the broadcast. If you're just trying to enumerate the LOCAL
MSDE instance(s), you can read the registry subkeys under
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
HKLM\Software\Microsoft\Microsoft SQL Server.
If anyone out there knows of a function that can be used to return named
instances of MSDE, I'd appreciate the info.
Thanks,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>
|||Michael,
Thanks for the reply and info. At least now I know it isn't something I was
doing wrong.
I'll use your registry workaround for the local info.
Thanks again
Dave Taylor
"Michael C" <michaelc@.nospam.org> wrote in message
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...[vbcol=seagreen]
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
> Thanks,
> Michael C.
>
> "Dave Taylor" <nospam_dave@.processeng.com> wrote in message
> news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
available[vbcol=seagreen]
> did
then[vbcol=seagreen]
on[vbcol=seagreen]
MSDE[vbcol=seagreen]
> running
machine,
> it
>
|||No problem. I just found a website that has a utility for retrieving all
SQL Server information from all computers on a specified subnet via a UDP
packet sent to Port 1434. I tested it on my home network (I don't have MSDE
installed right now though). It might be another way to go... but right
now I'm not sure if it's robust enough for my purposes. Anyway, here's the
link http://www.sqlsecurity.com/DesktopDe...aspx?tabid=26. (One version
includes C# source code). If you do try the SQLPing utility, please let me
know if it returns MSDE info. also.
Enjoy,
Michael C.
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:%234E8NcadEHA.3392@.tk2msftngp13.phx.gbl...
> Michael,
> Thanks for the reply and info. At least now I know it isn't something I
was[vbcol=seagreen]
> doing wrong.
> I'll use your registry workaround for the local info.
> Thanks again
> Dave Taylor
> "Michael C" <michaelc@.nospam.org> wrote in message
> news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...
return[vbcol=seagreen]
LOCAL[vbcol=seagreen]
> available
machine[vbcol=seagreen]
> then
not
> on
> MSDE
> machine,
>
|||hi Michael,
"Michael C" <michaelc@.nospam.org> ha scritto nel messaggio
news:oicOc.19466$oE1.1984378@.news4.srv.hcvlny.cv.n et...
> I had the same problem. SQL-DMO does two things when it enumerates SQL
> Servers:
> 1. It creates a list of SQL Servers using the Win32 API NetServerEnum
> function
> 2. It creates a list of SQL Servers that respond to a broadcast on Port
> 1433
> 3. It combines these two lists.
> If you're not connected to a network, NetServerEnum doesn't seem to return
> any SQL Servers. MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast. If you're just trying to enumerate the LOCAL
> MSDE instance(s), you can read the registry subkeys under
> HKLM\Software\Microsoft\MSSQLServer\MSSQLServer and
> HKLM\Software\Microsoft\Microsoft SQL Server.
> If anyone out there knows of a function that can be used to return named
> instances of MSDE, I'd appreciate the info.
SQL-DMO 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.
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.
next problem will be WinXP sp 2 firewall, which will default closing all
ports so that single ports must be manually open by the user in order to
allow network connections... this will defeat dynamic TCP/IP allocation as
you should rely on static assignement for Named instances too...
further method to enlist SQL Server instances available on the LAN can be
found at http://www.sqldev.net/misc/EnumSQLSvr.htm ,
http://www.sqldev.net/misc/ListSQLSvr.htm and
http://www.sqldev.net/misc/OleDbEnum.htm
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>MSDE runs on Port 1434 instead of 1433, which means it
> won't pick up the broadcast.
default instance always default on port 1433 TCP/IP, while UDP1434 is used
(if network protocols have been enabled) for dynamic named instances
listening in order to redirect to the appropriate TCP/IP dialog port
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||
> 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.
Then again, you might want to quit using SQL-DMO for this,
as Yukon removes SQL-DMO again :-(
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
|||Yukon does not remove DMO, it replaces the 2000 version with DMO9. It does
add a new API called SMO which is the new management API going fwds.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Martijn Tonies" <m.tonies@.upscene-remove-.com> wrote in message
news:ucF3#QbdEHA.3864@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> logic
broadcasts,[vbcol=seagreen]
> have
of
> Then again, you might want to quit using SQL-DMO for this,
> as Yukon removes SQL-DMO again :-(
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
SQL
> Server.
> Upscene Productions
> http://www.upscene.com
>
|||Dave,
Note that the current MSDE (2000RevA) install disables the network
connections by default. You will have to enable them using the svmetcn.exe
in the 80\Tools\Binn directory under the install directory. This was
supplied by the current install and I now have my MSDE responding to network
queries on port 1433.
You can also just put '(local)' in as the server name and you should get
a connection locally.
Ron Allen
"Dave Taylor" <nospam_dave@.processeng.com> wrote in message
news:OtXGtZZdEHA.3944@.tk2msftngp13.phx.gbl...
> I have a VB.NET app using MSDE and the SQL-DMO to get a list of available
> server. I installed a copy of MSDE on the users' machine (this machine
did
> not have MSDE previously so it was a new copy including latest SP3), then
> installed my app. When I start my app, the local computer name does not
> show up in the list of available servers. This has happened twice now on
> separate machines. Is there something I need to do after installing MSDE
> for it to register as a server? Note: I know MSDE is installed and
running
> because my app creates and properly fills databases on the local machine,
it
> just doesnt show up in the list of available servers.
> Thanks
> Dave Taylor
>