SQL-DMO - Problem connecting to remote database

  • I'm trying to establish a connection to a Remote SQL Server database

        Dim oServer

        Set oServer = CreateObject("SQLDMO.SQLServer")

        tConnection = "\\" & REMOTE_IP & "\" & REMOTE_GROUP

        oServer.AutoReConnect = False               ' this allows the disconnect event to be trapped

        oServer.LoginSecure = False

        oServer.LoginTimeout = -1

        oServer.Connect tConnection, REMOTE_USERNAME, REMOTE_PASSWORD

    Connecting locally doesn't work either...

        tConnection = "(local)" & "\" & REMOTE_GROUP
    What am I doing wrong?

    All I get is:

    Err.Number: 20482

    Err.Description: [SQL-DMO]This server object is not connected.

    I have the Event trapped:-

    Private Sub oServer_RemoteLoginFailed( Severity, _

                                           MessageNumber, _

                                           MessageState, _

                                           Message )

    but the process is not getting there

    Language: VBScript

    Platform: XP (eventually NT/2000)

    SQL-DMO: Installed (entries in registry)

    Thanks

    J:

    x-post: Forums: VBScript, Visual Basic, SQL Server

  • don't you  need to define the port number opened for sql server?

    also how remote is remote? different domain? different location?

  • I finally got it working by switching to ADO.

    REMOTE_IP = "1.1.1.1"

    REMOTE_DATABASE = "DBname" 

    REMOTE_USERNAME = "username"

    REMOTE_PASSWORD = "password"

    DB_CONNECT_STRING = "Server='" & REMOTE_IP & "';" & _

        "Driver='(SQL Server)';" & _

        "Database='" & LOCAL_DATABASE & "';" & _

        "user id = '" & LOCAL_USERNAME & "';" & _

        "password='" & LOCAL_PASSWORD & "'"

    Dim oLocal

    Set oLocal = CreateObject( "ADODB.Connection" )

    With oLocal

        .Provider = "SQLOLEDB.1"

        .CommandTimeout = 60

        .ConnectionString = DB_CONNECT_STRING

        On Error Resume Next

        .Open

    End With

    That worked...

    Thanks for the response anyway!

    J.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply