Query to retrieve data from multiple databases on different servers ...
Posted on 2004-10-12
I'm wondering if it is possible to develop a query that retrieves data from multiple databases that are on different servers.
This is the situation :
On Server1 we have DB1 with a table called SEAT (PKey = Seat_ID.
On Server2 we have DB2 with a table called SEAT_TESTRESULT (Foreign key = Seat_ID).
I want a query that shows the TestResults (DB2.SEAT_TESTRESULT) for a certain Seat (DB1.SEAT).
So, when both tables were in the same Db, the query should look like this :
SELECT s.*, sr.*
FROM SEAT s, SEAT_TESTRESULT sr
WHERE s.Seat_ID = sr.Seat_ID
AND s.Seat_ID = 1234567
But how will the query look like if the 2 DB's are on different servers ?
What have to be done to setup 2 connections at the same time (because you are querying 2 db's on different servers, I suppose you need to have 2 connections open).
How is this all managed ?
We use SQL Server 2000 on a Windows2000 OS.
I hope what is ask is possible (I suppose it is).
Thanks in advance for your comments,