Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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://adventure-works.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://adventure-works.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 <.
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. |
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. |