.NET Framework Developer's Guide
Special Considerations Using Query Notifications (ADO.NET)

Applications that use query notification features need to take into account the following special considerations.

Consideration

Description

Service Account for SQL Server

An application will not receive notifications from an instance of SQL Server that uses the Local System account as the service account. For more information, see "Setting Up Windows Service Accounts" in SQL Server Books Online.

Receiving Notifications

Notifications cannot be received on computers running Windows 95 or Windows 98.

Query Notifications and Transactions

If multiple modifications are made inside of a transaction affecting a set of data with a registered notification request, only a single notification event will be sent.

Rapid Updates and Query Notifications

An application that uses query notifications must take into consideration cases where a notification occurs immediately. When data is changed on the server, a notification message will be sent to the appropriate Service Broker queue. Applications need to reregister to receive additional notifications. Therefore, if a data set is updated quickly by multiple applications, an application could receive a notification, retrieve the data, and then get another update notification almost immediately after the cache has been refreshed. Applications that use query notifications must be written to take this case into account. If an application uses data that is constantly updated, a different strategy for caching data may be more appropriate.

SET Option Settings

When a SELECT statement is executed under a notification request, the connection that submits the request must have the following options set:

  • ANSI_NULLS ON

  • ANSI_PADDING ON

  • ANSI_WARNINGS ON

  • CONCAT_NULL_YIELDS_NULL ON

  • QUOTED_IDENTIFIER ON

  • NUMERIC_ROUNDABORT OFF

  • ARITHABORT ON
Writing Notification Queries

You can set up notifications for SELECT and EXECUTE statements. When using an EXECUTE statement, SQL Server registers a notification for the command executed rather than the EXECUTE statement itself. The command must meet the requirements and limitations for a SELECT statement. When a command that registers a notification contains more than one statement, the Database Engine creates a notification for each statement in the batch.

Query notifications are supported for SELECT statements that meet the following requirements:

  • The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.

  • The statement may not use the asterisk (*) or table_name.* syntax to specify columns.

  • The statement may not use unnamed columns or duplicate column names.

  • The statement must reference a base table.

  • The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.

  • A projected column in the SELECT statement that is used as a simple expression must not appear more than once.

  • The statement must not include PIVOT or UNPIVOT operators.

  • The statement must not include the INTERSECT or EXCEPT operators.

  • The statement must not reference a view.

  • The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.

  • The statement must not reference server global variables (@@variable_name).

  • The statement must not reference derived tables, temporary tables, or table variables.

  • The statement must not reference tables or views from other databases or servers.

  • The statement must not contain subqueries, outer joins, or self-joins.

  • The statement must not reference the large object types: text, ntext, and image.

  • The statement must not use the CONTAINS or FREETEXT full-text predicates.

  • The statement must not use rowset functions, including OPENROWSET and OPENQUERY.

  • The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.

  • The statement must not use any nondeterministic functions, including ranking and windowing functions.

  • The statement must not contain user-defined aggregates.

  • The statement must not reference system tables or views, including catalog views and dynamic management views.

  • The statement must not include FOR BROWSE information.

  • The statement must not reference a queue.

  • The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

For more information, see "Creating a Query for Notification" in SQL Server Books Online.
See Also

Concepts

Query Notifications in SQL Server (ADO.NET)
Tags :


Page view tracker