Re: sp_trace_setfilter does not work

Tech-Archive recommends: Fix windows errors by optimizing your registry

Quinn (dellsql@xxxxxxxxxxxxxxxxx) writes:
Because when I open the trace file, I see all activity. I want to only see
the activity from my application's login which is why I'm using the login
filter.

OK, so I did some research, and it is obvious that the Profiler generates
an incorrect trace script. For the trace with a simple filter that you
posted, the bug did not cause any problem.

The error lies in the thid parameter:

exec sp_trace_setfilter @TraceID, 11, 1, 6, N'sommar'

1 means OR, so when combined with some other filter, like the default
filter on application, the filter became meaningless.

I also found that when you checked the options "Exclude rows that do not
have values", this was scripted as

exec sp_trace_setfilter @TraceID, 11, 0, 1, N''

Here, the third parameter is correctly set to 0, but the last parameter
should be NULL - at least that is actually what Profiler emits when it
sets up the filter.

The workaround is obvious: review and correct the scripted filter.

I tested this in the CTP of SP2 as well, and it does not seem to be fixed.
I searched the Connect site, but I was surprised to not find bug for
this - I would really expect this to be a known issue. Anyway, I submitted
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=258393


Also when I look at the trace file's properties using the profiler
gui, there's nothing in the filter section.

I don't think the filter settings are saved with a server-side trace.
When you save a trace to file from Profiler it is, but if you look at
the files, you can see that the file formats are completely different.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: sp_trace_setfilter does not work
    ... I want to only see the activity from my application's login which is why I'm using the login filter. ... Also when I look at the trace file's properties using the profiler gui, there's nothing in the filter section. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)
  • RE: Filter in Replication
    ... login and password to the SQL Server database. ... > device used by an individual user or multiple users? ... > can't rely on this in the filter. ...
    (microsoft.public.sqlserver.replication)
  • Re: [PATCH 0/3] ftrace: updates for tip
    ... I added a "trace" flags field in the task structure. ... The second patch uses this for the ftrace pid code. ... Regarding the filter functions, ... to only specify the PID, ...
    (Linux-Kernel)
  • Re: sp_trace_setfilter does not work
    ... However the trace is created but the filter ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: application slow
    ... experience unusual slowness, including timeouts. ... Could the cause be external to SQL Server? ... You might try the same trace without a filter during the problem period ...
    (microsoft.public.sqlserver.server)