SQL Server FAQ : How to join tables from different servers?
To be able to join tables between two SQL Servers, first you have to link them. After the linked servers are setup, you just have to prefix your tables names with server name, database name, table owner name in your SELECT queries. The following example links SERVER_01 to SERVER_02. Execute the following commands in SERVER_02:
EXEC sp_addlinkedserver SERVER_01
GO
/* The following command links 'sa' login on SERVER_02 with the 'sa' login of SERVER_01 */
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SERVER_01', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'sa password of SERVER_01'
GO
SELECT a.title_id
FROM SERVER_01.pubs.dbo.titles a
INNER JOIN SERVER_02.pubs.dbo.titles b
ON a.title_id = b.title_id
GO