Re: Connecting to MSDE Thru VB Without Machine Name

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 23:33:18 +0200

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
>
>

Relevant Pages

  • Re: Connecting to MSDE Thru VB Without Machine Name
    ... Even with the new enumeration options shown in the PDC release of Whidbey, ... >> Unfortunately, in practice, the enumeration of SQL Servers is not ... >>> my instance name string, it uses the string to connect. ... >>> Dim oNames As SQLDMO.NameList ...
    (microsoft.public.sqlserver.msde)
  • Re: Connecting to MSDE Thru VB Without Machine Name
    ... > Unfortunately, in practice, the enumeration of SQL Servers is not ... >> my instance name string, it uses the string to connect. ... >> Dim oNames As SQLDMO.NameList ... >> Dim msgtext As String ...
    (microsoft.public.sqlserver.msde)
  • Connecting to MSDE Thru VB Without Machine Name
    ... my instance name string, it uses the string to connect. ... The installation of MSDE will always install an instance of "SQLINSTEQU". ... Dim oNames As SQLDMO.NameList ... Set oSQLApp = New SQLDMO.Application ...
    (microsoft.public.sqlserver.msde)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)
  • Auto Write Name and Merge across
    ... Dim Sheetname01 As String ... Dim WeekName01 As String ...
    (microsoft.public.excel.misc)