Multiple Instance data connection copy between similar/but not same db tables

• Mi Mu

  Newbie

  Points: 6

  More actions

  May 30, 2006 at 4:40 am

  #172735

  Lousy title, I know, but my problem is as follows;

  I have an online 2K SQL server which is replicated as an instance on my local machine.

  I have another 2K SQL server instance with a live certification db.  The certification DB has a similar data structure to the online version, but has some differences between the tables.

  I need to copy the latest data from my local machine, and transform it to match the live certification db.

  I feel I can work out the T-SQL fine using "where NOT IN xxxxx... " okay, my problem is connecting between the instances of the db.

  I have used the following --

  EXEC sp_addlinkedserver CERTIFICATION

  GO

  EXEC sp_addlinkedsrvlogin @rmtsrvname = 'server1\CERTIFICATION', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'XXXX'

  GO

  SELECT a.HospitalID

  FROM server1\CERTIFICATION.mastercertification.dbo.thistable a

  INNER JOIN server1.mastercertification.dbo.thistable b

  ON a.thisColumn = b.this Column

  GO

  My error is "The object name 'server1.CERTIFICATION.mastercertification.dbo' contains more than the maximum number of prefixes. The maximum is 3."

  I've tried slashes \ to no avail.

  Any pointers would be greatly appreciated. 

  Thanks, Michael

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 30, 2006 at 5:17 am

  #640641

  Michael

  Check out the topic Identifying a Data Source Using a Linked Server Name in Books Online.

  John

• Mi Mu

  Newbie

  Points: 6

  More actions

  May 30, 2006 at 5:34 am

  #640645

  Thanks, plowing through it as we speak . . .