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 &gt;= 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.