Re: Problem using SP_TRACE_SETFILTER - it doesn't seem to apply th

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Wanderer (Wanderer_at_discussions.microsoft.com)
Date: 12/31/04 

Date: Thu, 30 Dec 2004 23:09:03 -0800

Ok - so it seems that, for some reason, the sp_trace_setfilter is not
working. Going to try and build in some logic to catch and check why.
Anything that you can see from my SP, that points the way, I'd appreciate
hearing it.

Have a great New Year's eve, and a peaceful and prosperous new year

"Wanderer" wrote:

> Hmm - not working.
> When I run:
> SELECT * FROM ::fn_trace_getfilterinfo(1)
>
> I get 0 rows, that implies to me that the filters are not getting created.
> Can you confirm on a test that you DO get something from this?
>
> Thanks
>
> "Olu Adedeji" wrote:
>
> > sure thing! I hope that works. . .
> >
> > "Wanderer" <Wanderer@discussions.microsoft.com> wrote in message
> > news:E57DE62A-122D-448E-BEFB-AE3DDEE2F877@microsoft.com...
> > > Hmmm- there looks like mine:
> > > --Profiler's trace
> > > -- Set the Filters
> > > declare @intfilter int
> > > declare @bigintfilter bigint
> > >
> > > exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
> > > set @bigintfilter = 1000
> > > exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
> > >
> > > set @intfilter = 100
> > > exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter
> > >
> > > --My trace
> > > set @LogicalOperator = 0
> > > set @columnId = 13
> > > set @ComparisonOperator = 4
> > > @DurationFilterValue = 1000 (bigint)
> > > exec sp_trace_setfilter
> > >
> > @TraceId,@ColumnId,@LogicalOperator,@ComparisonOperator,@DurationFilterValue
> > > which is effectively
> > > exec sp_trace_setfilter @tracid,13,0,4,@DurationFilterValue
> > >
> > > I'll see if the hardcoding works better ... sighs
> > >
> > > .... and it would seem it doesn't! Grrr
> > >
> > >
> > >
> > > "Wanderer" wrote:
> > >
> > > > Thanks Olu, for the responses - just realized I hadn't been polite
> > enough to
> > > > acknowledge them!
> > > >
> > > > I must have mis-understood yuo last mail - I thought you were proposing
> > I
> > > > use your SP, or check how you are using the SP_TRACE_SETFILTER.
> > > >
> > > > The specific Traces I am trying to turn on are:
> > > >
> > > > set @LogicalOperator = 0 --and (1 = OR)
> > > > set @columnId = 13 --Duration
> > > > set @ComparisonOperator = 2 --Greater than
> > > > @DurationFilterValue - I am passing in 1000, i.e. 1 second
> > > >
> > > > I see you are using comparison operator of 4 - greater than or equal
> > to - I
> > > > will try that and see if that is somehopw causing the issue. If not, I
> > will
> > > > create the trace via profiler, and then try and gen the trace and see
> > what's
> > > > what.
> > > >
> > > > Thanks for the help so far!!!
> > > >
> > > > "Olu Adedeji" wrote:
> > > >
> > > > > Wanderer,
> > > > >
> > > > > as per my email, I was intimating that you open profiler and set the
> > filter
> > > > > manually, then script out the trace and cut the section that performs
> > the
> > > > > sp_trace_setfilter into your procedure substituting your parameters
> > for
> > > > > hardcoded values. then run your procedure and check the trc files to
> > see if
> > > > > the traces have been set. . from your post it is obvious that profiler
> > does
> > > > > not understand the parameters you have passed into sp_trace_setfilter
> > and as
> > > > > a result it is unable to present it in the trc file. PS. out of
> > interest
> > > > > what are you trying to filter?
> > > > >
> > > > >
> > > > > PS. I don't dispute that your trace runs fine . . I am merely trying
> > to
> > > > > help resolve the problem
> > > > >
> > > >
> >
> >
> >

Relevant Pages