Post reply

DBCC INPUTBUFFER(@@spid)

  • July 30, 2009 at 5:05 am

    #137542

    Hi,

    I am having one procedure, in which there many proceudres are called. I just want to know which query(also procedure having that query), updates the particulat table.

    For that, i have opted update trigger for that particular table. In that trigger i have used DBCC INPUTBUFFER(@@spid), which gives the main procedure name only and not exact procedure or the query which updates the table.

    Anybody having idea abt this.

    Thnks

  • July 30, 2009 at 5:17 am

    #1032289

    Hi

    Instead of dbcc inputbuffer(@spid) use below query or refer to http://www.sqlservercentral.com/redirect/articles/67645/

    for more information.

    SELECT DEST.TEXT

    FROM sys.[dm_exec_connections] SDEC

    CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST

    WHERE SDEC.[most_recent_session_id] = @spid

    [font="Verdana"]Thanks
    Chandra Mohan[/font]

  • July 30, 2009 at 7:00 am

    #1032357

    Since the query is currently running, you may need to use sys.dm_exec_requests. This will provide you with a current statement start & end offset so that you can see which statement within a batch is currently executing. You'd still use the sys.dm_exec_query_text as shown above. It just depends on how the call is performed within SQL Server. Test with both.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 31, 2009 at 7:22 am

    #1032910

    Hi,

    Thank u for your answers. I shall get if i get any errors.

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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