asked on

Query to retrieve data from multiple databases on different servers ...

Hi Experts,

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,

Best regards,

The Mayor.

SOLUTION

praveen_ms

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wimmeyvaert

ASKER

Hi praveen ms,

Thanks for your reply.
I tested out your suggestion but when executing 'sp_addlinkedsrvlogin' I get following error :

Server: Msg 15015, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 40
The server 'DOLMENSERVER' does not exist. Use sp_helpserver to show available servers.

DOLMENSERVER is the name of the Server where my DB2 is situated on.
DB1 is situated on a server named NORMA.
When I trigger 'sp_helpserver' (on my NORMA server), I only get my current server I'm connected to (=NORMA) :
name network_name status id
----------------------------------------------------------------------------------
NORMA NORMA rpc,rpc out 0

When I do the same on the second server (DOLMENSERVER) I get :
name network_name status id collation_name
connect_timeout query_timeout
---------------------------------------------------------------------------------------------------------------DolmenServer DolmenServer rpc,rpc out,use remote collation 0 NULL
0 0

I have to say that my 2 servers are each in a separate domain. Maybe that's the reason why they can't 'see' each other.
Altough it is possible to go to the shared drives from 1 server to another.
Both servers are running Win2000 + SQL Server 2000.

Any idea why even the first step of your comment is not working ?

Best regards,

The Mayor

ASKER CERTIFIED SOLUTION

alanha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wimmeyvaert

ASKER

Hi alanha,

I'm not sure if I will have time today to try out your suggestion, but I will check it out and let you know the results.

Thanks for commenting...

Best regards,

The Mayor.

alanha

what happened

wimmeyvaert

ASKER

Sorry for my very late reply guys !
I totally forgot about this question.

I splitted the points because both praveen_ms and alanha gave me a part of the solution.
I hope both of you can live with that.

Anyway, thank you both for your help and sorry again for my late replies.

Best regards,

The Mayor.