Re: Connecting to MSDE Thru VB Without Machine Name

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Paul McTeigue (paul_mcteigue_at_msn.com)
Date: 04/13/04

  • Next message: Phil: "Writting records to a MSDE DB from a separate SQL DB" 
    Date: Tue, 13 Apr 2004 19:09:50 -0400

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

  • Next message: Phil: "Writting records to a MSDE DB from a separate SQL DB"

    Relevant Pages

    • Re: mail attachment problem
      ... Paul ... > Tibor Karaszi, SQL Server MVP ... >> found a problem with the email attachment. ...
      (microsoft.public.sqlserver.server)
    • Re: problem with query moving data from 1 dbase to another.
      ... Tibor Karaszi, SQL Server MVP ... "Paul" wrote in message ... > Syntax error converting the nvarchar to int. ... > Data_Item_Log_ID is type integer and the source field> (from another dbase on the same server) is type nvarchar 4. ...
      (microsoft.public.sqlserver.server)
    • Re: Trigger help please
      ... Tibor Karaszi, SQL Server MVP ... "Paul in Harrow" wrote in message ... >> the INSERT statement inside the trigger. ...
      (microsoft.public.sqlserver.programming)
    • Re: Concatenation
      ... If you're referring to the N in the statement I have no ... Paul ... >SQL Server MVP ... it's just the name field is empty. ...
      (microsoft.public.sqlserver.programming)
    • Re: mail attachment problem
      ... Tibor Karaszi, SQL Server MVP ... "Paul Godward" wrote in message ... > found a problem with the email attachment. ... Excel gives an error message that says "This file is not in a ...
      (microsoft.public.sqlserver.server)