CODEWITHSUNDEEP
sqlsql-server
Kade Williams
Kade Williams

Reputation: 1191

Match columns from 2 tables on 2 different servers

I have 2 databases on their own server. I want to compare a FIRSTNAME and LASTNAME columns that is located in a table on each database.

If the first database has a firstname and lastname combo that matches the firstname and lastname columns in the second database, I want to display that name.

I just want to know which name is listed in both database servers.

I tried the below but I still get an error:

Could not find server 'sql01' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

My SQL query:

EXEC sp_addlinkedserver @server='sql01'

SELECT LASTNAME, FIRSTNAME, STATUS, CLASS
  FROM sql01.database1.dbo.MyTable1 T1
  WHERE EXISTS (SELECT FIRSTNAME, LASTNAME 
                FROM app02.database2.dbo.MyTable2 T2
                WHERE T1.FIRSTNAME = T2.FIRSTNAME
                AND T1.LASTNAME = T2.LASTNAME)

My environment:

I am running the query in SQL Server while already being connected to app02.

Any suggestions?

Upvotes: 0

Views: 84

Answers (2)

Syntax Error
Syntax Error

Reputation: 1640

Include the instance name with sp_addlinkedserver (sql01\SQLEXPRESS, or something similar depending on the version of SQL server)

Also you need to run another Stored procedure to provide login details for the linked server

EXEC sp_addlinkedsrvlogin 'Servername', 'false', NULL, 'username', 'password'

Upvotes: 1

tysonwright
tysonwright

Reputation: 1525

You need to add the server and the server login:

EXEC sp_addlinkedserver @server='sql01'

EXEC sp_addlinkedsrvlogin 'sql01', 'false', NULL, 'username', 'password'

Upvotes: 0

Related Questions