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

Quinn (dellsql@newsgroups.nospam) writes:
I'm not sure how you deduce that the filter is not there. I tried your
script, changed to a login on myserver. I logged as that user, ran
some queries, and ran some queries from Windows login. I then stopped
the trace and used fn_trace_gettable to look at the result, and it seemed
OK to me.

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. Also when I look at the trace file's properties using the profiler
gui, there's nothing in the filter section.

Show quoteHide quote

Quinn (dellsql@newsgroups.nospam) writes:
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


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.

Hi Erland,
Thanks for your submitting a feedback to Microsoft. Yes, I also reproduced
this issue and it seemed most likely a product issue.

Hi Dellsql,
Since Erland had submitted a feedback which will be routed to the product
team, it may be fixed in the next release.
If this issue is urgent to your business, I also recommend that you contact
CSS to see if they could publish a hotfix for you individually; if it is
proved to be a product issue, the call may be free.

To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.

Please feel free to let us know if you have any other questions or concerns.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Profiler: SQL:StmtRecompile - EvenSubClass definitions?
who or what is accessing my table?
Stored procedure debugging
SSIS on a separate box
Can SQL 2000 Enterprise Manager & SQL 2005 Mgnt Studio coexist ?
Recommendations for SQL Server-compatible database design tool?
BOL (2005) Favorites
Tool to create link between multiple databases
Fickle Import Performance Data... for profiler 2005
SS2005: Change query order in SQL Management Studio Solution Explorer