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?
+1
A:
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.
Aaron D
2010-08-09 19:01:43
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'.
DavidStein
2010-08-09 19:21:52
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.
Aaron D
2010-08-09 20:42:30