4

Because of an outdated SQL Server version I have to use server-side traces to collect some information, but if I try to implement it I get the error Filters with the same event column ID must be grouped together. My code:

exec sp_trace_setfilter 2, 10, 0, 7, N'SQL Server Profiler%'
exec sp_trace_setfilter 2, 10, 0, 7, N'DatabaseMail%'
exec sp_trace_setfilter 2, 10, 0, 7, N'SQLAgent%'

KnowledgeBase example (https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setfilter-transact-sql?view=sql-server-ver16):

sp_trace_setfilter  1, 10, 0, 6, N'SQLT%';  
sp_trace_setfilter  1, 10, 0, 6, N'MS%';

As often it may be something obvious, but I do not have an idea why it doesn't work (it's not a semicolon).
Does one of you have an idea?

Doug Deden
  • 2,171
  • 10
  • 14
Mainecoon21
  • 175
  • 9

2 Answers2

7

I get an error "Filters with the same event column ID must be grouped together".

It seems the trace already has one or more filters on the ApplicationName column (column 10) followed by filters on other columns. You get the error when you add another ApplicationName filter because the column is different than the last filter added. SQL Trace requires that all filters on a given column be added in sequence (i.e. "grouped together").

Problem script example:

-- add application name not like filter
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft SQL Server Management Studio%'

-- add host name equal filter
exec sp_trace_setfilter @TraceID, 8, 0, 0, N'YourServer'


-- add another application name not like filter
-- this errs because the last filter added is not application name
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler%'

Remediated script:

-- add application name not like filters
-- this succeeds because the app name filters are grouped together
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft SQL Server Management Studio%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler%'

-- add host name equal filter
exec sp_trace_setfilter @TraceID, 8, 0, 0, N'YourServer'

Note this grouping requirement applies to the initial trace create script as well as when filters are subsequently added. The implication when adding another filter to a trace is one of the following apply:

  • trace have no filters
  • trace not already have a filter on the column
  • the last filter added to the trace must be on the same column
Dan Guzman
  • 28,989
  • 2
  • 46
  • 71
0

Unless you provide the full code, finding out what is wrong will be hard. I used the same filters you provided in the question and could successfully start a stop a server-side trace. I am using:

Microsoft SQL Server 2022 (RTM-CU1) (KB5022375) - 16.0.4003.1 (X64) Jan 27 2023 16:51:31 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19044: )

Here is the sample I ran:

-- Variables
DECLARE @rc int, @TraceID int, @filename NVARCHAR(256), @maxsize BIGINT, @on BIT
DECLARE @Folder VARCHAR(1000)

-- Create the holding folder
SET @Folder = 'C:\temp\testtrace1'


exec master.dbo.xp_create_subdir @Folder


SELECT @filename = @Folder + '' + REPLACE(@@SERVERNAME, '', '_') + '__sp_trace'
SELECT @maxsize=50, @on=1


-- Create a Queue
EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @filename, @maxsize, NULL 
IF (@rc != 0) GOTO error


-- Set the events
exec sp_trace_setevent @TraceID, 10, 2, @on  -- Needed for 2008 RML
exec sp_trace_setevent @TraceID, 12, 2, @on  --  Needed for 2008 RML 
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 8, @on


exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'DatabaseMail%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'


-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1


-- Wait 1 min
WAITFOR DELAY '00:01:00'


-- Stop/Delete the trace
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2


goto finish


error: 
select ErrorCode=@rc


finish:
SqlWorldWide
  • 13,687
  • 3
  • 30
  • 54