Author:    Unknown

Date:        25 Jul 2012

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' 

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;