|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Filtering out blank TextData in ProfilerSQL Server 2000. When I set up a Profiler trace for SP:StatementCompleted
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 wrote:
> SQL Server 2000. When I set up a Profiler trace for If the TextData column is not trapped for a specific event, you cannot > SP:StatementCompleted 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 filter on it. On which events are you seeing this issue? -- David Gugick Quest Software I am seeing this on SP:StatementCompleted 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 Show quote "David Gugick" wrote: > Vern Rabe wrote: > > SQL Server 2000. When I set up a Profiler trace for > > SP:StatementCompleted 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 > > > 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. -- Show quoteHTH 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" <VernR***@discussions.microsoft.com> wrote in message news:D4856401-32D5-44B7-996C-DE5EFEB67B6B@microsoft.com... >I am seeing this on SP:StatementCompleted 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: >> > SQL Server 2000. When I set up a Profiler trace for >> > SP:StatementCompleted 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 >> >> >> Vern Rabe wrote:
> I am seeing this on SP:StatementCompleted event, which does trap I tried this on SQL Server 2000 SP4 and don't seem to see the same problem > 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). > (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 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 Show quote "Vern Rabe" <VernR***@discussions.microsoft.com> wrote in message news:D4856401-32D5-44B7-996C-DE5EFEB67B6B@microsoft.com... >I am seeing this on SP:StatementCompleted 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: >> > SQL Server 2000. When I set up a Profiler trace for >> > SP:StatementCompleted 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 >> >> >> 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. -- Show quoteHTH 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" <VernR***@discussions.microsoft.com> wrote in message news:F7BAAF0B-BFD9-4C5F-A677-1261A27A85B5@microsoft.com... > SQL Server 2000. When I set up a Profiler trace for SP:StatementCompleted > 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  |
|||||||||||||||||||||||
Other interesting topics