Implementing Event Notifications
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. For more information, see Dialog Security for Event Notifications.
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:
Create a queue to receive messages.
Note
The queue receives the following message type: https://schemas.microsoft.com/SQL/Notifications/QueryNotification.
Create a service on the queue that references the event notifications contract.
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. For more information, see Service Broker Routing.
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
(
[https://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
CREATE ROUTE NotifyRoute
WITH SERVICE_NAME = 'NotifyService',
ADDRESS = 'LOCAL';
GO
For more information about how to create Service Broker target services, see Benefits of Programming with Service Broker.
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' ;
Warning
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
To drop an event notification