View Single Post

   
  #2 (permalink)  
Old 04-24-2008, 06:07 PM
Plamen Ratchev
 
Posts: n/a
Default Re: capture and save running sql query to database table (for logging)

You may not be able to do that in a trigger. In SQL Server 2005 you can get
the current statement with this query:

SELECT [text]
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS S
WHERE session_id = @@SPID;

However, running this inside a trigger returns the SQL statement to create
the trigger.

The only statement that will actually output the current query is:

DBCC INPUTBUFFER(@@SPID);

But you cannot really store the result set from DBCC to a table.

One way to accomplish what you need is to set up a server side trace.

BTW, if the purpose of this is to prevent SQL injection attacks, you can go
the other way around and fix the code to prevent them rather than audit.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply With Quote