topic Re: Multiple Databases in a dataset in Report Server

Multiple Databases in a dataset

Anonymous — Thu, 18 Jun 2020 11:56:07 GMT

Hi,

We have a requirement of fetching data from two databases for most of our SSRS related paginated reports. Creating a data source in the Report designer (Visual Studio) allows connection to be made to a single data source only (1 database). Is there a best practice approach/solution to this requirement other than the one we got? (Detail below)

We did some research and figured out a way to deal with it, but it only works if the 2 databases reside on the same server. When writing the report queries, we can append the database name with the schema to reference the table we require. For example:
Select D.DeptDescription, S.Amount
from TESTDB1.dbo.Dept D join TESTDB2.dbo.Sales S
on D.DeptCode = S.DeptCode

Using the above approach we face a different problem; related to migration. We have different DB names on Development and Production servers, meaning we can’t use the same query on Prod to get the same data
(Database in Prod might be LIVEDB1 and LIVEDB2).
So every time a report is deployed on Production the query will need to be modified. Is there a work around or a better/clean solution for this?

-Thanks

Yaseen

Re: Multiple Databases in a dataset

d_gosbell — Fri, 19 Jun 2020 04:10:24 GMT

You should be solve this more elegantly in SQL Server by using synonyms (see https://docs.microsoft.com/en-us/sql/relational-databases/synonyms/create-synonyms?view=sql-server-ver15)

These effectively work as sort short cut links. I think if you setup linked servers you can event create cross-server synonyms, but you'd need to be careful with these as they come with the same perform potential performance issues as referencing linked servers directly. However using them to link across databases in the same instance is less prone to issues (and has the same performance implications as using 3 part names). We typically put all our synonyms in their own schemas with a prefix of syn_ so that we can easily see that we are not referencing a local object

So you could do something like the following in TESTDB1 (notice that the reference to TESTDB2 is set once when you create the synonym)

CREATE SYNONYM syn_db2.Sales FOR TESTDB2.dbo.Sales;

Then your report just has to connect to TESTDB1 and could run the following query:

Select D.DeptDescription, S.Amount
from Dept D join syn_db2.Sales S
on D.DeptCode = S.DeptCode