[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1925
  • Last Modified:

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.
0
wimmeyvaert
Asked:
wimmeyvaert
  • 3
  • 2
2 Solutions
 
praveen_msCommented:
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
 
wimmeyvaertAuthor Commented:
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
 
alanhaCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
wimmeyvaertAuthor Commented:
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
 
alanhaCommented:
what happened
0
 
wimmeyvaertAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now