SQL Server Profiler is a well known tool for tracing different activity that happens between your server and the clients connected to it.
But very few people know that you can have custom events and trace them in the SQL Server Profiler. They can be found under User configurable event group:
We can have up to 10 custom events.
Firing these custom events is done by executing sp_trace_generateevent stored procedure which takes event id as the first input parameter. These event ID's span from 82 (UserConfigurable:0) to 91 (UserConfigurable:9)
A simple code example to demonstrate this:
USE AdventureWorks
GO
CREATE PROCEDURE spDoStuff
@AddressID int
AS
UPDATE Person.Address
SET ModifiedDate = GETDATE()
WHERE AddressID = @AddressID
IF @@ROWCOUNT = 0
BEGIN
DECLARE @msg NVARCHAR(128)
SELECT @msg = N'No data updated. Rowcount 0. AddressId = ' + CAST(@AddressID AS NVARCHAR(10))
EXEC sp_trace_generateevent 82, @msg
END
SELECT * FROM Person.Address WHERE AddressID = @AddressID
GO
-- UPDATES OK
EXEC spDoStuff 1
GO
-- this id doesn't exist. trace event is executed as shown in the below picture
EXEC spDoStuff -22
GO
DROP PROCEDURE spDoStuff
The UserConfigurable:0 event was successfully fired as is shown in the profiler trace.
These custom events are highly underused and there isn't even much talk about them at all. That is a shame because they can be a very helpful addition to your debugging or general execution tracing. By using them you can easily narrow the profiler trace and with that the load on the server over long periods of time making them ideal for finding seldom occurring problems in production environment.
There is one downside to them though. Running sp_trace_generateevent requires ALTER TRACE permissions to run. Most likely the user running the stored procedure doesn't have that permission. Unless you're running under SA of course :)
A workaround for this is to putting it simply create a wrapper stored procedure around the sp_trace_generateevent stored procedure, sign the wrapper with a certificate and allow public access to it.
