How to create a dynamic Reporting Services Shared Data Source Using Linked Servers

When using SQL Server Reporting Services (SSRS), wouldn’t it be nice to be able to specify the data source at run time? For example, you could deploy a report on a single Report Server, but have the ability to retrieve data from any database on any server. Without a dynamic data source, you would have to deploy multiple instances of the report, one for each database server you want to use. Additionally, if you are using stored procedures, you must also duplicate them on each database server.

SSRS already allows for a dynamic data source through an expression-based connection string. The main limitation is that an expression-based connection string can only be used with an embedded data source; i.e., one that is contained in the report definition. You cannot use it with a shared data source. However, a shared data source offers advantages over an embedded data source and is often a necessity. Imagine having to modify the data source for hundreds of reports. With an embedded data source you would have to edit every report, while with a shared data source you could do so in just one place.

This post discusses a solution that lets you specify the database server at run time, while still being able to use a shared data source. The solution uses linked servers. A linked server is essentially an alias for a remote server on a local server. Once defined, a linked server allows you to execute queries on the remote server locally. This solution consists of the following steps:

Choose a database server for the shared data source
Define a linked server for each remote database that a report needs to access
In order to query from the linked servers, create a stored procedure that takes server name as a parameter; executing the stored procedure should return the results from the desired server
Create a shared data source
In the report, select the shared data source when defining a query
In the report, define a report parameter for server name to be passed to the above stored procedure

Let’s take a look at an illustrated walkthrough.

Step 1

Assume that we need to access two database servers, selene and ts-yt02. Choose ts-yt02 as the database server for the shared data source.

Step 2

On ts-yt02, create a linked server for selene with the following script:

exec sp_addlinkedserver @server='selene', @srvproduct='', @provider='SQLNCLI', @datasrc='selene'

Step 3

Create a stored procedure named SelectFromServer which takes the server name as a parameter. The stored procedure constructs a dynamic SQL query using the server name. Be aware of precautions when using dynamic SQL. Here the query simply returns a list of employee information. Use the fully qualified name for the table; i.e. Server.Database.Schema.Table. For simplicity, select from the same table on all servers, although you can certainly have a different query for each server. For illustration, create the stored procedure under the master database, but you may wish to put it elsewhere.

create procedure [dbo].[SelectFromServer]

@ServerName sysname

as

begin

set nocount on;

declare @sql nvarchar(max)

set @sql = N'select id, name, email from ' + quotename(@ServerName) + '.SoftArtisans.dbo.Employee'

exec sp_executesql @sql

end

go

Step 4

In the SQL Server Business Intelligence Studio, create a shared data source which targets ts-yt02 and the master database (assuming that you created the SelectFromServer stored procedure under master, otherwise use whichever database you chose).

Step 5

Create a data set for the report. For data source, select the shared data source. For query type, select Stored Procedure and enter SelectFromServer.

Step 6

Because the SelectFromServer stored procedure requires a parameter, create a report parameter named ServerName which will be passed to the stored procedure.

Now map the report parameter to the stored procedure parameter. Open the data set properties and select the Parameters tab. For Parameter Name, enter @ServerName (prepending the parameter name with @). For Parameter Value, select the report parameter that you just created, @ServerName.

Step 7 (optional)

For added convenience, you may wish to allow the user to select from a list of available servers rather than having to type in the server name manually. To do so, you can create an additional query which returns the list of servers, then set the available values of the report parameter to the results of the query.

Create a new data set, ServerList, which uses a text query. You can use any method to retrieve the server names. For simplicity, get the list of servers by querying a system table:

select SrvName from sys.sysservers

Specify that SSRS should use the results of the ServerList data set to generate possible values for the report parameter. Open the Parameter Properties dialog for the report parameter, select the Available Values tab, and make the below selections. For the Value and Label fields, note that the selected field is the same as that in the query.

Viewing the report

When viewing the report, you will be presented with a drop-down list of available servers. Select a server then select View Report to see the results from the desired server.

Using linked servers is just one option for retrieving data from a remote database. There are other alternatives which may be more suitable, such as synonyms or directly invoking a stored procedure on the remote database.

Getting Started:

Learn more about SSRS integration with OfficeWriter or start your free trial of OfficeWriter today.

OR

Scripting Deployment of Reporting Service Reports with Stored Credentials

Twitter Roundup: Talking About SSRS

OfficeWriter and the Microsoft Application Platform

SoftArtisans