Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
 Posted - 2012-04-30 : 14:02:59
|
| I need a query to find all the stored procedures being used in the reports. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-30 : 14:21:24
|
| you can make use of Catalog table in report server for that. it will have reportXML field which is XML and contains a node for CommandText which gives you name of stored procedure or the query used in report------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-05-01 : 09:24:32
|
| We are not using the reporting server for reports but we use the regular transaction database server for reports generation..Please let me know how to identify the stored procedures which reports being used instead of manual verification process. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-05-01 : 09:39:11
|
| Rofl?Do you have a naming convention that allows you to easily identify a report SP? Such as rpt_sp_REPORT1?Then you could just browse the INFORMATION_SCHEMA.ROUTINES for SPECIFIC_NAME or ROUTINE_NAME.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-05-01 : 09:46:37
|
| No..There is no report naming convention for stored procedures it was generated with standard name for all procedures that's where the problem..Is there a way to figure out the sp's..with above constraints mentioned.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-01 : 10:05:10
|
quote:
Originally posted by sqlfresher2k7
We are not using the reporting server for reports but we use the regular transaction database server for reports generation..Please let me know how to identify the stored procedures which reports being used instead of manual verification process.
then there's no straight forward way. So are reports developed in application language rather than using reporting tool?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-05-01 : 11:51:46
|
| reports are developed using sqlserver report builder 2.0 version. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-01 : 11:55:45
|
| so is report model created and deployed to server?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2012-05-01 : 12:14:59
|
| Yes...Reports model are created and deployed to server. |
|
|
|
|
|
|
Query