CODEWITHSUNDEEP
sql-serverreporting-servicessql-server-2008-r2ssrs-2008
Nirman
Nirman

Reputation: 6793

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.

Upvotes: 2

Views: 14115

Answers (4)

pravi.net
pravi.net

Reputation: 21

Multiple Datasets in same report, and its possible.

Step 1 Create as many as Datasources you want with different server/database/table name for ex: DataSource1 = server1/Database1/table1 DataSource2 = server2/Database2/table2

Step 2 Create as many as Datasets with

Step 3 Within the same Report window -> right click and choose Insert - Table

Place the table where ever you want Go to properties of table - Set DataSetName to Datasource what you need to. DatasetName = Datasource2

Make sure Dataset works and has fields shown on left side under Report Data

Save and Run the report.

Have fun...!

Upvotes: 2

RobbZ
RobbZ

Reputation: 1410

Typically when I am creating a report I create shared data sources that point at different databases. You can think of them as basically a connection string. The nice thing about SSRS is that you can have one data source that you upload to the server and can use in different reports. Then if you need to change the location of the database you just need to do it once for the shared data source on the SSRS server and all of the reports will point to the new location. In addition this can separate out the server from the report. So in your instance you would upload the same report to multiple servers and connect the report to different servers depending on the data source you have on that server.

  1. First create a Data Source in your project
  2. Add that Data Source to your report (add Data Source, Use shared data source reference)
  3. When you deploy the report to a server you need to deploy the Shared Data Source and the report
  4. After you deploy the report bring up the context menu for that report, and click manage data sources. Now set this one to your shared data source. Each server could be using a different shared Data source. (I only have access to Sharepoint Integrated servers right now so the options might be a bit different on native mode SSRS).

Upvotes: 1

Ron Smith
Ron Smith

Reputation: 3266

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

Upvotes: 5

Mathese F
Mathese F

Reputation: 559

You are forced to do dynamic sql.

Do you have a way to retrieve the name of the database and to know how it changes?

You could imagine to pass the name as a parameter. And to prevent sql injection to easyli, you can check the variable is existing in sys.databases and in another dataset, if the first is true, you can pass the value to your stored proc and execute it.

For your stored procedure, create a variable nvarchar(max), add all your code in that variable, double the quote and extract the database name to let it like this.

declare @sql = 'select * from [' + @mydb + '].dbo.mytable'

Upvotes: 1

Related Questions