sp_trace_generateevent (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
Creates a user-defined event. The event can be collected using SQL Trace or Extended Events.
Note
This stored procedure is not deprecated. All other SQL Trace related stored procedures are deprecated.
Transact-SQL syntax conventions
sp_trace_generateevent
[ @eventid = ] eventid
[ , [ @userinfo = ] N'userinfo' ]
[ , [ @userdata = ] userdata ]
[ ; ]
The ID of the event to fire. @eventid is int, with no default. The ID must be in the range from 82
through 91
inclusive. This range represents user-defined events. In SQL Trace, use sp_trace_setevent to add an event with this ID to a trace to capture events with the same ID fired from this stored procedure.
The optional user-defined string. @userinfo is nvarchar(128), with a default of NULL
.
The optional user-defined data for the event. @userdata is varbinary(8000), with a default of 0x
.
The following table describes the return code values that you could get, following the completion of the stored procedure.
Return code | Description |
---|---|
0 |
No error. |
1 |
Unknown error. |
3 |
The specified event isn't valid. The event might not exist or it isn't an appropriate one for the stored procedure. |
13 |
Out of memory. Returned when there isn't enough memory to perform the specified action. |
To capture the events fired by this stored procedure using Extended Events, add the user_info
event to an event session. For more information, see CREATE EVENT SESSION. The user_info
event is fired for any user-defined event ID value passed to the @eventid
parameter.
Only ID numbers of user-defined events can be used with sp_trace_generateevent
. An error is raised if any other event ID number is used.
The parameters of this stored procedure are strictly typed. If the data type of the value passed to a parameter doesn't match the parameter data type specified in its description, the stored procedure returns an error.
sp_trace_generateevent
performs many of the actions previously executed by the xp_trace_*
extended stored procedures. Use sp_trace_generateevent
instead of xp_trace_generate_event
.
In SQL Server and in Azure SQL Managed Instance, requires the ALTER TRACE
permission. In Azure SQL Database, requires membership in the public
database role.
The following example fires a user-defined event when a row is inserted into a table. The event contains the data inserted into the table.
To collect the event fired by this example, create an extended event session and include the user_info
event, or create a SQL trace and include the UserConfigurable:0
event.
-- Create a table
DROP TABLE IF EXISTS dbo.user_defined_event_example;
CREATE TABLE dbo.user_defined_event_example
(
Id int IDENTITY(1,1) PRIMARY KEY,
Data nvarchar(60) NOT NULL
);
DROP TRIGGER IF EXISTS fire_user_defined_event;
GO
-- Create an insert trigger on the table
CREATE TRIGGER fire_user_defined_event ON dbo.user_defined_event_example
FOR INSERT
AS
DECLARE @EventData varbinary(8000);
-- Convert inserted rows to JSON and cast it as a binary value
SELECT @EventData = CAST((
SELECT Id, Data
FROM inserted
FOR JSON AUTO
) AS varbinary(8000));
-- Fire the event with the payload carrying inserted rows as JSON
EXEC dbo.sp_trace_generateevent
@eventid = 82,
@userinfo = N'Inserted rows into dbo.user_defined_event_example',
@userdata = @EventData;
GO
-- Insert a row into the table. The trigger fires the event.
INSERT INTO dbo.user_defined_event_example (Data)
VALUES (N'Example data');
-- Copy the binary payload from the event and cast it to a string with the JSON value
SELECT CAST(0x5B007B0022004900640022003A0031002C002200440061007400610022003A0022004500780061006D0070006C0065002000640061007400610022007D005D00 AS nvarchar(max));
-- This returns: [{"Id":1,"Data":"Example data"}]