Getting data from multiple databases (How to link databases)

Document created by Gordonn_Lamothe Employee on Sep 9, 2015
Version 1Show Document
  • View in full screen mode


How to link 2 DB Servers, to be able to run Reports using Both

[In my Example… I will link the DB2(LAMGO01-U110747) to the DB1(LAMGO01-U100928)]


This can be done for multiple reasons:

  • Compare data from Source and Target, for troubleshooting SQL Bridge issues.
  • Compare data from DM and EM, for troubleshooting Replication issues
  • Creating reports from multiple database, without having to replicate the data
  • Creating reports from multiple database, for data that is not replicated and /or synch by default.



Here are the steps for linking 2  Databases:


From the Database 1(DB1)  –Right-click on Linked Servers and Select New Linked Server…….

(you will need to be a sysadmin on both machines)



Under the General Tab on Right -      Put the Name of the Database 2 (DB2) MDB Server and Select SQL Server


Now click the security Tab on Right Click the Security Option that makes sense for your environment:

Click OK


Now both MDB’s are linked and you can run Queries based on both MDB’s.



Running Reports using the 2 Databases:


Since in my Example, I am on the DB1 machine when I want a DB1 table I can just use the table name.


When I want info from the DB2 table in need to use [Server Name].databasename.owner.tablename


Example of 2 tables with owner ca_itrm and 2 with owner dbo


How to run selects for each Database


A normal select command:       Select * from ca_agent where agent_type =1



DB1:                                           select  *  from ca_agent   where agent_type =1

DB2:                                           select  *  from [DB2].mdb.dbo.ca_agent where agent_type =1



This also allows you to compare the results from the DB1 and DB2

For Example :   Give me all the records from a certain table that are on DB1, but not on DB2



DB1 not on DB2:

  use mdb

select *  from ca_discovered_hardware

where dis_hw_uuid not in (select dis_hw_uuid from [DB2].mdb.dbo.ca_discovered_hardware)

and dis_hw_uuid in (select dis_hw_uuid from ca_discovered_hardware)




DB2 not on DB1:

use mdb

select *  from [DB2].mdb.dbo.ca_discovered_hardware

where dis_hw_uuid in (select dis_hw_uuid from [DB2].mdb.dbo.ca_discovered_hardware)

and dis_hw_uuid not in (select dis_hw_uuid from ca_discovered_hardware)