Eliminate Null's from server side trace

Last Post 15 Dec 2007 10:21 AM by trans53. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
trans53
New Member
New Member

--
14 Dec 2007 01:58 PM
Hi guys,

I would like to setup a server side trace to return only statements where text like '%drop%.
I did create a stored procedure with SQL:StmtCompleted and then set a filter like
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%drop%'.

Everything works fine and I can catch statements with a drop command but I do also get many statements with NULL in a textData column when I use (FROM :: fn_trace_gettable) function to retrieve data.
I also tried to open trace in Profiler but I have blanks instead.
Statement like this (exec sp_trace_setfilter @TraceID, 1, 0, 7, N'NULL) also didn't help.

Is there any way how to eliminate records if there is a Null in a TextData column using server side trace?

Bellow is a sample how I do this but it's not working.

Thanks

exec sp_trace_setevent @TraceID, 41, 27, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 8, @on
exec sp_trace_setevent @TraceID, 41, 26, @on
exec sp_trace_setevent @TraceID, 41, 41, @on
exec sp_trace_setevent @TraceID, 41, 3, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 15, @on

exec sp_trace_setfilter @TraceID, 1, 0, 6, N'%drop%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'NULL'

trans53
New Member
New Member

--
15 Dec 2007 10:21 AM
Thank you for reply,

So basically it's not possible to do so in SS 2000?

I got how to get this using SELECT * FROM ::fn_trace_gettable(xxxx) WHERE [textdata] IS NOT NULL
but was thinking to prevent those records into the trace at all if possible, because trace files grow huge very fast and i need to run traces constantly for 2 weeks in a row.
trans53
New Member
New Member

--
17 Dec 2007 02:59 PM
it's interesting that after i switched to event 12( sql:batchcompleted) i do not get null records back. I am very curious why i don't get Null's this way.
You are not authorized to post a reply.

Acceptable Use Policy