Export (0) Print
Expand All

sys.dm_qn_subscriptions

Updated: 12 December 2006

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.

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.
ms187793.note(en-US,SQL.90).gifNote:

status

int

Indicates the status of the subscription.

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

Requires VIEW SERVER STATE permission on server.

ms187793.note(en-US,SQL.90).gifNote:
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

Release History

12 December 2006

New content:
  • Added the Relationship Cardinalities and Examples sections.
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft