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?

0

2 Answers 2

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
2
  • Thank you for pushing me into a right direction. The point was not the wrong order in my script, but 1 already existing filter created by someone for testing proposes with no warning on me. The rule you mentioned applies not only to creation but also to updating a trace with additional filter. Once created filter sequence is ready and can be only updated either by adding new line for a very last column of the "create" script or you have to drop the trace definition and recreate it. It's not possible to add a new filter later if the column you need is not the very last defined before. Commented Mar 7, 2023 at 13:20
  • @Mainecoon21, correct. I edited by answer to make the requirement clearer.
    – Dan Guzman
    Commented Mar 7, 2023 at 13:44
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: 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.