MS SQL Server Tools - Filtering out blank TextData in Profiler

Author

Filtering out blank TextData in Profiler

Vern Rabe

2005-11-04, 1:28 pm

SQL Server 2000. When I set up a Profiler trace for SP:StatementComplete
d
events, and add a filter on TextData, e.g., "-- ProcName%", I get many events
where the TextData is blank, or NULL. How can I filter those out?

Thanks
Vern

David Gugick

2005-11-04, 1:28 pm

Vern Rabe wrote:
> SQL Server 2000. When I set up a Profiler trace for
> SP:StatementComplete
d events, and add a filter on TextData, e.g., "--
> ProcName%", I get many events where the TextData is blank, or NULL.
> How can I filter those out?
>
> Thanks
> Vern

If the TextData column is not trapped for a specific event, you cannot
filter on it. On which events are you seeing this issue?

--
David Gugick
Quest Software

Jasper Smith

2005-11-04, 1:28 pm

You can't in SQL2000. SQL2005 improves on this and does allow you to filter
these out. In SQL2000 you can load the trace files into a table using
fn_trace_gettable and them remove all the irrelevant rows.

--
HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Vern Rabe" < VernRabe@discussions
.microsoft.com> wrote in message
news:F7BAAF0B-BFD9-4C5F-A677- 1261A27A85B5@microso
ft.com...
> SQL Server 2000. When I set up a Profiler trace for SP:StatementComplete
d
> events, and add a filter on TextData, e.g., "-- ProcName%", I get many
> events
> where the TextData is blank, or NULL. How can I filter those out?
>
> Thanks
> Vern

Vern Rabe

2005-11-04, 1:28 pm

I am seeing this on SP:StatementComplete
d event, which does trap TextData. I
am only tracing that one event, and I only have the one filter on TextData,
where it's Like "-- ProcName%" (plus the default filter on application).

Jasper said it's not filterable without going to a table (or file?), but is
in 2005, so that's the only additional incentive I need to upgrade my tools
today.

Thanks

Vern

"David Gugick" wrote:

> Vern Rabe wrote:
>
> If the TextData column is not trapped for a specific event, you cannot
> filter on it. On which events are you seeing this issue?
>
> --
> David Gugick
> Quest Software
>
>
>

Jasper Smith

2005-11-04, 8:24 pm

Although the filter only works against SQL2005. If tracing a SQL2000
instance using SQL 2005 Profiler then the option to exclude rows with no
values is greyed out. You need to be tracing a SQL2005 instance for it to
work.

--
HTH

Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

"Vern Rabe" < VernRabe@discussions
.microsoft.com> wrote in message
news:D4856401-32D5-44B7-996C- DE5EFEB67B6B@microso
ft.com...[color=darkred]
>I am seeing this on SP:StatementComplete
d event, which does trap TextData.
>I
> am only tracing that one event, and I only have the one filter on
> TextData,
> where it's Like "-- ProcName%" (plus the default filter on application).
>
> Jasper said it's not filterable without going to a table (or file?), but
> is
> in 2005, so that's the only additional incentive I need to upgrade my
> tools
> today.
>
> Thanks
>
> Vern
>
> "David Gugick" wrote:
>

David Gugick

2005-11-04, 8:24 pm

Vern Rabe wrote:
> I am seeing this on SP:StatementComplete
d event, which does trap
> TextData. I am only tracing that one event, and I only have the one
> filter on TextData, where it's Like "-- ProcName%" (plus the default
> filter on application).
>

I tried this on SQL Server 2000 SP4 and don't seem to see the same problem
(unless I'm not understanding something). I created three procedures MyProc,
MyProc2, and MyPro and started a trace with SQL:StmtCompleted,
RPC:Completed, and SP:StmtCompleted with a filter on the TextData column for
LIKE "-- MyProc".

When I execute each procedure, I see the SP:StmtCompleted event only for
MyProc and MyProc2. I do not see Mypro. No other events are raised because
of the filter.

If this is different than what you seeing, can you explain in more detail
what's going on?

--
David Gugick
Quest Software

Jeff Plumb

2005-11-08, 4:10 pm

Hi Vern,

We're about to release a new SQL Server monitoring tool, named
SQLBenchmarkPro at GAJSoftware (www.gajsoftware.com). We have been in beta
for a few months & will be releasing sometime in the next few weeks (as our
bug-count is fairly low now). SQLBenchmarkPro basically wraps the SQL
Profiler API & allows you to create trace templates & run them against
multiple servers either manually or against schedules you define.

It also allows you to add a secondary filter to a trace, so that you can
filter
using a where clause with any combination of and's and or's. This should
solve
the problem that you are describing.

It also provides graphical reporting and has analytical performance
capabilities. There is a zero install footprint required on your production
machine (as everything can run off another machine/s). If you're interested,
register on the site & download the beta from the "Download" page.

Regards,
Jeff Plumb
http://www.gajsoftware.com

"Vern Rabe" < VernRabe@discussions
.microsoft.com> wrote in message
news:D4856401-32D5-44B7-996C- DE5EFEB67B6B@microso
ft.com...[color=darkred]
>I am seeing this on SP:StatementComplete
d event, which does trap TextData.
>I
> am only tracing that one event, and I only have the one filter on
> TextData,
> where it's Like "-- ProcName%" (plus the default filter on application).
>
> Jasper said it's not filterable without going to a table (or file?), but
> is
> in 2005, so that's the only additional incentive I need to upgrade my
> tools
> today.
>
> Thanks
>
> Vern
>
> "David Gugick" wrote:
>

Sponsored Links