SQL Server Performance Forum – Threads Archive

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

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’carseerrors’
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.

---

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

---

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.

---

]]>