Solved

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

Posted on 2004-10-12
7
1,913 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 setup several different housekeeping processes for a SQL Server.

820 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