[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

How to find out which reports use a specific stored procedure?

Is there a way to find out which reports refer to a specific stored procedure, other than checking all reports individually?

This is to avoid the situation where a stored procedure is updated correctly for one report, but not realizing that the update breaks other reports that refer to the same stored procedure
0
mc_eze
Asked:
mc_eze
  • 2
  • 2
1 Solution
 
rajeshprasathCommented:
you need to run a profiler and find out all the reports that using the same stored procedure.
0
 
mc_ezeAuthor Commented:
Thanks for the tip
0
 
rajeshprasathCommented:
open all the report rdl files in xml editor and then search the storedprocedure name in seach dialog by checking the all open documents option. you can use editpro or editplus for quickly find the reports.
0
 
itcoupleCommented:
Hi

If you want to automate it I suggest to look for some code (eg VBA/VBscript) to loop throught XML files in your solution folder and find the tags which store SP name.

Regards
Emil
0
 
mc_ezeAuthor Commented:
This query turned out to be what was needed. This was found here: http://beyondrelational.com/blogs/jacob/archive/2008/01/16/how-to-find-all-stored-procedures-used-by-report-server.aspx

;WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd

)
SELECT *
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText

FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog

) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now