sp_trace_setfilter does not work

I am trying to create a server side trace via script. The trace will have a filter on LoginName. However the trace is created but the filter is not there. I used to do this all the time under SQL 2000 but I have not been able to set the filters using sp_trace_setfilter under SQL 2005. I'm using SQL 2005 with SP1. Below is the script. I just generated it from Profiler.



-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'd:\traces\trace1', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 11, 1, 6, N'app_user'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

.

Relevant Pages

  • SQL Server 2005 - Server side trace script
    ... I am trying to set up a trace that will tracks all the "RPC:Completed" events and give me some of the vital statistics like TextData, StartTime, EndTime, Duration, Reads, Writes, CPU, etc. ... I have cut and paste the script I get at the end. ... declare @TraceID int ... declare @bigintfilter bigint ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Profiler Scheduling
    ... Can you create a stored procedure from the script, ... Kalen Delaney, SQL Server MVP ... declare @TraceID int ... -- display trace id for future references ...
    (microsoft.public.sqlserver.tools)
  • sp_create_trace throws Errorcode = 12
    ... I'm trying to execute server-side trace with SqlServer 2000 on ... If it helps I added script which I tried to execute: ... declare @TraceID int ...
    (comp.databases.ms-sqlserver)
  • RE: Profiler Trace issue
    ... I hope you made the necessory changes with path and file name in the script. ... you got from the trace script. ... declare @TraceID int ...
    (microsoft.public.sqlserver.server)
  • Trace question
    ... I'm setting up a trace in Query Analyzer and having an odd problem. ... The script executes as expected ... the logical reads filter is 232. ... declare @trace_id int ...
    (microsoft.public.sqlserver.programming)