May 15, 2008 at 10:10 am
I built a custom auditing construct where I store the text of the stored procedure I am currently executing. The code I use to accomplish this is below:
CREATE PROCEDURE mySproc
AS
--some list of commands/inserts/etc
-- audit this procedure
DECLARE @sql_handle-2 varbinary(max)
DECLARE @AuditedText varchar(max)
SELECT @sql_handle-2 = sql_handle FROM sys.dm_exec_requests WHERE sql_handle IS NOT NULL
SELECT @AuditedText = [text] FROM sys.dm_exec_sql_text(@sql_handle)
INSERT INTO AuditLog(AuditedText)
VALUES(@AuditedText)
99 out of 100 times this works perfectly but I'm noticing that sometimes it will pull text completely unrelated to what was actually executed....for example this was returned once:
create procedure sys.sp_trace_getdata
(@traceid int,
@records int = 0
)
as
select * from OpenRowset(TrcData, @traceid, @records)
or a backup statement....
My question is, why is the sql_handle not returning the handle of the text that was executed when I thought this was specific to the spid tied to the procedure? Could there be multiple sql_handles in the DMV and I'm pulling back the wrong one? How can I ensure this sql_handle is for this procedure?
Any ideas?
May 19, 2008 at 9:24 pm
No one uses sql_handle??
July 30, 2008 at 7:24 am
Maby to late but
try
SELECT @sql_handle-2 = sql_handle FROM sys.dm_exec_requests WHERE session_id = @@spid and sql_handle IS NOT NULL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply