Easy way to view SQL query (s) generated by SSRS reports?

Question

Is there an easy way to view the SQL queries actually generated by SSRS other than running profile tracing to capture them?

Is there any way from the BIDS editor to see this?

Answer 1

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

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 lists of parameters and just insert my query. Then I could optimize the query in Management Studio, and then paste it back into BIDS when I was pleased with the result.

Answer 2

You can run something like below on the SSRS report server. You will be able to see the sql that is running 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 

Answer 3

What I usually do is run SQL Profiler when the report starts and display the query from it using the parameters.

Answer 4

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