Solved

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

Posted on 2004-10-12
7
1,912 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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