Simple Way to View SQL Query (ies) Generated by SSRS Reports?

Question

Is there a simple way to view the SQL Queries actually generated by SSRS other than running profile traces to capture them?

Is there some way from within the BIDS editor to see this?

Federico

Answer 1

In short, no. There is no good workaround. However, for development I generally created a test query alongside my work in SSRS. I would edit this inside Management Studio and then just paste the values back into BIDS. Assuming two parameters named "StudentID" and "TeacherID", the query looked like:

DECLARE @StudentID int
DECLARE @TeacherID int

SELECT @StudentID = StudentID FROM Students WHERE StudentName LIKE 'John Doe'
SELECT @TeacherID = TeacherID FROM Teachers WHERE TeacherName LIKE 'Mr. Jones'

-- PASTE IN QUERY FROM BIDS BELOW

This allowed me to use real text values from the drop-down parameter lists and simply paste in my query. Then I could optimize the query in Management Studio and then paste it back into BIDS when I was happy with the result.

Godthaab

Thanks Aaron. How would you handle or test a multi-value parameter if the user wanted to be able to enter multiple studentId's? Or for example you had 4 different students named 'John Doe'. – Federico

I'd like to say there was a neat trick, but I usually just hard-code it to make sure it works in the few instances that I needed it. SSRS does a text replacement instead of an actual parameterization so places where you'd have "WHERE StudentID IN (@StudentIDs)" it will actually output dynamic SQL like "WHERE StudentID IN (65, 66, 67)". I guess you could wrap your text inside an sp_executesql but then you lose syntax highlighting. As usual, there is no great solution. – Godthaab

Answer 2

You could run something like the below against your SSRS report server. You will be able to see the sql that is being execute by the report datasets.

;WITH XMLNAMESPACES (
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
),
ReportData AS
(
    SELECT name ReportName
           , x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType
           , x.value('CommandText[1]','VARCHAR(8000)') AS CommandText
           , x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
    FROM (SELECT  name
                  , CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
            FROM  ReportServer.dbo.Catalog
            WHERE content IS NOT NULL
                  AND type != 3) a
                  CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
)

SELECT *
FROM ReportData

Kilmer

A thousand upvotes!!!! I was blind and now I see what's running. – Rutharuthann

Answer 3

What I normally do is run SQL Profiler when I run the report and pull the query out of it with the parameters.

Hughs

Answer 4

Close the file, change the extension from .rdlc to .rdl and reopen it. It should display as HTML. Now do a search for "select" and there you go!

Protractile

run-time parameters will be included in the .rdl definition. – Mota

This will make it xml not html, at least in newer versions. – Honghonied

Answer 5

You could have many reports in your report server referencing XML namespaces from various years. This query below establishes many namespaces and then queries against all of them.

-- Declare various XML namespaces used throughout various reports
;WITH XMLNAMESPACES (
    DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
    'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner' AS rd_2005,
    'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS rd_2008,
    'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' AS rd_2010,
    'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' AS rd_2016,
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd_reportdesigner,
    'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl_2010
),
-- Cast content as XML file
ReportData AS (
    SELECT 
        name AS ReportName,
        CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
    FROM 
        ReportServer.dbo.Catalog
    WHERE 
        content IS NOT NULL
        AND type != 3
),
-- Parse each XML file across all the different XML namespace formats
ReportContents AS (
    SELECT 
        ReportName,
        reportXML.value('(//rd_2005:DataSourceName)[1]', 'VARCHAR(100)') AS DataSourceName,
        reportXML.value('(//rd_2005:CommandType)[1]', 'VARCHAR(100)') AS CommandType,
        reportXML.value('(//rd_2005:CommandText)[1]', 'VARCHAR(MAX)') AS CommandText
    FROM 
        ReportData
    UNION ALL
    SELECT 
        ReportName,
        reportXML.value('(//rd_2008:DataSourceName)[1]', 'VARCHAR(100)') AS DataSourceName,
        reportXML.value('(//rd_2008:CommandType)[1]', 'VARCHAR(100)') AS CommandType,
        reportXML.value('(//rd_2008:CommandText)[1]', 'VARCHAR(MAX)') AS CommandText
    FROM 
        ReportData
    UNION ALL
    SELECT 
        ReportName,
        reportXML.value('(//rd_2010:DataSourceName)[1]', 'VARCHAR(100)') AS DataSourceName,
        reportXML.value('(//rd_2010:CommandType)[1]', 'VARCHAR(100)') AS CommandType,
        reportXML.value('(//rd_2010:CommandText)[1]', 'VARCHAR(MAX)') AS CommandText
    FROM 
        ReportData
    UNION ALL
    SELECT 
        ReportName,
        reportXML.value('(//rd_2016:DataSourceName)[1]', 'VARCHAR(100)') AS DataSourceName,
        reportXML.value('(//rd_2016:CommandType)[1]', 'VARCHAR(100)') AS CommandType,
        reportXML.value('(//rd_2016:CommandText)[1]', 'VARCHAR(MAX)') AS CommandText
    FROM 
        ReportData
    UNION ALL
    SELECT 
        ReportName,
        reportXML.value('(//rd_reportdesigner:DataSourceName)[1]', 'VARCHAR(100)') AS DataSourceName,
        reportXML.value('(//rd_reportdesigner:CommandType)[1]', 'VARCHAR(100)') AS CommandType,
        reportXML.value('(//rd_reportdesigner:CommandText)[1]', 'VARCHAR(MAX)') AS CommandText
    FROM 
        ReportData
    UNION ALL
    SELECT 
        ReportName,
        reportXML.value('(//rd_reportdesigner//cl_2010:DataSourceName)[1]', 'VARCHAR(100)') AS DataSourceName,
        reportXML.value('(//rd_reportdesigner//cl_2010:CommandType)[1]', 'VARCHAR(100)') AS CommandType,
        reportXML.value('(//rd_reportdesigner//cl_2010:CommandText)[1]', 'VARCHAR(MAX)') AS CommandText
    FROM 
        ReportData
)
-- Final selection
SELECT DISTINCT * 
FROM ReportContents
WHERE CommandText IS NOT NULL
ORDER BY ReportName, DataSourceName;

Gazelle