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
901 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adding SQL Server Browser in after install is complete 8 57
Connect to SQL 2008 r2 server over the Internet 4 80
calculate running total 8 51
grouping by date only 6 22
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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