Creating a Query for Notification

SET Option Settings

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

• ANSI_NULLS ON

• ANSI_PADDING ON

• ANSI_WARNINGS ON

• CONCAT_NULL_YIELDS_NULL ON

• QUOTED_IDENTIFIER ON

• NUMERIC_ROUNDABORT OFF

• ARITHABORT ON

The statement must not run under READ_UNCOMMITTED or SNAPSHOT isolation levels.

If these options or the isolation level is not set appropriately, the notification is fired immediately after the SELECT statement is executed. When a notification is active, the connection that issues a command that causes a notification to fire must also have the SET options set as shown. Otherwise, the command fails with a Transact-SQL error.

When the statement is contained in a stored procedure, the ANSI_NULLS option and the QUOTED_IDENTIFIER option must be set when the stored procedure is created. For more information, see SET ANSI_NULLS (Transact-SQL) and SET QUOTED_IDENTIFIER (Transact-SQL).

Statements for Notification

In general, you can request notification for any query that can be used to create an indexed view. You can set up notifications for the following statements:

• SELECT

  For requirements and limitations specific to SELECT, see "Supported SELECT Statements" below. For more information on the SELECT statement, see SELECT (Transact-SQL).

• EXECUTE

  In this case, 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. For more information on the EXECUTE statement, see EXECUTE (Transact-SQL).

When a command that registers a notification contains more than one statement, the Database Engine creates a notification for each statement in the batch.

Supported SELECT Statements

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 statement must not reference tables with computed columns.

• 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 UNION, 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).

• The statement can not specify READPAST locking hint.

• The statement must not reference any Service Broker QUEUE.

• The statement must not reference synonyms.

• The statement must not have comparison or expression based on double/real data types.

• The statement must not use the TOP expression.

Batches and Stored Procedures

If a subscription request is made for a batch or stored procedure, a separate subscription request is made for each statement executed within the batch or stored procedure.

EXECUTE statements will not register a notification, but will flow the notification request to the executed command. If it is a batch, the context will be applied to the executed statements and the same rules described above apply.

Duplicate Subscriptions

Submitting a duplicate of an active subscription causes the existing subscription to be renewed using the new specified time-out value. A duplicate subscription is one that meets the following conditions:

• The query is submitted by the same user under the same database context.

• The same template, parameter values, notification ID, and delivery location are used.

This means that if a notification is requested for identical queries, only one notification is sent. This applies to a query duplicated in a batch, or to a query in a stored procedure that is called multiple times.

See Also

Reference

Concepts