April 13, 2011 at 7:31 am
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
April 13, 2011 at 7:41 am
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.
April 13, 2011 at 1:55 pm
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
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 Question
April 13, 2011 at 2:36 pm
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 🙂
April 13, 2011 at 2:45 pm
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...
April 13, 2011 at 2:47 pm
You might want to have a look at
Jacob Sebastians blog (title: "How to find all stored procedures used by Report Server?") 😉
April 13, 2011 at 2:52 pm
LutzM (4/13/2011)
You might want to have a look atJacob 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
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 Question
April 13, 2011 at 3:05 pm
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.
April 13, 2011 at 3:11 pm
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
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 Question
April 13, 2011 at 3:19 pm
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
April 13, 2011 at 3:47 pm
WayneS (4/13/2011)
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.
Do you have a Denali SSRS installation available? What are the data types MS uses now?
September 30, 2011 at 4:41 pm
I stumbled across this thread today and wondered if I might enlist more help on this topic. I'm running the supplied queries but with the namespace for 2008 R2 (.../reporting/2010/01/...) but I'm coming up with no results. I know for a fact that I'm using Stored Procedures for at least some of my SSRS reports so something's a bit hinky here.
Since I know next to nothing about querying xml, I'm at a loss to troubleshoot the query at this point.
Help?
DECLARE @StartTime DATETIME,
@EndTime DATETIME
SELECT @StartTime = '20110929', @EndTime = '20110929'
;WITH xmlnamespaces (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS Test
)
SELECT
t1.Name AS [Report Name],
x.value('CommandText[1]','VARCHAR(max)') AS [Stored Procedure],
CONVERT(CHAR(8),DATEADD(ss, (exlog.TimeDataRetrieval
+ exlog.TimeProcessing
+ exlog.TimeRendering)/1000,0),108 )AS Duration,
'@'+REPLACE(CAST(exlog.[parameters] AS NVARCHAR(MAX)),'&',', @') AS [Parameters]
FROM
(
SELECT
NAME,
ItemID,
CAST(CAST(CONTENT AS VARBINARY(MAX)) AS XML) AS ReportsXML
FROM reportserver.dbo.catalog
WHERE [TYPE] = 2
) t1
INNER JOIN ExecutionLog exlog ON exlog.ReportID = t1.ItemID
CROSS APPLY t1.ReportsXML.nodes('/Report/DataSets/DataSet/Query')r(x)
WHERE
NOT PATINDEX('%.%', name) > 0
AND x.value('CommandType[1]', 'VARCHAR(max)') ='StoredProcedure'
AND exlog.TimeStart > @StartTime
AND exlog.TimeEnd < @EndTime
October 1, 2011 at 2:02 am
I guess the problem is not really caused by XQuery but rather by the values for the date variables: Both show the same date but the query will find no rows since you're looking for all procedures used after 2011-09-29 00:00 and before 2011-09-29 00:00.
(exlog.TimeStart > @StartTime AND exlog.TimeEnd < @EndTime)
Do you get any results when using @StartTime = '20110929', @EndTime = '20110930' ?
March 20, 2017 at 10:22 am
BeyondRelational.com is down. Where is "X" defined in this query?
DECLARE @StartTime DATETIME,
@EndTime DATETIME
SELECT @StartTime = '20110929', @EndTime = '20110929'
;WITH xmlnamespaces (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS Test
)
SELECT t1.Name AS [Report Name],
x.value('CommandText[1]','VARCHAR(max)') AS [Stored Procedure],
CONVERT(CHAR(8),DATEADD(ss, (exlog.TimeDataRetrieval
+ exlog.TimeProcessing
+ exlog.TimeRendering)/1000,0),108 )AS Duration,
'@'+REPLACE(CAST(exlog.[parameters] AS NVARCHAR(MAX)),'&',', @') AS [Parameters]
FROM
(
SELECT
NAME,
ItemID,
CAST(CAST(CONTENT AS VARBINARY(MAX)) AS XML) AS ReportsXML
FROM reportserver.dbo.catalog
WHERE [TYPE] = 2
) t1
INNER JOIN ExecutionLog exlog ON exlog.ReportID = t1.ItemID
CROSS APPLY t1.ReportsXML.nodes('/Report/DataSets/DataSet/Query')r(x)
WHERE
NOT PATINDEX('%.%', name) > 0
AND x.value('CommandType[1]', 'VARCHAR(max)') ='StoredProcedure'
AND exlog.TimeStart > @StartTime
AND exlog.TimeEnd < @EndTime
March 20, 2017 at 11:18 am
This is a six year old thread. Probably best to start a new thread and post questions there.
But x is in this line: CROSS APPLY t1.ReportsXML.nodes('/Report/DataSets/DataSet/Query')r(x)
Sue
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply