[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2011-03-14
5
Medium Priority
?
1,001 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:apusjellis
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:bhoenig
ID: 35129213
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 35182905
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
 

Author Comment

by:apusjellis
ID: 35183488
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
 
LVL 3

Accepted Solution

by:
bhoenig earned 2000 total points
ID: 35184032
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
 

Author Comment

by:apusjellis
ID: 35184812
That wasnt exactly what I was looking for but, I altered the script you provided and found my answer. i appreciate the help.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question