none
Capture T-SQL Code Being Run by Report RRS feed

  • Question

  • I am having a problem where a report is not giving me the same results as when I run the report's stored procedure manually in SSMS. So I would like to determine the exact SQL code that the Report Server is requesting of my datasource. Perhaps one of the parameters is not being passed correctly.

    But I am having a hard time figuring out how to find the stored procedure call in any Profiler results. Our main database server where the stored procedure resides is a busy server with multiple databases, so I cannot just start a trace and find my code. I have tried filtering on the database name, the application name, the login, etc. But none are showing me my procedure call.

    Any tips? I should be running the trace on my source data server, not my server where the ReportServer database is, correct?  Thanks in advance.

    Dan

    Friday, September 27, 2019 7:37 PM

Answers

  • OK, I have figured out a decent way to narrow down Profiler results so that you can see what SQL code your report server is running.

    In Profiler:

    1. File > New Trace
    2. Log onto the SQL Server where your stored procedure (or other objects you are using for your reporting) are located.
    3. On the Trace Properties screen, click the Events Selection tab.
    4. Click the Column Filters button. ApplicationName will be selected by default.
    5. On the right side, expand the Like node and paste in .Net SqlClient Data Provider
    6. On the left side, click LoginName.
    7. On the right side, expand the Like node and type in the name of the SQL user (or AD user) that actually runs the query as indicated by the Data Source in your report.
    8. Click OK.
    9. Click Run.
    10. Now, run your SSRS report and you should see the relevant data in the trace. SQL statements will show up in the trace when they are finished running.
    11. If these steps do not give your your SSRS results, try it without the LoginName filter and see if it works.
    12. Stop the trace when the report is done generating.

    • Marked as answer by djsavlon Monday, September 30, 2019 8:03 PM
    • Edited by djsavlon Monday, September 30, 2019 8:07 PM forgot a detail "SQL statements will show up in the trace when they are finished running."
    Monday, September 30, 2019 8:03 PM

All replies

  • Hello djsavlon, if you can monitor the database server while the report is being run and if you know the account used to generate the report, you can capture what is currently run active queries and capture the report script. 
    Friday, September 27, 2019 10:19 PM
  • yes. you should look at the source database server. If you have the ssrs report file, you can open that in Visual studio and find the query used that way too. Is this SSRS report?
    Friday, September 27, 2019 10:23 PM
  • Hi Dan,

    Just want to mention, that SSRS SP can only get one dataset from the SP. Other things should work exactly the same as in SSMS.

    You should use profiler to catch on the SQL Server which runs the SP, not the RS database server.

    You could also directly view the SP definition, mention this just in case it might help:How to View the Definition of a Stored Procedure

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 30, 2019 8:18 AM
  • Thank you both. My problem is that I don't know how to configure Profiler to capture these SP executions. Nothing I have tried seems to work.
    Monday, September 30, 2019 12:37 PM
  • OK, I have figured out a decent way to narrow down Profiler results so that you can see what SQL code your report server is running.

    In Profiler:

    1. File > New Trace
    2. Log onto the SQL Server where your stored procedure (or other objects you are using for your reporting) are located.
    3. On the Trace Properties screen, click the Events Selection tab.
    4. Click the Column Filters button. ApplicationName will be selected by default.
    5. On the right side, expand the Like node and paste in .Net SqlClient Data Provider
    6. On the left side, click LoginName.
    7. On the right side, expand the Like node and type in the name of the SQL user (or AD user) that actually runs the query as indicated by the Data Source in your report.
    8. Click OK.
    9. Click Run.
    10. Now, run your SSRS report and you should see the relevant data in the trace. SQL statements will show up in the trace when they are finished running.
    11. If these steps do not give your your SSRS results, try it without the LoginName filter and see if it works.
    12. Stop the trace when the report is done generating.

    • Marked as answer by djsavlon Monday, September 30, 2019 8:03 PM
    • Edited by djsavlon Monday, September 30, 2019 8:07 PM forgot a detail "SQL statements will show up in the trace when they are finished running."
    Monday, September 30, 2019 8:03 PM