Issue with sp_trace_setfilter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Issue with sp_trace_setfilter

Author

Topic

Donger
Starting Member

7 Posts

Posted - 2008-04-28 : 17:02:56

Hi Folks, first time poster, long time listener.

We're SQL Server 2005 SP2 x64, and I'm playing around with server side tracing. Specifically, we're trying to audit anyone doing UPDATE on the database. We don't want to capture anything else (like user 'SELECT's), that would generate unnecessary events.

What I've done:
1) Create a trace/template in SQL profiler to capture "SQL:StmtCompleted"
2) Add a filter on 'TextData' column, using 'Like'; 'UPDATE%'
3) Profiler trace using this template works fine, I'm capturing the correct data and nothing else.
4) In the profiler tool, I did 'Export-Script Trace Definition'. This created a script file that had the following line:

exec sp_trace_setfilter @TraceID, 1, 1, 6, N'UPDATE%'

5) I can run the server side trace (I'm tracing to a filename 1.trc on the server). Unfortunately - here's the issue - when I open the trace file using profiler, it looks like I have captured all SQL:StmtCompleted events, even the ones that I did not want (like SELECT statements). The filter is not working.

Where is my error? Anyone have ideas?

Thanks in advance for the help!

Donger

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 17:16:46

Try 0 for @logical_operator. That's what I've got in my code for the first filter.

Here is mine:
EXEC dbo.sp_trace_setfilter @traceId, 11, 0, 6, N'YadaYada'
EXEC dbo.sp_trace_setfilter @traceId, 11, 1, 6, N'YadaYadaYada'
EXEC dbo.sp_trace_setfilter @traceId, 28, 0, 0, 20816

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Donger
Starting Member

7 Posts

Posted - 2008-04-28 : 17:42:51

Hi Tara, thanks for the quick response! After your message I went around and did some additional experimentation.

I had to do two things to get this to work:

1) Modify 1 to 0
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'UPDATE%' --changed now to:
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'UPDATE%'

2) Moved the setfilter line to the END of the list.
--this code doesn't work:
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'UPDATE%'
exec sp_trace_setfilter @TraceID, 10, 1, 6, N'Microsoft SQL Server Management Studio - Query'
exec sp_trace_setfilter @TraceID, 10, 0, 1, N''

--this code WORKS:
exec sp_trace_setfilter @TraceID, 10, 1, 6, N'Microsoft SQL Server Management Studio - Query'
exec sp_trace_setfilter @TraceID, 10, 0, 1, N''
exec sp_trace_setfilter @TraceID, 1, 0, 6, N'UPDATE%'

I'm still a bit fuzzy why the first piece of code doesn't work correctly....but thanks anyway!

Donger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources