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.
|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.|
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:
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:
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 ( [http://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 Introduction to Service Broker Programming.
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' ;
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.
To create an event notification
To drop an event notification
5 December 2005