SQL Trace Stored Procedure

• Roger Abram

  Hall of Fame

  Points: 3683

  More actions

  September 30, 2008 at 5:52 am

  #193351

  I'm working on creating a stored procedure based off of a sample I found on the web and keep getting the following message after it runs (it does appear to run successfully and creates a file I can look at).

  Msg 19055, Level 16, State 1, Procedure sp_trace_setfilter, Line 1

  Filters with the same event column ID must be grouped together.

  Here's what I'm asking it to look at. Do I need to rearrange these? Thanks.

  exec sp_trace_setevent @TraceID, 10, 1, @on

  exec sp_trace_setevent @TraceID, 14, 1, @on

  exec sp_trace_setevent @TraceID, 14, 9, @on

  exec sp_trace_setevent @TraceID, 14, 6, @on

  exec sp_trace_setevent @TraceID, 14, 10, @on

  exec sp_trace_setevent @TraceID, 14, 14, @on

  exec sp_trace_setevent @TraceID, 14, 11, @on

  exec sp_trace_setevent @TraceID, 14, 12, @on

  exec sp_trace_setevent @TraceID, 10, 15, @on

  exec sp_trace_setevent @TraceID, 10, 16, @on

  exec sp_trace_setevent @TraceID, 10, 1, @on

  exec sp_trace_setevent @TraceID, 10, 9, @on

  exec sp_trace_setevent @TraceID, 10, 17, @on

  exec sp_trace_setevent @TraceID, 10, 2, @on

  exec sp_trace_setevent @TraceID, 10, 10, @on

  exec sp_trace_setevent @TraceID, 10, 18, @on

  exec sp_trace_setevent @TraceID, 10, 11, @on

  exec sp_trace_setevent @TraceID, 10, 12, @on

  exec sp_trace_setevent @TraceID, 10, 13, @on

  exec sp_trace_setevent @TraceID, 10, 6, @on

  exec sp_trace_setevent @TraceID, 10, 14, @on

  exec sp_trace_setevent @TraceID, 11, 1, @on

  exec sp_trace_setevent @TraceID, 11, 9, @on

  exec sp_trace_setevent @TraceID, 11, 2, @on

  exec sp_trace_setevent @TraceID, 11, 6, @on

  exec sp_trace_setevent @TraceID, 11, 10, @on

  exec sp_trace_setevent @TraceID, 11, 14, @on

  exec sp_trace_setevent @TraceID, 11, 11, @on

  exec sp_trace_setevent @TraceID, 11, 12, @on

  exec sp_trace_setevent @TraceID, 43, 15, @on

  exec sp_trace_setevent @TraceID, 43, 1, @on

  exec sp_trace_setevent @TraceID, 43, 9, @on

  exec sp_trace_setevent @TraceID, 43, 10, @on

  exec sp_trace_setevent @TraceID, 43, 11, @on

  exec sp_trace_setevent @TraceID, 43, 12, @on

  exec sp_trace_setevent @TraceID, 43, 13, @on

  exec sp_trace_setevent @TraceID, 43, 6, @on

  exec sp_trace_setevent @TraceID, 43, 14, @on

  exec sp_trace_setevent @TraceID, 42, 1, @on

  exec sp_trace_setevent @TraceID, 42, 9, @on

  exec sp_trace_setevent @TraceID, 42, 6, @on

  exec sp_trace_setevent @TraceID, 42, 10, @on

  exec sp_trace_setevent @TraceID, 42, 14, @on

  exec sp_trace_setevent @TraceID, 42, 11, @on

  exec sp_trace_setevent @TraceID, 42, 12, @on

  exec sp_trace_setevent @TraceID, 12, 15, @on

  exec sp_trace_setevent @TraceID, 12, 16, @on

  exec sp_trace_setevent @TraceID, 12, 1, @on

  exec sp_trace_setevent @TraceID, 12, 9, @on

  exec sp_trace_setevent @TraceID, 12, 17, @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, 18, @on

  exec sp_trace_setevent @TraceID, 12, 11, @on

  exec sp_trace_setevent @TraceID, 12, 12, @on

  exec sp_trace_setevent @TraceID, 12, 13, @on

  exec sp_trace_setevent @TraceID, 13, 12, @on

  exec sp_trace_setevent @TraceID, 13, 1, @on

  exec sp_trace_setevent @TraceID, 13, 9, @on

  exec sp_trace_setevent @TraceID, 13, 6, @on

  exec sp_trace_setevent @TraceID, 13, 10, @on

  exec sp_trace_setevent @TraceID, 13, 14, @on

  exec sp_trace_setevent @TraceID, 13, 11, @on

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 30, 2008 at 6:11 am

  #878785

  I'm guessing you need to put the first line with the filterid of 10 about 10 lines further in the code (after all the 14s).

• Roger Abram

  Hall of Fame

  Points: 3683

  More actions

  September 30, 2008 at 6:24 am

  #878795

  Thank you for the suggestion but that didn't resolve it.

  Roger

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  September 30, 2008 at 6:32 am

  #878801

  roger this is the clue 😉

  Roger Abram (9/30/2008)

  ---

  Msg 19055, Level 16, State 1, Procedure sp_trace_setfilter, Line 1

  Filters with the same event column ID must be grouped together.

  can you post the section that sets the filters using

  sp_trace_setfilter

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• Roger Abram

  Hall of Fame

  Points: 3683

  More actions

  September 30, 2008 at 6:38 am

  #878805

  Sure.....

  -- Set the Filters

  declare @intfilter int

  declare @intdbFilter int

  declare @bigintfilter bigint

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

  set @intfilter = 100

  exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter

  -- check for NULL

  if @dbname is not null

  begin

  -- database filter

  set @intdbFilter = db_id(@dbname)

  exec sp_trace_setfilter @TraceID, 3, 1, 0, @intdbFilter

  end

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  September 30, 2008 at 7:14 am

  #878835

  Why have you specified

  exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

  twice

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉