Why am I unable to create a filter on a trace in SQL Server?

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?

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

StackExchange Database Administrators Q#324460, answer score: 7