SQL Server 2008 R2 – How to Get SSRS Datasources from ReportServer

sql-server-2008-r2ssrs

Housekeeping on an old SSRS, this particular server is 2008R2.

I have the query below which gets me the name, location, creation and modification overview for all the reports. I would also like to include the 'Connection String' at least, and some other details if possible from the data sources properties. But I can't find it in ReportServer. I have seen people use PowerShell to get it out of SSRS web service, I was hoping to find a T-SQL solution.

How can I display Data Source details with a SQL query?

--List all the reports on SSRS via "ReportServer" database 

USE [ReportServer]
GO
SELECT Name
--, [ItemID] --Primary key
, [Path]
, [Description]
--, [CreatedByID] --need link to get anything usable from here
, Created.UserName as CreatedByUser
, [CreationDate]
--, [ModifiedByID] --need link to get anything usable from here
, Modified.UserName as ModifiedByUser
, [ModifiedDate]


FROM [dbo].[Catalog]
left join (select [UserID]
            , [UserName]
            from [dbo].[Users]) as Created
        on Catalog.CreatedByID = Created.UserID
left join (select [UserID]
            , [UserName]
            from [dbo].[Users]) as Modified
        on Catalog.ModifiedByID = Modified.UserID

WHERE [Type] = 2 -- value per foundation Source http://sqlsrv4living.blogspot.com/2014/01/ssrs-get-list-of-all-reports-using.html

ORDER BY [Path], Name

Best Answer

Get SSRS Datasources from ReportServer

I would also like to include the 'Connection String' at least

How can I display Data Source details with a SQL query?

TSQL for getting the Connection String of SSRS Data sources

-- Transact-SQL script to get connection string of all SSRS Shared Datasources.

/*
Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?
With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources,
to document the usage or to search for a specific server/database.

Please remark: Querying the ReportServer database directly is not a supported way.
Works with SSRS 2005 and higher version ReportServer databases.
Requieres select rights on the "Catalog" table in ReportServer database.
*/

-- Connection strings of all SSRS Shared Datasources
;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY 
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
-- Optional filter:
-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
ORDER BY CON.[Path]
        ,CON.SharedDsName;

Here's a query to add the data source name as well using your TSQL:

USE [ReportServer]
GO

SELECT CATALOG.NAME
    ,CATALOG.[Path]
    ,DataSource.NAME datasource
    ,CATALOG.[Description]
    ,Created.UserName AS CreatedByUser
    ,CATALOG.[CreationDate]
    ,Modified.UserName AS ModifiedByUser
    ,CATALOG.[ModifiedDate]
FROM [dbo].[Catalog]
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Created ON CATALOG.CreatedByID = Created.UserID
LEFT JOIN (
    SELECT [UserID]
        ,[UserName]
    FROM [dbo].[Users]
    ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID
JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID
JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID
WHERE CATALOG.[Type] = 2
ORDER BY [Path]
    ,NAME
Related Question