sys.dm_qn_subscriptions

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

Relationship Cardinalities

From	To	On	Type
sys.dm_qn_subscriptions	sys.databases	database_id	Many-to-one
sys.dm_qn_subscriptions	sys.internal_tables	object_id	Many-to-one

Permissions

Requires VIEW SERVER STATE permission on server.

Note

If the user does not have VIEW SERVER STATE permission, this view returns information about subscriptions owned by current user.

Examples

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

See Also

Reference

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

Other Resources

Using Query Notifications
Internal Tables

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added the Relationship Cardinalities and Examples sections.