Can I see which stored procedures are used in SSRS reports

  • DemonicKat78

    Old Hand

    Points: 321

    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

    SSC Guru

    Points: 294069

    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

    SSC Guru

    Points: 184296

    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 FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • DemonicKat78

    Old Hand

    Points: 321

    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

    SSC Guru

    Points: 294069

    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

    SSC Guru

    Points: 107049

    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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • LutzM

    SSC Guru

    Points: 107049

    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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • WayneS

    SSC Guru

    Points: 95341

    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[/url]


    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[/url], How to ask a question, Performance Problems[/url], Common date/time routines,
    CROSS-TABS and PIVOT tables Part 1[/url] & [url url=http://www

  • LutzM

    SSC Guru

    Points: 107049

    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?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • tacy.highland

    SSCrazy

    Points: 2986

    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

  • LutzM

    SSC Guru

    Points: 107049

    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' ?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Michael Wasson

    Old Hand

    Points: 388

    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

  • Sue_H

    SSC Guru

    Points: 89905

    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 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply