TechNet
Export (0) Print
Expand All

sys.dm_qn_subscriptions (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 nameData typeDescription
idintID of a subscription.
database_idintID of the database in which the notification query was executed. This database stores information related to this subscription.
sidvarbinary(85)Security ID of the server principal that created and owns this subscription.
object_idintID of the internal table that stores information about subscription parameters.
createddatetimeDate and time that the subscription was created.
timeoutintTime-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.
statusintIndicates the status of the subscription. See the table under remarks for the list of codes.
FromToOnType
sys.dm_qn_subscriptionssys.databasesdatabase_idMany-to-one
sys.dm_qn_subscriptionssys.internal_tablesobject_idMany-to-one

The status code of 0 indicates an undefined status.

The following status codes indicate that a subscription fired because of a change:

CodeMinor statusInfo
65798Subscription fired because data changedSubscription triggered by insert
65799Subscription fired because data changedDelete
65800Subscription fired because data changedUpdate
65801Subscription fired because data changedMerge
65802Subscription fired because data changedTruncate table
66048Subscription fired because timeout expiredUndefined info mode
66315Subscription fired because object changedobject or user was dropped
66316Subscription fired because object changedobject was altered
66565Subscription fired because database was detached or droppedserver or db restarted
66571Subscription fired because database was detached or droppedobject or user was dropped
66572Subscription fired because database was detached or droppedobject was altered
67341subscription was triggered because of lack od resources on the serversubscription was triggered because of lack od resources on the server

The following status codes indicate that a subscription failed to be created:

CodeMinor statusInfo
132609Subscription creation failed because the statement is not supportedQuery is too complex
132610Subscription creation failed because the statement is not supportedInvalid statement for subscription
132611Subscription creation failed because the statement is not supportedInvalid set options for subscription
132612Subscription creation failed because the statement is not supportedInvalid isolation level
132622Subscription creation failed because the statement is not supportedused internally
132623Subscription creation failed because the statement is not supportedover the template limit per table

The following status codes are used internally and are classed as check kill and init modes:

CodeMinor statusInfo
198656Used internally: check kill and init modesUndefined info mode
198928Subscription was destroyedSubscription fired because db was attached
198929Subscription was destroyedSubscription fired because user was dropped
198930Subscription was destroyedSubscription was dropped because of a resubscription
198931Subscription was destroyedsubscription was killed
199168Subscription is activeUndefined info mode
199424Subscription initialized but not yet activeUndefined info mode

Requires VIEW SERVER STATE permission on server.

System_CAPS_ICON_note.jpg Note


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 Ruth0.

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  

Dynamic Management Views and Functions (Transact-SQL)
Query Notifications Related Dynamic Management Views (Transact-SQL)
KILL QUERY NOTIFICATION SUBSCRIPTION (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft