SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can I see which stored procedures are used in SSRS reports


Can I see which stored procedures are used in SSRS reports

Author
Message
DemonicKat78
DemonicKat78
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 155
HI,


I have been tasked with finding out which stored procedures are used in any SSRS reports that we have.

Is there an easy way to do this?

Thanks

Jayne
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)

Group: General Forum Members
Points: 262527 Visits: 9673
How many reports?


One simple way is to start a trace and run all the reports manually. You won't get much faster than this AFAIK.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166397 Visits: 15139
The report definitions are xml files so you could write something in powershell or .NET to query the rdl files.



Jack Corbett
Consultant Straight Path Solutions
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
DemonicKat78
DemonicKat78
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 155
Thanks, but we have 3000 reports! I was hoping there was a way that the reportserver stored information about what stored procedures each report used... ahh well...

Thanks anyway :-)
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)SSC Guru (262K reputation)

Group: General Forum Members
Points: 262527 Visits: 9673
The report definition is actually stored in a db somewhere, I just can't tell you where.

Now assuming that it is still in xml you can parse it.


Also there's an export function in the web interface, so maybe you can trace that to see how the data is extracted to download /rebuild the xml file.

From there it's just a parsing job...
LutzM
LutzM
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96391 Visits: 13561
You might want to have a look at
Jacob Sebastians blog (title: "How to find all stored procedures used by Report Server?") ;-)



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166397 Visits: 15139
LutzM (4/13/2011)
You might want to have a look at
Jacob Sebastians blog (title: "How to find all stored procedures used by Report Server?") ;-)


Awesome. I thought it had to be there, but I was looking for an xml or varchar(max) column and it's an IMAGE datatype.



Jack Corbett
Consultant Straight Path Solutions
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
LutzM
LutzM
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96391 Visits: 13561
I posted a slightly different query as my answer to a quiz question (SQLServer Quiz 2011).
This query is modified to query SSRS 2008 and to return the actual parameter values used for a specific report. Might be helpful, too.

I agree, the image data type is not the one someone would expect to find such valuable information. It's also strange that MS still uses the text/ntext data type in SSRS 2008 even though they advice against it in (almost?) every single offical documentation.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166397 Visits: 15139
LutzM (4/13/2011)

I agree, the image data type is not the one someone would expect to find such valuable information. It's also strange that MS still uses the text/ntext data type in SSRS 2008 even though they advice against it in (almost?) every single offical documentation.


I believe all those data types (text/ntext and image) are deprecated as well.



Jack Corbett
Consultant Straight Path Solutions
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
WayneS
WayneS
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85633 Visits: 11063
Jack Corbett (4/13/2011)
LutzM (4/13/2011)

I agree, the image data type is not the one someone would expect to find such valuable information. It's also strange that MS still uses the text/ntext data type in SSRS 2008 even though they advice against it in (almost?) every single offical documentation.


I believe all those data types (text/ntext and image) are deprecated as well.


Yep, as in they're not in Denali.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search