Muokkaa

Jaa

sp_trace_generateevent (Transact-SQL)

  • Artikkeli

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

Syntax

sp_trace_generateevent
    [ @eventid = ] eventid
    [ , [ @userinfo = ] N'userinfo' ]
    [ , [ @userdata = ] userdata ]
[ ; ]

Arguments

[ @eventid = ] eventid

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.

[ @userinfo = ] 'userinfo'

The optional user-defined string. @userinfo is nvarchar(128), with a default of NULL.

[ @userdata = ] userdata

The optional user-defined data for the event. @userdata is varbinary(8000), with a default of 0x.

Return code values

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.

Remarks

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.

Permissions

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.

Examples

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"}]

Related content