Post reply

sp_execute

  • September 19, 2007 at 2:19 pm

    #70250

    When I see SQL Statemnet dbcc inputbuffer of a SPID, I am seeing as (sp_execute; 1). What ever SPID I use, I am seeing the same.

    How to see the exact SQL statement what they are running? Even with the trace I am not able to see SQl statements.

    Any info is greatly appreciated.

  • September 19, 2007 at 4:12 pm

    #734740

    With SQL Server Trace you should select RPC:Completed, Statement:Completed and Batch:Completed.

    sp_execute is called right after catching(compiling) an statement


    * Noel

  • September 20, 2007 at 11:19 am

    #735019

    I selected select RPC:Completed, Statement:Completed and Batch:Completed in the trace. But I am cannot get the exact SQL Statements inside the following statements:

    sp_execute 4

    sp_unprepare 4

    I can see some select statements in the middle of the above SQL but I am not able to identify which select statement belongs to what. And also select statements are something like which doesn't look relevant,

    select "T3"."C1" AS "C0", "T3"."C2" AS "C1", "T3"."C5" AS "C2", "T3"."C3" AS "C3", "T3"."C6" AS "C4", "T3"."C4" AS "C5", "T3"."C7" AS "C6", "T3"."C0" AS "C7", "T3"."C8" AS "C8", "T1"."C4" AS "C9", "T3"."C9" AS "C10", "T3"."C10" AS "C11", "T3"."C11" AS "C12

    Also What is sp_unprepare?

     

  • September 21, 2007 at 4:43 am

    #735167

    run the following:

    /* code to determine actual SQL when DBCC INPUTBUFFER returns

      code like sp_prepareexec:1

     replace spid with your actual spid value

    */ 

    DECLARE @Handle binary(20)

    SELECT @Handle = sql_handle FROM master.dbo.sysprocesses

    WHERE spid = 60

    SELECT * FROM ::fn_get_sql(@Handle)

     

    ---------------------------------------------------------------------

  • October 30, 2007 at 10:07 am

    #746731

    The column name 'sql_handle' is available in SQL Server 2005 but not in SQL Server 2000.

    Is there any way to find out the same information in SQL Server 2000?

  • October 31, 2007 at 7:19 am

    #747050

    Mohamed, the code supplied definitely works in SQL 2000.

    regards

    george

    ---------------------------------------------------------------------

  • September 25, 2008 at 9:50 pm

    #877416

    I've tested it and it works on 2005, however the function ::fn_get_sql is going to be deprecated by Microsoft.

    I believe that another way is to do the following:

    /* code to determine actual SQL when DBCC INPUTBUFFER returns

    code like sp_prepareexec:1

    replace spid with your actual spid value

    */

    DECLARE @Handle binary(20)

    select @Handle = plan_handle from sys.dm_exec_requests where session_id=spid

    select * from sys.dm_exec_sql_text(@HANDLE)

  • August 27, 2009 at 1:33 am

    #1045363

    Correct tsql as following:

    DECLARE @Handle binary(20)

    select @Handle = plan_handle from sys.dm_exec_requests where session_id=@@spid

    select * from sys.dm_exec_sql_text(@HANDLE)

Viewing 8 posts - 1 through 7 (of 7 total)

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