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
|