SQL Profiler - Scripting a trace with HostName filter

I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so:

• Fired up Profiler

• Created an empty trace

• Added SP:Start/Complete

• Set a filter on hostname

• Scripted to test.sql file

Open file in SSMS, no sign of the filter. Rinse, repeat, same again. Assuming I was doing something daft I looked up the value to use (harder to find than it should be!) and added to my trace definition manually, which worked.

EXEC sp_trace_setfilter @TraceId, 8, 0, 6, N'MyHostName'

Came back to check on why I couldn't get this to work from Profiler and same result. I found mention of this being a problem when scripting traces for 2000 from SSMS but no mention of this situation with SSMS2005/2008 to 2005/2008 servers. Is this a bug in Profiler?

When the script is generated in Profiler 2005, the default value saved for the logical operator is 1='OR', so the trace will capture more information than we initially intended.

exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME1'    -- OR 
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2'
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3'
set @bigintfilter = 10 -- reads >= 10
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter  -- AND
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'DB'          -- OR

In Profiler >=2008, the script has 0(AND) for the first occurrence of the column being filtered.

exec sp_trace_setfilter @TraceID, 8, 0, 0, N'HOSTNAME1'    -- AND               
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME2'  
exec sp_trace_setfilter @TraceID, 8, 1, 0, N'HOSTNAME3' 
exec sp_trace_setfilter @TraceID, 35,0, 6, N'DB'           -- AND                
set @bigintfilter = 10 -- reads
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter  -- AND

This looks like a bug in Profiler 2005.

StackExchange Database Administrators Q#4798, answer score: 6