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
917 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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