CODEWITHSUNDEEP
sql-server-2008reporting-services
DavidStein
DavidStein

Reputation: 3189

SSRS Report to read from 2 databases

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:

DataSource

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"?

Upvotes: 0

Views: 838

Answers (1)

KM.
KM.

Reputation: 103707

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.

Upvotes: 2

Related Questions