SSRS Report to read from 2 databases.

Question

I want to compare records between 2 databases on my SQL 2008 Server. The query below works in SSMS.

SELECT 'Manual',
       manual.*,
       'Auto',
       Auto.*
  FROM    (SELECT * FROM TESTINGSOMODS..TESTINGSOManMODS) Manual
       FULL OUTER JOIN
          (SELECT * FROM CHELTONCUSTOMIZATIONS..SOMODS) Auto
       ON manual.[Order No] = auto.fsono

I created a solution and when I tried to set up a shared data source, I am confronted with the following:

So, my first question is, if I want to pull from 2 different databases what do I enter for "Select or enter a database name"?

Answer 1

Set it up to run from TESTINGSOMODS. Create a stored procedure on TESTINGSOMODS that contains the query, use this procedure for the report. The report will not know that you are accessing CHELTONCUSTOMIZATIONS, it will only look at a result set from the procedure on TESTINGSOMODS.