Need sp_trace_setfilter xref list of @columnid parameters
-
Where in BOL can I find the cross-reference list of @columnID's used in the sp_trace_setfilter parameters.
Please be specific as I've searched BOL and can't locate them. Specifically, I want to filter on database ID w/in the sp_trace_setfilter statement. (looks like 10=AppName 11=UserName in the following BOL example):
BOL sp_trace_setfilter example:
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'
BT -
-
The link seems not to work, any alternative to the link above.
-
It is on the MS site but I already assembled:
ID Name Description 1 TextData Text value dependent on the event class that is captured in the trace. 2 BinaryData Binary value dependent on the event class captured in the trace. 3 DatabaseID ID of the database specified by the USE database statement, or the default database if no USE database statement is issued for a given connection. 4 TransactionID System-assigned ID of the transaction. 6 NTUserName Microsoft Windows NT® user name. 7 NTDomainName Windows NT domain to which the user belongs. 8 ClientHostName Name of the client computer that originated the request. 9 ClientProcessID ID assigned by the client computer to the process in which the client application is running. 10 ApplicationName Name of the client application that created the connection to an instance of SQL Server. This column is populated with the values passed by the application rather than the displayed name of the program. 11 SQLSecurityLoginName SQL Server login name of the client. 12 SPID Server Process ID assigned by SQL Server to the process associated with the client. 13 Duration Amount of elapsed time (in milliseconds) taken by the event. This data column is not populated by the Hash Warning event. 14 StartTime Time at which the event started, when available. 15 EndTime Time at which the event ended. This column is not populated for starting event classes, such as SQL:BatchStarting or SP:Starting. It is also not populated by the Hash Warning event. 16 Reads Number of logical disk reads performed by the server on behalf of the event. This column is not populated by the Lock:Released event. 17 Writes Number of physical disk writes performed by the server on behalf of the event. 18 CPU Amount of CPU time (in milliseconds) used by the event. 19 Permissions Represents the bitmap of permissions; used by Security Auditing. 20 Severity Severity level of an exception. 21 EventSubClass Type of event subclass. This data column is not populated for all event classes. 22 ObjectID System-assigned ID of the object. 23 Success Success of the permissions usage attempt; used for auditing. 24 IndexID ID for the index on the object affected by the event. To determine the index ID for an object, use the indid column of the sysindexes system table. 25 IntegerData Integer value dependent on the event class captured in the trace. 26 ServerName Name of the instance of SQL Server (either servername or servername\instancename) being traced. 27 EventClass Type of event class being recorded. 28 ObjectType Type of object (such as table, function, or stored procedure). 29 NestLevel Nest Level 30 State Server state, in case of an error. 31 Error Error number. 32 Mode Lock mode of the lock acquired. This column is not populated by the Lock:Released event. 33 Handle Handle of the object referenced in the event. 34 ObjectName Name of object accessed. 35 DatabaseName Name of the database specified in the USE database statement. 36 Filename Logical name of the file name modified. 37 ObjectOwner Owner ID of the object referenced. 38 TargetRoleName Name of the database or server-wide role targeted by a statement. 39 TargetUserName User name of the target of some action. 40 DatabaseUserName SQL Server database username of the client. 41 LoginSID Security identification number (SID) of the logged-in user. 42 TargetLoginName Login name of the target of some action. 43 TargetLoginSID SID of the login that is the target of some action. 44 ColumnPermissionsSet Column-level permissions status; used by Security Auditing. SQL = Scarcely Qualifies as a Language
-
Here are the values for eventclass
10 RPC:Completed 11 RPC:Starting 12 SQL:BatchCompleted 13 SQL:BatchStarting 14 Login 15 Logout 16 Attention 17 ExistingConnection 18 ServiceControl 19 DTCTransaction 20 Login Failed 21 EventLog 22 ErrorLog 23 Lock:Released 24 Lock:Acquired 25 Lock 
eadlock26 Lock:Cancel 27 Lock:Timeout 28 DOP Event 33 Exception 34 SP:CacheMiss 35 SP:CacheInsert 36 SP:CacheRemove 37 SP:Recompile 38 SP:CacheHit 39 SP:ExecContextHit 40 SQL:StmtStarting 41 SQL:StmtCompleted 42 SP:Starting 43 SP:Completed 44 SP:StmtStarting 45 SP:StmtCompleted 46 Object:Created 47 Object eleted50 SQL Transaction 51 Scan:Started 52 Scan:Stopped 53 CursorOpen 54 Transaction Log 55 Hash Warning 58 Auto Update Stats 59 Lock eadlock Chain60 Lock:Escalation 61 OLE DB Errors 67 Execution Warnings 68 Execution Plan 69 Sort Warnings 70 CursorPrepare 71 Prepare SQL 72 Exec Prepared SQL 73 Unprepare SQL 74 CursorExecute 75 CursorRecompile 76 CursorImplicitConversion 77 CursorUnprepare 78 CursorClose 79 Missing Column Statistics 80 Missing Join Predicate 81 Server Memory Change 92 Data File Auto Grow 93 Log File Auto Grow 94 Data File Auto Shrink 95 Log File Auto Shrink 96 Show Plan Text 97 Show Plan ALL 98 Show Plan Statistics 100 RPC Output Parameter 102 Audit Statement GDR 103 Audit Object GDR 104 Audit Add/Drop Login 105 Audit Login GDR 106 Audit Login Change Property 107 Audit Login Change Password 108 Audit Add Login to Server Role 109 Audit Add DB User 110 Audit Add Member to DB Role 111 Audit Add/Drop Role 112 App Role Pass Change 113 Audit Statement Permission 114 Audit Object Permission 115 Audit Backup/Restore 116 Audit DBCC 117 Audit Change Audit 118 Audit Object Derived Permission SQL = Scarcely Qualifies as a Language
-
For column 22 ObjectType, here are the values:
1 Index 2 Database 3 User object 4 CHECK constraint 5 Default or DEFAULT constraint 6 FOREIGN KEY constraint 7 PRIMARY KEY constraint 8 Stored procedure 9 User-defined function (UDF) 10 Rule 11 Replication filter stored procedure 12 System table 13 Trigger 14 Inline function 15 Table valued UDF 16 UNIQUE constraint 17 User table 18 View 19 Extended stored procedure 20 Ad-hoc query 21 Prepared query 22 Statistics SQL = Scarcely Qualifies as a Language
-
Thanks
-
How would I code the sp_trace_setfilter statement to filter on DATABASE ID = 5 ??
BT -
This will place a filter aon the database id.
declare @intfilter int
set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
One tip, if your not sure about the paramters and values just define it in Profeiler and when use the option "Script Trace".
Markus
[font="Verdana"]Markus Bohse[/font]
-
THANK YOU! you guys are the best.
FYI, thought i would mention, i DID use the scripting option (2005) and for some crazy reason, it DID NOT hold onto the database ID filter. it did not set it.
so i came out here frantically looking for the "manual" way to do it.
=)
-
Markus,
I have noticed that when I script a trace, for some reason, it doesn't pick up all of the column filters.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply