SQL Server Performance Forum – Threads Archive
need more info. in audit user error mess. trc fileHelloAll,
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’
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.