Search code examples
sql-serverreporting-servicessql-server-2008-r2ssrs-2008

SSRS reports with multiple database

I am creating an SSRS report which will fetch data from two different databases. The problem is that, the database names will be different in development and live servers.

My question is - 1) What should I do at Stored Proc level so that I don't need to change anything there if the database name gets changed? 2) What should I do at report level (or on dataset) so that I don't need to change anything for the different database names?

I am having SSRS deployed on SQL Server 2008 R2.

Solution

  • You don't need to do anything with your Stored Procedure. Just make sure it is on the correct Databse. You can handle various Database Names with a Parameter on your Report:

    1. Create a DBName Parameter in your Report
    2. Set your Data Source Connection String to: ="Data Source=YourServerName;Initial Catalog=" & Parameters!DBName.Value