Post reply

Retrieving the text of a query

  • September 8, 2005 at 9:22 am

    #165845

    I would like to write a trigger that captures the text of a query against a table and insert it into an audit table.  So when an update executes against a user table, I want the actual SQL statement.

    Is there a system variable/sproc/function that captures this?

    If not, has anyone done this another way?

  • September 8, 2005 at 9:24 am

    #588428

    Start a trace with the profiler that checks the the words update and the table name. Make sure it inserts into a table and you're good to go.

  • September 8, 2005 at 9:26 am

    #588429

    Can't use profiler.  This has to be done within a trigger.

  • September 8, 2005 at 9:29 am

    #588431

    Not gonna happen with anything else than a trace or s shitload of recoding.

  • September 8, 2005 at 10:19 am

    #588456

    I have never tried this but here is a thought

    You can run dbcc inputbuffer (@@SPID) to read the buffer contents. Not sure if that will help you though. Keep in mind that there is a limit of 255 chars on the string and if the update happens through RPC only the procname is avilable

    This is probably best done with log reader tools but is not going to be cheap

     


    * Noel

  • September 8, 2005 at 10:34 am

    #588461

    You can try some server side tracing, (not using profile)

    But I do not think the work can be done in a trigger.

    http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

     

  • September 8, 2005 at 11:39 am

    #588483

    I was hoping not to have to use tracing (because of the pitfalls with profiler) but this could be a good solution.

    Have you used this to just capture the query text?

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

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