?
Solved

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

Posted on 2004-10-12
7
Medium Priority
?
1,917 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 300 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 300 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

764 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