The servers must be linked (See SQL Books Online, Security --> Linked Servers) through Enterprise Manager or a stored procedure
This example compares a table called "table1yr" in the sampledb on two different servers SQL06 and SQL03.
Code:
SELECT DISTINCT * FROM SQL06.sampledb.dbo.table1yr
WHERE ITEMID in
(
select DISTINCT ItemID from SQL06.sampledb.dbo.table1yr
WHERE ITEMID NOT IN
(
SELECT ITEMID FROM SQL03.sampledb.dbo.table1yr
)
)
JOIN SQL server tables from different servers
Posted: Tue Dec 28, 2004 10:53 am
BB
Regular
Joined: Jun 23, 2004
Posts: 340
Subject: JOIN SQL server tables from different servers
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:
Code:
EXEC sp_addlinkedserver SERVER_01
GO
Link a login between two servers. (This is an example! You should not link the sa user! Also, the sa password should not be 'sa'!)
Code:
/* 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
Here is a sample query:
Code:
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
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest (c) 2006 by Group29 Productions.
You can syndicate Group29 Productions news with an RSS Feeder using the file backend.php