MS SQL Server - Server side trace to capture certain commands not working perfectl

Author

Server side trace to capture certain commands not working perfectl

kunalap

2005-07-29, 9:23 am

Hello,

I am in pain. A lot of pain.
Been working on this for quiet some time now. And now facing some crazy
problem.

This is related only to SQL 2000. I have a procedure setup to setup a trace
to capture certain commands like 'alter database', 'alter table', 'truncate
table' and 'drop table'.

I am capturing event 'Statement Completed' with the columns that I want,
including 'TextData'. (exec sp_trace_setevent @traceident, 41, 1, @on ).

Then I have filters set to read TextData column only for the commands that I
want:
exec sp_trace_setfilter @traceid = @traceident, @columid = 1,
@logical_operator = 1, @comparison_operator
= 6, @value = N'Drop database%'

and so on for each command.
The trace works fine. Captures all that I want. But the problem is, its
capturing a little more than I want.

Its capturing stuff where 'TextData' is NULL as well. Drop/Truncate are
rarely there...they are setup because we HAVE to. But I have a 1MB .trc
output file now with nothing but NULL entries.

I captures the user id, even mine when using Enterprise Manager and in the
command field (TextData) its just NULL.

I tried to setup another filter like: exec sp_trace_setfilter @traceid =
@traceident, @columid = 1, @logical_operator = 0, @comparison_operator

= 7, @value = N'NULL'

meaning, AND capture where Textdata is NOT LIKE 'NULL'. This is letting all
hell break loose. Every command being executed on server is being captured.

I have tried many permutations and combinations. But still not progress. I
don't want those NULL's to be captured. Their probably some time kind of drop
table commands within some stored procedures or something like that ???? Or
is this a flaw in the server side trace.

Running the same setup through Profiler GUI runs perfectly.

Any kind of help is apprecitated.

Thanks in advance.

-Kunal.

ChrisR

2005-07-29, 11:23 am

Did you write the script yourself? If so, get it working in the GUI then
script it out from there?

"kunalap" <kunalap@discussions.microsoft.com> wrote in message
news:138056E5-9016-45E2-860A- 823FC9CA29BD@microso
ft.com...
> Hello,
>
> I am in pain. A lot of pain.
> Been working on this for quiet some time now. And now facing some crazy
> problem.
>
> This is related only to SQL 2000. I have a procedure setup to setup a
> trace
> to capture certain commands like 'alter database', 'alter table',
> 'truncate
> table' and 'drop table'.
>
> I am capturing event 'Statement Completed' with the columns that I want,
> including 'TextData'. (exec sp_trace_setevent @traceident, 41, 1, @on ).
>
> Then I have filters set to read TextData column only for the commands that
> I
> want:
> exec sp_trace_setfilter @traceid = @traceident, @columid = 1,
> @logical_operator = 1, @comparison_operator
= 6, @value = N'Drop
> database%'
>
> and so on for each command.
> The trace works fine. Captures all that I want. But the problem is, its
> capturing a little more than I want.
>
> Its capturing stuff where 'TextData' is NULL as well. Drop/Truncate are
> rarely there...they are setup because we HAVE to. But I have a 1MB .trc
> output file now with nothing but NULL entries.
>
> I captures the user id, even mine when using Enterprise Manager and in the
> command field (TextData) its just NULL.
>
> I tried to setup another filter like: exec sp_trace_setfilter @traceid =
> @traceident, @columid = 1, @logical_operator = 0,
> @comparison_operator

> = 7, @value = N'NULL'
>
> meaning, AND capture where Textdata is NOT LIKE 'NULL'. This is letting
> all
> hell break loose. Every command being executed on server is being
> captured.
>
> I have tried many permutations and combinations. But still not progress. I
> don't want those NULL's to be captured. Their probably some time kind of
> drop
> table commands within some stored procedures or something like that ????
> Or
> is this a flaw in the server side trace.
>
> Running the same setup through Profiler GUI runs perfectly.
>
> Any kind of help is apprecitated.
>
> Thanks in advance.
>
> -Kunal.

Sponsored Links