Creating Query Notification Subscriptions

Subscribing to a notification is a matter of creating a command, attaching a notification request to the command, and then executing the command. Query notification subscriptions must be created from a database access interface. Transact-SQL does not provide a mechanism for creating query notification subscriptions.

The Database Engine immediately registers the notification for the Transact-SQL statement in the command. For each statement that does not meet the requirements for a query notification, the Database Engine immediately creates a notification. For those statements that meet the requirements, a subscription is created. Care should be taken to avoid sending a command to the server that contains both statements that meet the requirements and statements that do not meet both the requirements.

Each query notification subscription produces one notification event. After the notification occurs, SQL Server removes the notification subscription. Therefore, an application that needs to be notified again must request a new notification with the command that retrieves the changed data.

Requesting a query notification is not transactional. When you request a notification on a command, the Database Engine registers the notification whether or not the transaction that contains the command commits or rolls back.

Subscriptions for query notifications are stored in the database where the query is executed. The dynamic management view sys.dm_qn_subscriptions presents information on the query notification subscriptions that are currently active. The catalog view sys.internal_tables shows the space used by query notification subscriptions that are currently active.

Choosing a Notification Subscription Strategy Using the SqlClient Managed Provider

There are two ways to request a notification subscription. A dependency provides a high-level interface to query notifications. An application simply creates an instance of a dependency class (for example, SqlDependency in ADO.NET), registers a handler to receive the notification event, and adds the dependency object to a query command. The dependency object calls the handler when the notification occurs.

A notification request provides a lower-level, more flexible interface to query notifications. Like a dependency, an application creates a notification request (for example, an instance of SqlNotificationRequest in ADO.NET) and attaches the notification request to a query command. Unlike a dependency, a notification request simply requests a notification subscription for the query. The application itself must retrieve notification messages from the database, process the messages, and react accordingly. This allows more flexibility in application design, but requires slightly more effort. A notification request is suitable for applications that require flexibility in handling notifications. For example, an application that runs on a scheduled basis cannot use a dependency, since the dependency object is destroyed when the application exits. With a notification request, however, the application can check the queue on startup, and refresh the local data cache if necessary.

Applications use a dependency when the application that registers the notification subscription is also the application that will process the notification, when the application will remain running continuously, and when connectivity to the database is available while the application is running. For applications that meet these requirements, dependencies provide an easy way to use query notifications. Applications use a notification request when the application cannot use a dependency. In this case, the application developer must design a strategy to receive and process messages that makes sense for the application.

Using SOAP for Notifications

Native HTTP SOAP support includes support for Query Notifications. A SOAP request that includes the <notificationRequest> header creates a notification subscription for a query. As with SqlNotificationRequest, the requestor must retrieve notification messages from the database, process the messages, and react accordingly. This approach is well-suited for Web services applications, since it does not require a continuous connection to SQL Server.

Using SQL Server Native Client for Notifications

Applications that use the SQL Server Native Client OLE DB provider or ODBC driver can also submit notification requests. For information on using query notifications with SQL Server Native Client applications, see Working with Query Notifications.

See Also

Concepts

Understanding When Query Notifications Occur
Using SqlDependency to Subscribe to Query Notifications
Using SqlNotificationRequest to Subscribe to Query Notifications

Help and Information

Getting SQL Server 2005 Assistance