13

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.

Improve this question
3
  • I can't do a full answer, but you should be able to find it in ReportServerDB: there is an Item (?) table that contains all entities. And some link between a report and datasource item. Good hunting!
    – gbn
    Oct 17, 2011 at 16:44
  • Thanks, gbn, but that's exactly what I can't find: the link between the catalog and datasource tables... there is no item table...
    – JHFB
    Oct 17, 2011 at 17:28
  • And, after close examination, I don't even think the shared data sources are in the datasource table. The plot thickens.
    – JHFB
    Oct 17, 2011 at 17:29

1 Answer 1

Reset to default
18

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!

Improve this answer
2
  • 2
    PERFECT. thanks for the followup "distilled" query. Exactly what I needed.
    – user14537
    Oct 17, 2012 at 13:31
  • You the real MVP
    – BilliD
    Sep 30, 2016 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.