SQL Server Performance

need more info. in audit user error mess. trc file

Discussion in 'SQL Server 2005 General Developer Questions' started by dmaddhali, Feb 3, 2006.

  1. dmaddhali New Member

    HelloAll,
    I am trying to create a trace for user error messages using audit user error messages event class.I could create .trc file. But , unfortunately I am unable to get sql query which caused the error.

    This is how I created the trace file

    1) DECLARE @TraceIdOut int
    Exec sp_trace_create @traceid= @TraceIdOut OUTPUT
    ,@options = 2 ,@tracefile =N'c:parseerrors'
    PRINT @TraceIdOut


    2) Set the trace event to monitor Parse error messages
    Exec sp_trace_setevent @traceid = 2,@eventid = 162,@columnid =1, @on = @On


    3) Set the trace event status to start.
    EXEC sp_trace_setstatus @traceid = 2, @status = 1

    I am getting only one column ..that is column which shows the user error..
    for example "incorrect syntax near 'abcd' ".

    I also want the sql query which caused the error. I could give only one columnid number while setting the trace event.

    Please help me in getting the sql query which caused this error.
  2. Chappy New Member

    You can add more columns to the trace by calling setevent multiple times with different parameters
    But I notice you are already tracing the TextData column, which from what you say, contains the error message

    I wonder if it would be possible to log the trace to a table instead of a trc file. Add the SPID as a column being traced also. Perhaps then you could place a trigger on that table, and run a DBCC INPUTBUFFER on the SPID being inserted

    Ive never done this before, but maybe its worth a try
  3. dmaddhali New Member

    Thanks Chappy . running set event number of times will allow us to trace more than 1 column. But in my case no column contains information regarding executed sql statement.
    I shall use other columns instead of the sql query.

Share This Page