Can I see which stored procedures are used in SSRS reports

  • DemonicKat78

    Old Hand

    Points: 381

    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: 184380

    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

  • DemonicKat78

    Old Hand

    Points: 381

    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: 184380

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

  • 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: 184380

    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

  • WayneS

    SSC Guru

    Points: 95342

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

    Hall of Fame

    Points: 3162

    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

    SSC-Addicted

    Points: 468

    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: 90287

    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