Is there a script that will identify which data source/connection string and stored proc an SSRS Report is using?

Is there a script that I can run that will identify which data source/ connection string that an SSRS Report is using? Is there a script that will let me know which stored procs the SSRS Reports are using?

I am trying to find a better way to be able to inventory SSRS reports, my current way is having to access each report and drill down to the data source connection property page of each report.

Any help is greatly appreciated.
Thanks
apusjellisAsked:
Who is Participating?
 
bhoenigConnect With a Mentor Commented:
Maybe using a left join will help.  This show all the reports that do not have a link to a DataSource (or an imbeded Connection String).


use ReportServer

select c.Name as 'ReportName', c.Path, d.Name 'Connection Name', d.ConnectionString
, c.* -- show all the Catalog columns
, '|||||||||||||||||' -- used for visual seperation of columns
, d.* -- show all the DataSource columns
from dbo.Catalog c
left join dbo.DataSource d on c.ItemID = d.ItemID
where d.ItemID is null  -- This eliminates all the reports with a datasource.
order by ReportName

Open in new window

0
 
bhoenigCommented:
I would try connecting to the ReportServer database and take a look around.  Start with this query:

use ReportServer

select c.Name as 'ReportName', c.Path, d.Name 'Connection Name'
from dbo.Catalog c
join dbo.DataSource d on c.ItemID = d.ItemID

Open in new window

0
 
TempDBACommented:
here are some of the main talbes where metadata is stored in ReportServer database. Try them:-

History
ZZ_Catalog
Users
ExecutionLogStorage
DataSource
Roles
Subscriptions
SnapshotData
Schedule
ReportSchedule
0
 
apusjellisAuthor Commented:
appreciate the response from both of you. That is not exactly what I am looking for. I am trying to pull from a SQL Query the Data Connection or Data String the report is using. My end goal is trying to identify reports that do not have a data connection or data string set on them.
0
 
apusjellisAuthor Commented:
That wasnt exactly what I was looking for but, I altered the script you provided and found my answer. i appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.