Post reply

Find SQ!L code being executed by EXEC SP_EXECUTE Stored Procedure

  • May 25, 2011 at 1:23 am

    #238755

    Hi All,

    Is there a way to identify the SQL code that is being executed by the Stored Procedure exec sp_executed

    I understand that the Stored Procedure is passed a Handle number to the SQL Execution plan...but I'm not sure how to use this to get the SQL code.

    Working on SQL Server 2008 R2.

    Thanks

    Denesh

  • May 25, 2011 at 1:46 am

    #1329513

    Hi All,

    I believe SQL Profile will show it. Before the SP_EXECUTE, there is a SP_PREPARE statement that shows the SQL code.

    Thanks

    Denesh

  • May 25, 2011 at 5:41 am

    #1329601

    Denesh Naidoo (5/25/2011)

    Hi All,

    I believe SQL Profile will show it. Before the SP_EXECUTE, there is a SP_PREPARE statement that shows the SQL code.

    Thanks

    Denesh

    In what column do you see the prepared text? I tried this option once before and got nowhere!

  • May 25, 2011 at 5:45 am

    #1329603

    Hi,

    It's in the TextData column.

    It's actually the line before you the SP_EXECUTE

  • May 25, 2011 at 5:51 am

    #1329605

    What's the event? Can you provide a quick screenshot?

  • May 26, 2011 at 5:32 am

    #1330188

    Hi,

    I've attached a screen shot from Profiler.

    If you look at the line that is highlighted in the TextData column...it reads

    declare @p1 int

    set @p1=589

    exec sp_prepare @p1

    If you look at the next line it reads

    exec sp_execute 589,'10166'

    You match a sp_execute with a sp_prepare with the first number after sp_execute which in this case is 589. The second number 10166 is a variable that is used in the SQL code.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply