Trace filter not working correctly

• zach_john

  SSCertifiable

  Points: 5922

  More actions

  September 30, 2009 at 2:25 pm

  #205222

  Having two filters works fine in profiler but when i script and run as a trace the filter is ignored. If I comment out one of the filters it works fine for the remaining filter.

  declare @rc int

  declare @TraceID int

  declare @maxfilesize bigint

  set @maxfilesize = 5

  exec @rc = sp_trace_create @TraceID output, 0, N'd:\TraceData\dmlfrome', @maxfilesize, NULL

  if (@rc != 0) goto error

  print @TraceID

  print @rc

  declare @on bit

  set @on = 1

  exec sp_trace_setevent @TraceID, 10, 15, @on

  exec sp_trace_setevent @TraceID, 10, 10, @on

  exec sp_trace_setevent @TraceID, 10, 11, @on

  exec sp_trace_setevent @TraceID, 10, 35, @on

  exec sp_trace_setevent @TraceID, 10, 12, @on

  exec sp_trace_setevent @TraceID, 10, 6, @on

  exec sp_trace_setevent @TraceID, 10, 14, @on

  exec sp_trace_setevent @TraceID, 12, 15, @on

  exec sp_trace_setevent @TraceID, 12, 1, @on

  exec sp_trace_setevent @TraceID, 12, 6, @on

  exec sp_trace_setevent @TraceID, 12, 10, @on

  exec sp_trace_setevent @TraceID, 12, 14, @on

  exec sp_trace_setevent @TraceID, 12, 11, @on

  exec sp_trace_setevent @TraceID, 12, 35, @on

  exec sp_trace_setevent @TraceID, 12, 12, @on

  declare @intfilter int

  declare @bigintfilter bigint

  --exec sp_trace_setfilter @TraceID, 10, 0, 7, N'REPLICATION MONITOR'

  exec sp_trace_setfilter @TraceID, 11, 1, 6, N'e%'

  exec sp_trace_setstatus @TraceID, 1

  goto finish

  error:

  select ErrorCode=@rc

  finish:

  go

  
  John Zacharkan

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  September 30, 2009 at 6:07 pm

  #1060245

  What exactly you want to flter out?

  Mj

• zach_john

  SSCertifiable

  Points: 5922

  More actions

  October 1, 2009 at 11:36 am

  #1060588

  I had two filters -

  1) Include only logins that start with the letter 'E'

  2) Exclude Appplications that are 'REPLICATION MONITOR'

  When I run my profiler interactive this works, if I save it off to and run the trace the filters are ignored. If I comment out filter 2 the filter works for 1, if I comment out 1 filter 1 works.

  
  John Zacharkan

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  October 2, 2009 at 4:09 pm

  #1061244

  Try:

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'REPLICATION MONITOR'

  exec sp_trace_setfilter @TraceID, 11, 0, 6, N'e%'

  Hope this works!

  MJ