EventClass translation

I'm outputting a profiler trace to a SQL Server table and I have an
EventClass column with an integer value in it instead of the verbose listing
that is in profiler. Can I join this to a system table to retrieve these
values?
Any links or how-to would be helpful.
Thanks
Scott
There is no system table, but you can create one using the following script.
By giving the table a name that starts with sp_, you can access the table
from any database. You can join EventClass in the trace table with ID in the
sp_EventID_Table table.
------
-- DEMO: Create an EventId table and populate it with definitions of each
ID number
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Scott M" <scott@.nospam.com> wrote in message
news:%23jjPBovOFHA.576@.TK2MSFTNGP15.phx.gbl...
> I'm outputting a profiler trace to a SQL Server table and I have an
> EventClass column with an integer value in it instead of the verbose
> listing that is in profiler. Can I join this to a system table to
> retrieve these values?
> Any links or how-to would be helpful.
> Thanks
> Scott
|||Thanks Much!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O8vL9nwOFHA.3376@.TK2MSFTNGP09.phx.gbl...
> There is no system table, but you can create one using the following
> script. By giving the table a name that starts with sp_, you can access
> the table from any database. You can join EventClass in the trace table
> with ID in the sp_EventID_Table table.
> ------
> -- DEMO: Create an EventId table and populate it with definitions of each
> ID number
> USE master
> GO
> CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
> GO
> SET NOCOUNT ON
> GO
> INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
> INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
> INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
> INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
> INSERT INTO sp_EventID_Table VALUES (14, 'Login')
> INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
> INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
> INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
> INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
> INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
> INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
> INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
> INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
> INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
> INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
> INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
> INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
> INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
> INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
> INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
> INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
> INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
> INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
> INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
> INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
> INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
> INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
> INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
> INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
> INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
> INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
> INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
> INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
> INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
> INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
> INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
> INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
> INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
> INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
> INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
> INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
> INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
> INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
> INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
> INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
> INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
> INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
> INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
> INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
> INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
> INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
> INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
> INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
> INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
> INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
> INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
> INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
> INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
> INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
> INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
> INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
> INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
> INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
> INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
> INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
> INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
> INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
> INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
> INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
> INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
> INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
> INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
> INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
> INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
> INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
> INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
> INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
> INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
> INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
> INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
> INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
> INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
> INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
> INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
> INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server
> Role')
> INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
> INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
> INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
> INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
> INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
> INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
> INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
> INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
> INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
> INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
> Permission')
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Scott M" <scott@.nospam.com> wrote in message
> news:%23jjPBovOFHA.576@.TK2MSFTNGP15.phx.gbl...
>