All we need is an easy explanation of the problem, so here it is.
We have a bunch of SSRS (2008) reports deployed to our web portal. We have edited some of the reports to use a shared data source different than the one with which it was originally deployed.
I am looking for a way to query the ReportServer database to show me which reports use which of these shared data sources. I found that you can use the XML data stored in Catalog.Content to show what data source is being used, but this appears to the data source with which the report was originally deployed.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Okay, I got it. Found this link which helped: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/581c7068-0abe-49f9-a1a5-93e94f472641/
I distilled that information to this query:
select Catalog.name, cat1.Name datasource from Catalog join DataSource on Catalog.ItemID = DataSource.ItemID join Catalog cat1 on DataSource.Link = cat1.ItemID where Catalog.Type = 2
Hope this helps someone else in the future!
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂