SQL query on multiple databases

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 firstdbINNER 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.

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