Query to find the ssrs reports that has embedded datasource

Question

I have hundreds of reports that are deployed in MSSQL reporting server. In which few of the reports has embedded datasource and the rest has shared datasource. Is there is any query or easy method to differentiate the reports that have shared and embedded datasource?

Answer 1

You can use the following query:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition')

SELECT C.Name,
    CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)).exist('/Report/DataSources/DataSource/ConnectionProperties') AS EmbeddedSourcePresent,
    CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)).exist('/Report/DataSources/DataSource/DataSourceReference') AS SharedSourcePresent
FROM ReportServer.dbo.Catalog C
     WHERE C.Content is not null
      AND C.Type = 2

You might need to change your namespace according to your ssrs version. I hope this helps.