Get Report Data Sources

Applicability:

SQL Server 2000: Not Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 25 Jul 2012

Description

Retrieves a list of the data sources used by one or many reports.

I was a little thrown off balance with this one. After the relative straightforward retrieval of other report metadata, I came to a bit of a brick wall when trying to find schema information on the DataSources table. It appears that Microsoft deliberately haven't published this information to discourage the writing of queries against the ReportServer database.

Data Sources are initially listed as type #5 entries in the Catalog table, alongside Reports and other Report Server objects. This listing contains only brief metadata (name, folder) but no connection-specific information.

The Data Sources table appears to be a way of modeling the 1..n relationship between Reports and Data Sources, and also stored connection information. However, there are 2 columns that can join to the Catalog table (ItemID and LinkID), with the latter being optional. I was unable to reliably unpick exactly what these relationships model, as there appeared to be attribute dependencies on some of them ie if x = NULL then relationship is y, otherwise it is z

I'm sure these variations capture shared data sources and those with and without stored credentials, but nowhere could I find this out for certain. The task was made more difficult as currently I have access to a ReportServer database but not the web site or report code to be able to check on assumptions.

I have therefore left the query at the very basic level and will revisit this code when I eventually discover the truth.

NOTE: It is assumed that the Report Server Database has its default name of 'ReportServer'

Code

Stored Procedure:

DROP PROC usp_GetReportDataSources

GO

CREATE PROC usp_GetReportDataSources @ReportName NVARCHAR(850) = NULL

AS

/****************************************************

Purpose: To retreive the data sources for one or many reports.

Uses the Catalog and DataSources tables in the report server

database (assumed to be called by its default

name of 'ReportServer')

The DataSources table is not well documented (Microsoft do

not publish the schema to discourage code like this),

so this is a best-guess amalgamation from multiple code sources

Author: Unknown

History: Unknown

*****************************************************/

SELECT

C2.Name AS Data_Source_Name,

C1.Name AS Dependent_Item_Name,

C1.Path AS Dependent_Item_Path

FROM ReportServer.dbo.DataSource AS DS

INNER JOIN ReportServer.dbo.Catalog AS C1

ON DS.ItemID = C1.ItemID

AND DS.Link IN

(

SELECT ItemID

FROM ReportServer.dbo.Catalog

WHERE TYPE = 5

) --Type 5 identifies data sources

FULL OUTER JOIN ReportServer.dbo.Catalog C2

ON DS.Link = C2.ItemID

WHERE C2.TYPE = 5

AND C1.Name LIKE '%' + ISNULL(@ReportName, '') + '%'

ORDER BY

C2.Name ASC,

C1.Name ASC;