Event Notifications

Chapter 15 DBMS-Specific Features

Event Notifications

An event notification sends information about a database or server event to a service broker service. Event notifications are created only by using Transact-SQL statements.

Creating an event notification

You can create an event notification in any of the following ways:

Select Model→EventNotification Objects to access the List of EventNotifications, and click the Add a Row tool
Right-click the model or package in the Browser, and select New→EventNotification

Event notification properties

You can modify an object's properties from its property sheet. To open an event notification property sheet, double-click its Browser entry.

The following extended attributes are available on the Microsoft tab:

Name	Description
Applies on	Specifies the scope of the event notification. You can choose between: database – the notification fires whenever the specified event in the FOR clause occurs anywhere in the instance of SQL Server. server - the notification fires whenever the specified event in the FOR clause occurs in the current database. queue - the notification fires whenever the specified event in the FOR clause occurs in the current queue. Can be specified only if FOR QUEUE_ACTIVATION or FOR BROKER_QUEUE_DISABLED is also specified. Scripting name: AppliesOn
Queue	Specifies the queue to which the event notification applies. Available only if Applies on is set to "queue". Scripting name: Queue
With fan in	Instructs SQL Server to send only one message per event to any specified service for all event notifications that: are created on the same event are created by the same principal (as identified by SID). specify the same service and broker_instance_specifier. specify WITH FAN_IN. Scripting name: WithFanIn
Events	Specifies the name of the event type that causes the event notification to execute. Can be a Transact-SQL DDL, SQL Trace, or Service Broker event type. Scripting name: Events
Service	Specifies the target service that receives the event instance data. SQL Server opens one or more conversations to the target service for the event notification. This service must honor the same SQL Server Events message type and contract that is used to send the message. See Services. Scripting name: Service
Instance	Specifies a service broker instance against which broker_service is resolved. Use 'current database' to specify the service broker instance in the current database. Scripting name: Instance