Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Fired up Profiler
  2. Created an empty trace
  3. Added SP:Start/Complete
  4. Set a filter on hostname
  5. 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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

You need to check if the server trace file has the column added in the list of columns for all your events. So if you have events 10 and 12 (RPC:Completed and SQL:BatchCompleted), you have to run sp_trace_setevent for all columns, including for column Hostname:

exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 12, 8, @on

where 10 and 12 - events and 8 - column Hostname

sp_trace_setevent says:

"Users must execute sp_trace_setevent for each column added for each event. During each execution, if @on is set to 1, sp_trace_setevent adds the specified event to the list of events of the trace. If @on is set to 0, sp_trace_setevent removes the specified event from the list."

After that the filter should be able to work properly.

share|improve this answer
    
The issue is that if I set HostName as a filter in Profiler, it doesn't appear in the scripted trace definition. If I add the filter manually to my scripted definition, it works fine. –  Mark Storey-Smith Aug 19 '11 at 10:46
    
Mark - You said "Open file in SSMS, no sign of the filter.". From this I understand that the sp_trace_setfilter is not scripted. But have you checked if the event is set for that specific column: exec sp_trace_setevent _TraceID, 12, 8, _on (instead of 12 need your used event id)? –  Marian Aug 19 '11 at 11:11
    
You might be right. I just went throught the process again with a single event and it scripted just fine. I would of sworn I selected HostName with "Select column" at the top of the grid (to ensure it was selected for all events) but looks like I went wrong somewhere! I'll play around with various combinations later and update the original Q if there are still any oddities. –  Mark Storey-Smith Aug 19 '11 at 13:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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