sp_trace_setfilter
Applies a filter to a trace. sp_trace_setfilter may be executed only on existing traces that are stopped (status is 0). SQL Server 2000 will return an error if this stored procedure is executed on a trace that does not exist or whose status is not 0.
Syntax
sp_trace_setfilter [ @traceid = ] trace_id
, [ @columnid = ] column_id
, [ @logical_operator = ] logical_operator
, [ @comparison_operator = ] comparison_operator
, [ @value = ] value
Arguments
[ @traceid = ] trace_id
Is the ID of the trace to which the filter will be set. trace_id is int, with no default. The user employs this trace_id value to identify, modify, and control the trace.
[ @columnid = ] column_id
Is the ID of the column on which the filter will be applied. column_id is int, with no default. If column_id is NULL, SQL Server clears all filters for the specified trace.
[ @logical_operator = ] logical_operator
Specifies whether the AND (0) or OR (1) operator will be applied. logical_operator is int, with no default.
[ @comparison_operator = ] comparison_operator
Specifies the type of comparison to be made. comparison_operator is int, with no default. The table contains the comparison operators and their representative values.
| Value | Comparison operator |
|---|---|
| 0 | = (Equal) |
| 1 | <> (Not Equal) |
| 2 | > (Greater Than) |
| 3 | < (Less Than) |
| 4 | >= (Greater Than Or Equal) |
| 5 | <= (Less Than Or Equal) |
| 6 | LIKE |
| 7 | NOT LIKE |
[ @value = ] value
Specifies the value on which to filter. The data type of value must match the data type of the column to be filtered. Thus, if the filter is set on an Object ID column that is an int data type, value must be int. NULL values and empty strings are not allowed; when a column value is null for an event, SQL Server will pass any filter defined on that column. If value is nvarchar or varbinary, it can have a maximum length of 8000.
When the comparison operator is LIKE or NOT LIKE, the logical operator can include "%" or other filter appropriate for the LIKE operation.
To apply the filter between a range of column values, sp_trace_setfilter must be executed twice -- once with a '>=' comparison operator, and another time with a '<=' operator.
Return Code Values
This table describes the code values that users may get following completion of the stored procedure.
| Return code | Description |
|---|---|
| 0 | No error. |
| 1 | Unknown error. |
| 2 | The trace is currently running. Changing the trace at this time will result in an error. |
| 4 | The specified Column is not valid. |
| 5 | The specified Column is not allowed for filtering. This value is returned only from sp_trace_setfilter. |
| 6 | The specified Comparison Operator is not valid. |
| 7 | The specified Logical Operator is not valid. |
| 9 | The specified Trace Handle is not valid. |
| 13 | Out of memory. Returned when there is not enough memory to perform the specified action. |
| 16 | The function is not valid for this trace. |
Remarks
sp_trace_setfilter is a Microsoft® SQL Server™ 2000 stored procedure that performs many of the actions previously executed by extended stored procedures available in earlier versions of SQL Server. Use sp_trace_setfilter instead of the xp_trace_set*filter extended stored procedures to create, apply, remove, or manipulate filters on traces. For more information, see Creating and Managing Templates.
All filters for a particular column must be enabled together in one execution of sp_trace_setfilter. For example, if a user intends to apply two filters on the application name column and one filter on the username column, the user must specify the filters on application name in sequence. SQL Server will return an error if the user attempts to specify a filter on application name in one stored procedure call, followed by a filter on username, then another filter on application name.
Parameters of all SQL Trace stored procedures (sp_trace_xx) are strictly typed. If these parameters are not called with the correct input parameter data types, as specified in the argument description, the stored procedure will return an error.
Permissions
Only members of the sysadmin fixed server role can execute sp_trace_setfilter.
Examples
This example sets three filters on Trace 1. The filters N'SQLT%' and N'MS%' operate on one column (AppName, value 10) using the "LIKE" comparison operator. The filter N'joe' operates on a different column (UserName, value 11) using the "EQUAL" comparison operator.
sp_trace_setfilter 1, 10, 0, 6, N'SQLT%'
sp_trace_setfilter 1, 10, 0, 6, N'MS%'
sp_trace_setfilter 1, 11, 0, 0, N'joe'