Combining data from 2 DB's on 2 different servers in CR XI

[kbrearey]

Hi everyone,

I am having trouble getting my head around an issue. I have data in 2 separate databases on 2 separate servers and I want to build a report that will incorporate data from both databases. (One table from each database to be more specific).

The tables in question have the same specification, in fact one is where we store our "Live" Data (data from the last 30 days), and the other is the "Archive" Data (Beyond 30 days).

Since the tables reside in 2 different databases, 2 different DBMS's and are connected to through 2 different DSN's, I haven't been able to figure a way to combine this data together.

Does anyone have any ideas on how I might be able to accomplish something like this?

I appreciate your time and help with this!

 

[briangriffin]

Depending on your database, the easiest way would be via a linked server (in SQL Server, I believe in Oracle they are called server links).

That enables you to query data on multiple servers from a single command:

select 
field1,
field2

from prodserver.proddatabase.owner.table

union all

select 
field1,
field2

from archiveserver.archivedatabase.owner.table

Your dba may not want to define a linked server, but from your description combining this data will be a common request so it's a good candidate.

If that's not a possibility please post with the backend database you are using.

 

[kbrearey]

Thanks for your response, briangriffin!

The "Live" database is running on DB2 and the "Archive" is a MySQL database. I generally don't access the archive data as the nature of our business is more 'now' oriented (which is why we archive after 30 days.) However, in this particular circumstance, there is a need to combine data from both databases. Long story short, I am not sure a linked server is going to be necessary as we generally won't need to run data from both databases.

In the past when I have needed to gather data from both tables, I have just created separate reports, and I may just do that this time as well, but if it is possible to link the data together simply, it might be useful to do so.