Quick how to on Dynamic Connections. If you have a report that you want to run against multiple servers, you properly don’t want to create the same report 10-100 times. With SSRS its incredibly easy to set your data source as a dynamic string.

First Create a Parameter called “Server” (enter a default value if you like):

DynamicDataSourceParam1

DynamicDataSourceParam2

Next go to your Data Source and select the Expression button

Enter the following Code:

 

="data source=" & Parameters!Server.Value & ";Initial Catalog=master"

 

 

DynamicDataSource1

DynamicDataSource2

In my example im using my code from my blog:

https://sqlnotesfromtheunderground.wordpress.com/2013/11/06/knowing-your-checkdb-duration-times-using-ola-hallengren-maintenance-scripts/

Set up your report like you want, with a query that can run against any server (if you want an quick easy query just to test, maybe use something like

SELECT
name ,
compatibility_level ,
collation_name ,
snapshot_isolation_state_desc ,
recovery_model_desc ,
page_verify_option_desc
FROM sys.databases

When you run the report, you should end up with something like this (Note the Parameter at the top where you can enter a SQL instance name to connect to and run your dataset agains.):

DynamicDataSourceResults2

Type in a new server name and either press Return or click the view Report button and you get details on the new server

DynamicDataSourceResults1

Advertisements