Solved

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

Posted on 2004-10-12
7
1,914 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:wimmeyvaert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 2

Assisted Solution

by:praveen_ms
praveen_ms earned 75 total points
ID: 12285297
Create a linked server from enterprise manager
and then in code

You need to add connect to a linked server

to do this

use execute sp_addlinkedsrvlogin [remoteserver],false,null,sa,'password'

After doing this

TO keep the SQL simple you can select the record from remote server to local temporary table using OpenQuery

select * INTO #SEAT_TESTRESULT
OPENQuery([remoteserver], 'select  * from SEAT_TESTRESULT)

and then you can run you query

SELECT s.*, sr.*
FROM SEAT s, #SEAT_TESTRESULT sr
WHERE s.Seat_ID = sr.Seat_ID
     AND s.Seat_ID = 1234567

Finally,
disconnect to do this

execute sp_droplnkedsrvlogin[remoteserver],sa

Regards
Praveen
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12285550
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
0
 

Accepted Solution

by:
alanha earned 75 total points
ID: 12288464
Hi,

Did you create the link to the remote server (you can do thin under Enterprise Manager, Security)...? or you can run a script in Query Analizer that will create the link for you.

like this...

1) Open Query Analyzer

2) Use the 'sp_addlinkedserver' system stored proc to add a SQL Server linked server:

   sp_addlinkedserver 'SERVER2', N'SQL Server'

  (for further info, highlight 'sp_addlinkedserver' in query analyzer and press <shift>-<F1>)

3) Try a sample SELECT of SERVER2 from SERVER1, using the 4-part name:

    SELECT TOP 1 *
    FROM SERVER2.MyDatabase.dbo.MyTable

Alan
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12295134
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.
0
 

Expert Comment

by:alanha
ID: 12507886
what happened
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 12524556
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question