SQL-DMO - Problem connecting to remote database

• Jane Alford

  SSC Enthusiast

  Points: 132

  More actions

  March 9, 2006 at 7:32 am

  #96678

  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

• Jen-169507

  Right there with Babe

  Points: 776

  More actions

  March 10, 2006 at 1:48 am

  #625865

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

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

• Jane Alford

  SSC Enthusiast

  Points: 132

  More actions

  March 10, 2006 at 7:18 am

  #625950

  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.