Connecting to MSDE Thru VB Without Machine Name

In order to avoid having to know the machine name, I am thinking about
using the following VB code to connect to an instance of MSDE.
It enumerates all possible SQL Servers and if it finds one with
my instance name string, it uses the string to connect.
I believe it was Andrea who showed me the SQLDMO stuff ...
The code does work on my machine - I am just wondering if it is a
good strategy when deploying the app. to a wide audience.
The installation of MSDE will always install an instance of "SQLINSTEQU".
Public Sub Init_App()
'
' First check that database is running.
'
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Dim Sqlserver_Running As String
Dim msgtext As String
Set oSQLApp = New SQLDMO.Application
Dim ws_Server_Str As String
Dim str_Pos As Integer
Dim ws_EquServer_Str As String
Set oNames = oSQLApp.ListAvailableSQLServers()
Sqlserver_Running = "NO"
'
' Search all available SQL Servers for a \\ServerName\InstanceName
' that has SQLINSTEQU as the instance name. If found, use the whole
' \\ServerName\InstanceName string to connect to the database.
' Hopefully this will work and then do not have to worry about machine
name.
'
For i = 1 To oNames.Count
ws_Server_Str = oNames(i)
strPos = InStr(ws_Server_Str, "SQLINSTEQU")
If strPos > 0 Then
ws_EquServer_Str = ws_Server_Str
Sqlserver_Running = "YES"
Exit For
End If
Next i
If Sqlserver_Running = "NO" Then
msgtext = "The database is not running. Please start your" & _
vbCr & "database and then re-start the application."
MsgBox msgtext
Set oSQLApp = Nothing
Set oNames = Nothing
End
End If
Set oSQLApp = Nothing
Set oNames = Nothing
'
' Establish a connection with the database using the OLE DB provider
' for SQL Server (SQLOLEDB). This provider does not need a data source
' or an existing ODBC driver. It is a native driver for MS SqlServer.
'
cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
";SERVER=" & ws_EquServer_Str & _
";UID=sa" & _
";PWD=abcdefg" & _
";DATABASE=Equ"
cn.Open
End Sub
Paul,
Unfortunately, in practice, the enumeration of SQL Servers is not reliable. I believe that Gert has some
technical elaborations about this on www.sqldev.net, please check that out before decoding on this strategy.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message news:OC7DHeZIEHA.3820@.tk2msftngp13.phx.gbl...
> In order to avoid having to know the machine name, I am thinking about
> using the following VB code to connect to an instance of MSDE.
> It enumerates all possible SQL Servers and if it finds one with
> my instance name string, it uses the string to connect.
> I believe it was Andrea who showed me the SQLDMO stuff ...
> The code does work on my machine - I am just wondering if it is a
> good strategy when deploying the app. to a wide audience.
> The installation of MSDE will always install an instance of "SQLINSTEQU".
>
> Public Sub Init_App()
> '
> ' First check that database is running.
> '
> Dim i As Integer
> Dim oNames As SQLDMO.NameList
> Dim oSQLApp As SQLDMO.Application
> Dim Sqlserver_Running As String
> Dim msgtext As String
> Set oSQLApp = New SQLDMO.Application
> Dim ws_Server_Str As String
> Dim str_Pos As Integer
> Dim ws_EquServer_Str As String
>
> Set oNames = oSQLApp.ListAvailableSQLServers()
> Sqlserver_Running = "NO"
> '
> ' Search all available SQL Servers for a \\ServerName\InstanceName
> ' that has SQLINSTEQU as the instance name. If found, use the whole
> ' \\ServerName\InstanceName string to connect to the database.
> ' Hopefully this will work and then do not have to worry about machine
> name.
> '
> For i = 1 To oNames.Count
> ws_Server_Str = oNames(i)
> strPos = InStr(ws_Server_Str, "SQLINSTEQU")
> If strPos > 0 Then
> ws_EquServer_Str = ws_Server_Str
> Sqlserver_Running = "YES"
> Exit For
> End If
> Next i
>
> If Sqlserver_Running = "NO" Then
> msgtext = "The database is not running. Please start your" & _
> vbCr & "database and then re-start the application."
> MsgBox msgtext
> Set oSQLApp = Nothing
> Set oNames = Nothing
> End
> End If
> Set oSQLApp = Nothing
> Set oNames = Nothing
> '
> ' Establish a connection with the database using the OLE DB provider
> ' for SQL Server (SQLOLEDB). This provider does not need a data source
> ' or an existing ODBC driver. It is a native driver for MS SqlServer.
> '
>
> cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
> ";SERVER=" & ws_EquServer_Str & _
> ";UID=sa" & _
> ";PWD=abcdefg" & _
> ";DATABASE=Equ"
>
> cn.Open
> End Sub
>
|||I searched that site you mentioned and could find nothing that said this
method was unreliable. On the contrary, I found an example that did just
that which can be found here:
http://www.sqldev.net/sqldmo/SamplesVB6.htm
I would appreciate it if you could back up your response with a link.
Thanks anyways ...
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB1$x7ZIEHA.720@.TK2MSFTNGP10.phx.gbl...
> Paul,
> Unfortunately, in practice, the enumeration of SQL Servers is not
reliable. I believe that Gert has some
> technical elaborations about this on www.sqldev.net, please check that out
before decoding on this strategy.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message
news:OC7DHeZIEHA.3820@.tk2msftngp13.phx.gbl...[color=darkblue]
"SQLINSTEQU".
>
|||I phrased it "in practice", but perhaps should have quoted the word "reliable" as well. Anyhow, check out
below:
http://www.sqldev.net/misc/OleDbEnum.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message news:OXBvaHaIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> I searched that site you mentioned and could find nothing that said this
> method was unreliable. On the contrary, I found an example that did just
> that which can be found here:
> http://www.sqldev.net/sqldmo/SamplesVB6.htm
> I would appreciate it if you could back up your response with a link.
> Thanks anyways ...
> Paul
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uB1$x7ZIEHA.720@.TK2MSFTNGP10.phx.gbl...
> reliable. I believe that Gert has some
> before decoding on this strategy.
> news:OC7DHeZIEHA.3820@.tk2msftngp13.phx.gbl...
> "SQLINSTEQU".
>
|||Tibor:
That is NOT the method I proposed.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23zGcXSaIEHA.828@.TK2MSFTNGP12.phx.gbl...
> I phrased it "in practice", but perhaps should have quoted the word
"reliable" as well. Anyhow, check out
> below:
> http://www.sqldev.net/misc/OleDbEnum.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message
news:OXBvaHaIEHA.3144@.TK2MSFTNGP10.phx.gbl...[color=darkblue]
in[color=darkblue]
out[color=darkblue]
about[color=darkblue]
whole[color=darkblue]
machine[color=darkblue]
source[color=darkblue]
SqlServer.
>
|||If you read the DMO documentation, you will see that the DMO ListAvailableSevers method uses the ODBC
SQLBrowseConnect function, which means that the weaknesses that this function has is also in the DMO method.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message news:uBvSUyaIEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Tibor:
> That is NOT the method I proposed.
> Paul
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23zGcXSaIEHA.828@.TK2MSFTNGP12.phx.gbl...
> "reliable" as well. Anyhow, check out
> news:OXBvaHaIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> in
> out
> about
> whole
> machine
> source
> SqlServer.
>
|||Hi Paul,
Believe Tibor on this one. He's dead right.
Even with the new enumeration options shown in the PDC release of Whidbey,
I've seen similar problems. Under the covers, most of these functions rely
on collecting browser packets.
Many of these "browse" type functions take ages to work on the network. Even
common browsing functions can take up to 24 minutes to settle to a stable
state. So, sometimes, they'll work, sometimes they won't.
Even searching for instances on the local system currently involves
searching the registry and making allowances for the different registry
structures created by different versions.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Paul McTeigue" <paul_mcteigue@.msn.com> wrote in message
news:OXBvaHaIEHA.3144@.TK2MSFTNGP10.phx.gbl...
> I searched that site you mentioned and could find nothing that said this
> method was unreliable. On the contrary, I found an example that did just
> that which can be found here:
> http://www.sqldev.net/sqldmo/SamplesVB6.htm
> I would appreciate it if you could back up your response with a link.
> Thanks anyways ...
> Paul
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
> message news:uB1$x7ZIEHA.720@.TK2MSFTNGP10.phx.gbl...
> reliable. I believe that Gert has some
out[vbcol=seagreen]
> before decoding on this strategy.
> news:OC7DHeZIEHA.3820@.tk2msftngp13.phx.gbl...
> "SQLINSTEQU".
machine
>
|||hi Paul,
"Paul McTeigue" <paul_mcteigue@.msn.com> ha scritto nel messaggio
news:uBvSUyaIEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Tibor:
>...
yep... Tibor is right... you always have to believe him =;-D
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 preceding 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 next generation troubles will depend on WinXP service pack 2, which will
default to close all ports on the internal firewall...
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Paul,
"Paul McTeigue" <paul_mcteigue@.msn.com> ha scritto nel messaggio
news:%23PePoymIEHA.2480@.tk2msftngp13.phx.gbl...
> Okay - I give - I will not use that method. The whole point was to avoid
> having to know
> the machine name - how do you guys solve this problem?
if you have to, you can resort on SQLBrowseConnect() provided by ODBC if you
do not need max precision, or give a try to the other methods like
NetServerEnum as described in http://sqldev.net/misc.htm
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||
> if you have to, you can resort on SQLBrowseConnect() provided by ODBC if
you
> do not need max precision, or give a try to the other methods like
> NetServerEnum as described in http://sqldev.net/misc.htm
> hth
Hi all
Interesting conversation, I have been reading about this lately and from
what I have read the NetServerEnum API function also has the same problem.
You must try to connect using NetQueryDisplayInformation to ensure you get
around the latency issue with the network browser.
Maybe there is no totally reliable / robust method to do this...
Regards
Daryl