Implement Event Notifications

Applies to: SQL Server

To implement an event notification, you must first create a target service to receive event notifications, and then create the event notification.

Important

Service Broker dialog security should be configured for event notifications that send messages to a service broker on a remote server. Dialog security must be configured manually according to the full security model.

Creating the Target Service

You do not have to create a Service Broker-initiating service because Service Broker includes the following specific message type and contract for event notifications:

https://schemas.microsoft.com/SQL/Notifications/PostEventNotification  

The target service that receives event notifications must honor this preexisting contract.

To create a target service:

1. Create a queue to receive messages.

   Note

   The queue receives the following message type: http://schemas.microsoft.com/SQL/Notifications/QueryNotification.

2. Create a service on the queue that references the event notifications contract.

3. Create a route on the service to define the address to which Service Broker sends messages for the service. For event notifications that target a service in the same database, specify ADDRESS = 'LOCAL'.

   Note

   Service Broker routing determines the service that receives the notification messages. If the event notification targets a service on a remote server, both the source server and the target server must have routes defined on them to make sure that two-way communication occurs.

The following example creates a queue, a service on the queue, and a route on the service to handle messages from the event notification contract.

CREATE QUEUE NotifyQueue ;  
GO  
CREATE SERVICE NotifyService  
ON QUEUE NotifyQueue  
(  
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]  
);  
GO  
CREATE ROUTE NotifyRoute  
WITH SERVICE_NAME = 'NotifyService',  
ADDRESS = 'LOCAL';  
GO  

Creating the Event Notification

Event notifications are created by using the Transact-SQL CREATE EVENT NOTIFICATION statement, and are dropped by using the DROP EVENT NOTIFICATION STATEMENT. To modify an event notification, you must drop and re-create the event notification.

The following example creates the event notification CreateDatabaseNotification. This notification sends a message about any CREATE_DATABASE event that occurs on the server to the NotifyService service that was previously created.

CREATE EVENT NOTIFICATION CreateDatabaseNotification  
ON SERVER  
FOR CREATE_DATABASE  
TO SERVICE 'NotifyService', '8140a771-3c4b-4479-8ac0-81008ab17984' ;  

Caution

Event notifications recognize CREATE_SCHEMA events and the <schema_element> definitions of CREATE SCHEMA statements as separate events. For example, an event notification is created on both the CREATE_SCHEMA and CREATE_TABLE events, and you run the following batch.

CREATE SCHEMA s

CREATE TABLE t1 (col1 int)

In this case, the event notification is raised two times: Onne time when the CREATE_SCHEMA event occurs, and again when the CREATE_TABLE event occurs. We recommend that you either avoid creating event notifications on both the CREATE_SCHEMA events and the <schema_element> texts of any corresponding CREATE SCHEMA definitions, or build logic into your application to avoid capturing unwanted event data.

To create an event notification

• CREATE EVENT NOTIFICATION (Transact-SQL)

To drop an event notification

• DROP EVENT NOTIFICATION (Transact-SQL)

See Also

Get Information About Event Notifications
EVENTDATA (Transact-SQL)