SQL query on multiple databases

Question

I have two databases on one sql server, and I have to link two tables from one DB server to two tables in another DB server to get the info that I need. The problem is that when I try to link the two tables from the second DB server the query returns duplicates of 1000 or more. How can I run a single query on two databases? All tables have the repair_ord column in common. Can someone please help me? Thank you.

server 1 = CXADMIN SERVER 2 = SAADMIN

Here is what my query looks like so far:

SELECT RF.REPAIR_ORD, 
       RH.RECV_UNIT, 
       RH.RECV_SERIAL_NBR, 
       RP.FAULT_CODE, 
       RP.REPAIR_ACTION_CODE, 
       CG.TASK_CODE 
  FROM CXADMIN.RO_FAILURE_DTL RF,  
       CXADMIN.RO_HIST RH, 
       saadmin.sa_repair_part@elgsad rp, 
       saadmin.sa_code_group_task_dtl@elgsad cg 
 WHERE RF.REPAIR_ORD = RH.REPAIR_ORD 
   AND RP.REPAIR_ORD = CG.REPAIR_ORD 
   AND RF.FAILURE_CODE ='DISK'
   AND RH.CURR_FACILITY_ID ='23' 
   AND RF.CREATED_DATE >'1-JUN-2010' 
   AND RF.CREATED_DATE <  '1-JUL-2010' 
   AND (   CG.TASK_CODE ='PHMD' 
        OR CG.TASK_CODE ='PHSN' 
        OR CG.TASK_CODE ='CHMD' 
        OR CG.TASK_CODE ='CHSN')

Answer 1

I think the duplicates issue is not one of joining the two databases but rather in your join in the first place. I think you might need an INNER or OUTER join to handle the linking. As for getting data from two different databases, the syntax is fairly simple. You just add the server name dot the database name dot the owner name dot the table name.

For example:

SELECT firstdb.*, seconddb.*
FROM Server1.Database1.dbo.myTable AS firstdb
INNER JOIN Server2.Database2.dbo.myTable AS seconddb
   ON firstdb.id = seconddb.id

In your example, it sounds like you are getting the link to work but you have a join issue on the repair_ord field. While I don't know your schema, I would guess that this link should be an INNER JOIN. If you just add both tables in the FROM statement and you don't do your WHERE statement properly, you will get into trouble like you are describing.

I would suggest that you simplify this setup and put it in a test environment (on one DB). Try the four-table join until you get it right. Then add in the complexities of multi-database calls.

Answer 2

If you rewrote your FROM clause to use ANSI 92 you would get this

 FROM CXADMIN.RO_FAILURE_DTL RF
     INNER JOIN CXADMIN.RO_HIST RH
      ON  RF.REPAIR_ORD = RH.REPAIR_ORD
           ,
      saadmin.sa_repair_part@elgsad rp
      INNER JOIN saadmin.sa_code_group_task_dtl@elgsad cg
       ON RP.REPAIR_ORD = CG.REPAIR_ORD 

It then becomes easy to see that you've created a cartesian product between RF join RH and RP JOIN CG

You need to JOIN RF to RP or CG, or RH to RP or CG

for example

FROM CXADMIN.RO_FAILURE_DTL RF
     INNER JOIN CXADMIN.RO_HIST RH
      ON  RF.REPAIR_ORD = RH.REPAIR_ORD
      INNER JOIN saadmin.sa_repair_part@elgsad rp
      ON  RF.REPAIR_ORD = RP.REPAIR_ORD
      INNER JOIN saadmin.sa_code_group_task_dtl@elgsad cg
       ON RP.REPAIR_ORD = CG.REPAIR_ORD 

Or if you insist on using ANSI-86 style joins you can just add AND RF.REPAIR_ORD = RP.REPAIR_ORD to your Where clause