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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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…
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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