I want to track the usage of extended stored procedures within a particular application. The Object Type data column sets a filter within SQL Server Profiler. And the Help file entry for the Object Type data column filter says that the value in the column represents "the type of the object involved in the event," which I assume corresponds to the Object Type column in sysobjects. So, a value of 'X' (the extended stored procedure's value for the sysobjects.type column) should represent an extended stored procedure. However, if I enter 'X' into Profiler's Object Type filter, I receive the error, This filter accepts numeric entries only. What value do I need to use to make the filter track my extended stored procedures?
Information about the correct values to use in an Object Type filter within Profiler is poorly documented and to the best of my knowledge doesn't appear anywhere in SQL Server Books Online (BOL). However, a colleague at Microsoft gave me the correct values for Profiler filters that are based on the Object Type column. The correct value to filter on for an extended stored procedure is 19. Table 1 shows the full list of values for Profiler filters. The list will come in handy when you try to build complicated filters in Profiler.
Unfortunately, knowing the value to filter on doesn't solve your problem. Extended stored procedures don't fire an SP:Completed event, which tracks the completion of a stored procedure. They fire an SQL:StmtCompleted event, which tracks the completion of an SQL statement within a T-SQL batch. However, the SQL:StmtCompleted event doesn't track the Object Type data column, which means that setting up a filter on this event (where Object Type = 19) will yield no data.
However, I came up with a workaround that might meet your needs. I noticed that Profiler's Security Audit:Audit Object Permission event does track the Object Type data column and that the event fires for an extended stored procedure. So, the short and direct technique for tracking extended stored procedure execution requires you to place a filter on the Security Audit:Audit Object Permission event class where Object Type = 19.