Query Notification Messages

A query notification message contains XML. Each message includes the reason the message was created and the notification message included when the notification was created.

Attributes on the QueryNotification element identify the reason for the message, while the Message element contains the notification ID for the subscription. For example, the XML document shown below is a complete query notification message, reformatted for readability:

<qn:QueryNotification
  xmlns:qn="https://schemas.microsoft.com/SQL/Notifications/QueryNotification"
  Type="change" Source="data" Info="insert">
    <qn:Message>http://mysite.microsoft.com/catalog.aspx?Category=Cars</qn:Message>
</qn:QueryNotification>

This query notification message reports that an INSERT statement changed the data for the subscription with the message, "http://mysite.microsoft.com/catalog.aspx?Category=Cars".

Note

Because the notification is delivered as an XML document, the Database Engine escapes characters that are not valid in an XML document. For example, if the message submitted with the subscription contains the character <, the returned XML escapes this character to &lt;.

The following tables list the values for each attribute. SQL Server only produces certain combinations of attributes. For example, because a Type of change requires that the subscription was created, a Type of change is never combined with a Source of statement.

Query notification messages fall into two main types, distinguished by the value of the Type attribute. To indicate that the results of the query have changed, the Database Engine creates a message of type change. To indicate that the subscription request failed, the Database Engine creates a message of type subscribe. The values of the other two attributes indicate the precise reason that SQL Server created the message.

Change Messages

When a change occurs that may affect the results of a query, SQL Server produces a message of type change. The following table describes change messages:

Source

Info

Description

data

truncate

One or more of the tables referenced in the query was truncated.

data

insert

SQL Server processed an INSERT statement on one or more of the tables referenced in the query.

data

update

SQL Server processed an UPDATE statement on one or more of the tables referenced in the query.

data

delete

SQL Server processed a DELETE statement on one or more of the tables referenced in the query.

timeout

none

The subscription time-out expired. None is not an available result on the client side. None on the server side is translated to either UNKNOWN or ERROR on the client side.

object

drop

One of the underlying objects used by the query was dropped.

object

alter

One of the underlying objects used by the query was modified.

system

restart

SQL Server started.

system

error

An internal error occurred in SQL Server.

system

resource

The notification subscription was removed due to the state of SQL Server, for example, a heavily loaded server.

Subscribe Messages

When SQL Server can not create a subscription, the server immediately produces a message of type subscribe. The following table describes subscribe messages.

Source

Info

Description

statement

query

The command submitted contained a SELECT statement that does not meet the requirements for query notification.

statement

invalid

The command submitted contained a statement that does not support notifications (for example, an INSERT or UPDATE).

statement

previous invalid

A previous command in the transaction contained a statement that does not support notifications (for example, an INSERT or UPDATE)

statement

set options

The connection options were not set appropriately when the command was submitted.

statement

isolation

The isolation level was not valid for query notification (SNAPSHOT isolation level).

statement

query template limit

A table specified in the query has reached the maximum number of internal templates.

See Also

Concepts