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;