2

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?

Remi
  • 214
  • 5
  • 17
  • http://stackoverflow.com/questions/9638431/listing-all-data-sources-and-their-dependencies-reports-items-etc-in-sql-ser – Snowlockk Mar 03 '17 at 15:11

1 Answers1

2

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.

ilker
  • 166
  • 2
  • 3
  • I had to use this to get past an invalid XML character: CAST(REPLACE(REPLACE(REPLACE(CAST(CONVERT(VARBINARY(MAX),C.Content) as varchar(max)),'&','&'),'''','''),'', '') AS XML).exist('/Report/DataSources/DataSource/ConnectionProperties') AS EmbeddedSourcePresent – influent Oct 23 '18 at 23:18