NSScheduledSubscriptionDetails (Transact-SQL)

Returns detailed information about the subscriptions of a subscription class in a Microsoft SQL Server Notification Services application that are scheduled to be evaluated within a given time period. This stored procedure only works with subscription classes that have scheduled subscription rules.

Syntax

[ schema_name . ] NSScheduledSubscriptionDetails
    [ @SubscriptionClassName = ] 'sub_class_name',
    [ @StartTime = ] 'start_time',
    [ @EndTime = ] 'end_time'

Arguments

  • [ @SubscriptionClassName = ] 'sub_class_name'
    Is the name of a subscription class. sub_class_name is nvarchar(255) and has no default value.
  • [ @StartTime = ] 'start_time'
    Is the start date and time, in UTC (Coordinated Universal Time or Greenwich Mean Time), for the time period of interest. start_time is datetime and has no default value.
  • [ @EndTime = ] 'end_time'
    Is the end date and time, in UTC, for the time period of interest. end_time is datetime and has no default value. You cannot specify an end_time value greater than 24 hours past the start_time value.

Return Code Values

None

Result Sets

The NSScheduledSubscriptionDetails result set is ordered by ScheduleTime.

Column Name Data Type Description

SubscriptionId

bigint

Unique ID of the scheduled subscription.

SubscriberId

nvarchar(255)

Unique ID of the subscriber who owns the subscription.

CreationDate

datetime

Date and time, in UTC, at which the subscription was created.

UpdateDate

datetime

Date and time, in UTC, at which the subscription was last updated.

Enabled

bit

Indicates whether the subscription is currently enabled or disabled. The value 1 indicates that the subscription is enabled.

ScheduleTime

datetime

Date and time, in UTC, at which the subscription is scheduled for processing.

Subscription_Class_Field_Name

application defined

The name of a subscription class field, as defined in the application definition file (ADF). Each field in the subscription class is represented by a column in the result set.

Remarks

Notification Services creates the NSScheduledSubscriptionDetails stored procedure in the application database when you create the instance. When you update the application, Notification Services recompiles the stored procedure.

This stored procedure is in the application's schema, which is specified by the SchemaName element of the application definition file (ADF). If no schema name is provided, the default schema is dbo.

To determine the current UTC date and time, run SELECT GETUTCDATE() in SQL Server Management Studio The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.

Permissions

Execute permissions default to members of the NSAnalysis and db_owner database roles and members of the sysadmin fixed server role.

Examples

The following example shows how to produce the scheduled subscription report for the PortfolioSubscriptions subscription class of the Stock application. The report contains information about subscriptions that are scheduled to run on 23 May, 2004, using the UTC time values.

The application uses the default SchemaName settings, which places all application objects in the dbo schema.

EXEC dbo.NSScheduledSubscriptionDetails 
    @SubscriptionClassName = N'PortfolioSubscriptions', 
    @StartTime = '2004-05-23 00:00:00', 
    @EndTime = '2004-05-23 23:59:59';

See Also

Reference

Notification Services Stored Procedures (Transact-SQL)

Other Resources

Notification Services Performance Reports
SchemaName Element (ADF)

Help and Information

Getting SQL Server 2005 Assistance