Page 112 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes To create a target service:

1. Create a queue to receive messages.

Notes 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’.

Notes 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

7.2.4 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.

106 LOVELY PROFESSIONAL UNIVERSITY