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)