CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresreporting-services
Elizabeth
Elizabeth

Reputation: 765

Using T-SQL find the Stored Procedure associated to an SSRS Report

I had some help writing this query -- I'm at a bit of a loss because i'm trying to find the the query type or procedure used and i'm not sure what else to add to the query or how to change it. 

SELECT      
    Ds.Name as Data_Source_Name,
    C2.Name AS Data_Source_Reference_Name,
    C.Name AS Dependent_Item_Name,
    C.Path AS Dependent_Item_Path,
    ds.*
FROM
    ReportServer.dbo.DataSource AS DS
INNER JOIN  
    ReportServer.dbo.Catalog AS C ON DS.ItemID = C.ItemID 
                                  AND DS.Link IN (SELECT ItemID 
                                                  FROM ReportServer.dbo.Catalog
                                                  WHERE Type = 5) --Type 5 identifies data sources
FULL OUTER JOIN 
    ReportServer.dbo.Catalog C2 ON DS.Link = C2.ItemID
WHERE
    C2.Type = 5
    AND c.name LIKE '%mkt%'
ORDER BY    
    C.Path, C2.Name ASC, C.Name ASC;

Please advise.

enter image description here

Upvotes: 2

Views: 10246

Answers (3)

Tim Mylott
Tim Mylott

Reputation: 2703

Based on my comment, give this a try, should get you moving in the right direction on how you can parse the xml and zero in on the specific command.

You might have to update the name spaces in the script below and also add your report name.

But try something like this:

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition') --You may have to change this based on you SSRS version
SELECT
    [Path],
    Name,
    report_xml.value( '(/Report/DataSources/DataSource/@Name)[1]', 'VARCHAR(50)' ) AS DataSource,
    report_xml.value( '(/Report/DataSets/DataSet/Query/CommandText/text())[1]', 'VARCHAR(MAX)' ) AS CommandText, 
    report_xml.value( '(/Report/DataSets/DataSet/Query/CommandType/text())[1]', 'VARCHAR(100)' ) AS CommandType, 
    report_xml
FROM
    (
    SELECT 
        [Path], 
        Name, 
        [Type],
        CAST( CAST( content AS VARBINARY(MAX) ) AS XML ) report_xml 
    FROM dbo.[Catalog]
    WHERE Content IS NOT NULL
    AND [Type] = 2
    ) x
WHERE 
--use below in where clause if searching for the CommandText.  Depending on how the report was developed I would just use the proc name and no brackets or schema.
--Example:  if you report was developed as having [dbo].[procName] just use LIKE '%procName%' below.  Because other reports could just have dbo.procName.
report_xml.value( '(/Report/DataSets/DataSet/Query/CommandText/text())[1]', 'VARCHAR(MAX)' ) LIKE '%Your Proc Name here%'
--comment out the above and uncomment below if know your report name and want to search for that specific report.
--[x].[Name] = 'The Name Of Your Report'

Upvotes: 5

Jason A. Long
Jason A. Long

Reputation: 4442

You're in the right neighborhood... When a report RDL is published its XML is converted into a image data type and stored in dbo.Catalog.Content.

If you convert the image data to VARBINARY(MAX) and then convert to XML, you'll be able to read the XML in plain text.

SELECT TOP (10)
    *
FROM
    dbo.Catalog c
    CROSS APPLY ( VALUES (CONVERT(XML, CONVERT(VARBINARY(MAX), c.Content))) ) cx (content_xml)
WHERE 
    c.Type = 2;

From there it's just a matter of parsing the XML to dig out what you're looking for. In this case you looking for tags that look like the following...

<DataSet Name="My_stored_proc">

Upvotes: 3

Alex
Alex

Reputation: 211

Are you looking for the stored procedure name? If your looking to see where that is at its in the database itself, database > DatabaseName > Programmability > Stored Procedures. If your trying to use the query you built for a report you need to make the stored procedure or change the query type to text and paste it in the box.

Upvotes: 0

Related Questions