locked
How to view SQL statements with prepared stored procedures, sp_execute RRS feed

  • Question

  • Hello,

    I was looking for a way to see the actual SQL statements executed by sp_execute in SQL Server 2012 R2 Profiler.

    Should be using in the "Events Selection" tab, selected "Show all events" and check Stored Procedures > SP:StmtCompleted to see the actual SQL statements?

    Thanks,

    Paul


    Paul



    • Moved by Olaf HelperMVP Tuesday, June 17, 2014 6:11 AM Moved from "SQL Database Engine" to a more specific forum
    • Edited by Paul Byrum Tuesday, June 17, 2014 3:37 PM Title clarification
    Tuesday, June 17, 2014 2:17 AM

Answers

  • The problem with that is that you probably would have to get the sp_prepare first which can be traced with RPC_Starting as well as the parameter order from that. (Or you can use the liht weight logging I was mentioning below, although I have to warn you as you are using binary values here as well and I did not thourogly tested against this)

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    • Marked as answer by Paul Byrum Tuesday, June 17, 2014 4:39 PM
    Tuesday, June 17, 2014 4:35 PM

All replies

  • If you want to see the currently running queries, then its fine... just follow the below links..

    http://www.mssqltips.com/sqlservertip/1264/capturing-graphical-query-plans-with-sql-server-profiler/

    https://www.simple-talk.com/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/


    Raju Rasagounder Sr MSSQL DBA


    • Edited by RAJU RG Tuesday, June 17, 2014 2:45 AM
    Tuesday, June 17, 2014 2:41 AM
  • I am able to get the SQL statements in the profile except for queries having parameters. Profiler doesn't replace the parameters with actual value while showing them. Is that your case?

    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, June 17, 2014 3:09 AM
  • If you are calling a stored procedure and want to catch the passed parameters you will have to capture the RPC_Started event. SPStmtCompleted won´t show the variable values. You might also want to take a look at a way of doing a light weight tracing done with the following solution:

    http://blogs.msdn.com/b/jenss/archive/2010/07/10/light-weight-sql-server-procedure-auditing-without-using-sql-server-auditing-version-2.aspx

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Tuesday, June 17, 2014 5:50 AM
  • I have SP:StmtCompleted turned on, as well as all events turned on. I see variables being password, but nothing useful and nothing readable.

    Users are having problems with a slow import, and I see many statements like the following. 

    exec sp_execute 4939,N'D13120614281182600799C00104EA13A0',N'S=140616215140,L=199565,I=[----]B1iP_Xcellerator',0x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D3

    8223F3E3C617574686576656E74733E3C617574686576656E7420757365723D224231696

    1646D696E222074696D657374616D703D2232303134303

    6313631343531343022206475726174696F6E3D223022206576656E743D22

    6C6F676F6E22206E616D6553706163653D2269706F222069706F537465705552493D2

    22F5265636569766542314576656E74732F636F6D2E7361702E6231692E73797374656D2E6576656E74646

    973706174636865722F69706F2F5265636569766542314576656E74732E69706F2F5265636569766542314576656E


    Paul

    Tuesday, June 17, 2014 4:03 PM
  • The problem with that is that you probably would have to get the sp_prepare first which can be traced with RPC_Starting as well as the parameter order from that. (Or you can use the liht weight logging I was mentioning below, although I have to warn you as you are using binary values here as well and I did not thourogly tested against this)

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    • Marked as answer by Paul Byrum Tuesday, June 17, 2014 4:39 PM
    Tuesday, June 17, 2014 4:35 PM
  • Thanks for your help.  This is a SAP database, so I can't modify their stored produces.  I can see adding RPC_Starting and sp_prepare and doing the light weight logging, but I feel uncomfortable modifying SAP code. I will need contact SAP for help.

    Paul

    Tuesday, June 17, 2014 4:39 PM
  • Sure thing and best decision to stay in supportability. Feel free to close the thread and mark it is answered then :-)-Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss

    Tuesday, June 17, 2014 4:41 PM