Returns information about the active query notifications subscriptions in the server. You can use this view to check for active subscriptions in the server or a specified database, or to check for a specified server principal.
|Column name||Data type||Description|
|id||int||ID of a subscription.|
|database_id||int||ID of the database in which the notification query was executed. This database stores information related to this subscription.|
|sid||varbinary(85)||Security ID of the server principal that created and owns this subscription.|
|object_id||int||ID of the internal table that stores information about subscription parameters.|
|created||datetime||Date and time that the subscription was created.|
|timeout||int||Time-out for the subscription in seconds. The notification will be flagged to fire after this time has elapsed.
Note: The actual firing time may be greater than the specified time-out. However, if a change that invalidates the subscription occurs after the specified time-out, but before the subscription is fired, SQL Server ensures that firing occurs at the time that the change was made.
|status||int||Indicates the status of the subscription. See the table under remarks for the list of codes.|
The status code of 0 indicates an undefined status.
The following status codes indicate that a subscription fired because of a change:
|65798||Subscription fired because data changed||Subscription triggered by insert|
|65799||Subscription fired because data changed||Delete|
|65800||Subscription fired because data changed||Update|
|65801||Subscription fired because data changed||Merge|
|65802||Subscription fired because data changed||Truncate table|
|66048||Subscription fired because timeout expired||Undefined info mode|
|66315||Subscription fired because object changed||object or user was dropped|
|66316||Subscription fired because object changed||object was altered|
|66565||Subscription fired because database was detached or dropped||server or db restarted|
|66571||Subscription fired because database was detached or dropped||object or user was dropped|
|66572||Subscription fired because database was detached or dropped||object was altered|
|67341||subscription was triggered because of lack od resources on the server||subscription was triggered because of lack od resources on the server|
The following status codes indicate that a subscription failed to be created:
|132609||Subscription creation failed because the statement is not supported||Query is too complex|
|132610||Subscription creation failed because the statement is not supported||Invalid statement for subscription|
|132611||Subscription creation failed because the statement is not supported||Invalid set options for subscription|
|132612||Subscription creation failed because the statement is not supported||Invalid isolation level|
|132622||Subscription creation failed because the statement is not supported||used internally|
|132623||Subscription creation failed because the statement is not supported||over the template limit per table|
The following status codes are used internally and are classed as check kill and init modes:
|198656||Used internally: check kill and init modes||Undefined info mode|
|198928||Subscription was destroyed||Subscription fired because db was attached|
|198929||Subscription was destroyed||Subscription fired because user was dropped|
|198930||Subscription was destroyed||Subscription was dropped because of a resubscription|
|198931||Subscription was destroyed||subscription was killed|
|199168||Subscription is active||Undefined info mode|
|199424||Subscription initialized but not yet active||Undefined info mode|
Requires VIEW SERVER STATE permission on server.
If the user does not have VIEW SERVER STATE permission, this view returns information about subscriptions owned by current user.
A. Return active query notification subscriptions for the current user
The following example returns the active query notification subscriptions of the current user. If the user has VIEW SERVER STATE permissions, all active subscriptions in the server are returned.
SELECT id, database_id, sid, object_id, created, timeout, status FROM sys.dm_qn_subscriptions; GO
B. Returning active query notification subscriptions for a specified user
The following example returns the active query notification subscriptions subscribed by login
SELECT id, database_id, sid, object_id, created, timeout, status FROM sys.dm_qn_subscriptions WHERE sid = SUSER_SID('Ruth0'); GO
C. Returning internal table metadata for query notification subscriptions
The following example returns the internal table metadata for query notification subscriptions.
SELECT qn.id AS query_subscription_id ,it.name AS internal_table_name ,it.object_id AS internal_table_id FROM sys.internal_tables AS it JOIN sys.dm_qn_subscriptions AS qn ON it.object_id = qn.object_id WHERE it.internal_type_desc = 'QUERY_NOTIFICATION'; GO