NSScheduledSubscriptionList (Transact-SQL)
Produces the scheduled subscription list report for a Microsoft SQL Server Notification Services application. The report contains information about all scheduled subscriptions for a given subscriber.
The results handle local time conversion and take into account daylight saving time offset changes with respect to the locale. The results are useful for diagnosing why a subscription did not generate a notification at an expected time. It is common for users to configure subscription information incorrectly, which results in notifications being generated at unexpected times.
Syntax
[ schema_name . ] NSScheduledSubscriptionList
[ @SubscriberId = ] 'subscriber_id',
[ @StartDate = ] 'start_date' ]
[, [ @EndDate = ] 'end_date' ]
[, [ @Language = ] 'language_code' ]
Arguments
- [ @SubscriberId = ] 'subscriber_id'
Is the unique identifier used to represent a subscriber in the Notification Services instance. subscriber_id is nvarchar(255) and has no default value.
- [ @StartDate = ] 'start_date'
Is the start date and time, in UTC (Coordinated Universal Time or Greenwich Mean Time), for scheduled subscriptions. start_date is datetime and has no default value.
- [ @EndDate = ] 'end_date'
Is the end date and time, in UTC, for scheduled subscriptions. If not specified (or NULL), the default value is the start_date value plus one day. end_date is datetime.
- [ @Language = ] 'language_code'
Is one of the Notification Services languages, and specifies the language to include in the report. language_code is used to return a subset of time zones from the NSTimeZoneNames table in the instance database. language_code is nvarchar(6) and has a default value of 'en' (English).
For a complete list of language_code values, see Subscriber Locale Codes.
Return Code Values
0 (success) or 1 (failure)
Result Sets
The NSScheduledSubscriptionList result set is ordered by SubscriptionClassName value and then by SubscriptionId value.
| Column Name | Data Type | Description |
|---|---|---|
SubscriberEnabled |
tinyint |
Indicates whether the subscriber is currently enabled. A nonzero value indicates that the subscriber is enabled and can generate notifications. |
SubscriptionClassName |
nvarchar(255) |
Name of the subscription class that contains the scheduled subscription for the subscriber. |
SubscriptionId |
bigint |
ID of a subscription for the subscriber. |
SubscriptionEnabled |
tinyint |
Indicates whether the subscription is enabled. A nonzero value indicates that the subscription is enabled and can generate notifications. |
ScheduleText |
nvarchar(2048) |
User-supplied schedule text for the subscription. |
UtcDateTime |
datetime |
Date and time, in UTC, at which the scheduled subscription is to be processed. If the value is NULL, the subscription has no scheduled time falling within the specified time range. |
LocalTimeZone |
nvarchar(100) |
Name of the local time zone for the subscription. |
Observing |
nvarchar(100) |
Indicates whether the subscription observes daylight saving time. The text string returned is the time zone specific string. |
LocalDateTime |
datetime |
Local date and time for the scheduled subscription. The date and time indicates whether the time zone is in daylight saving time or standard time. If the value is NULL, the subscription has no scheduled time falling within the specified time range. |
Remarks
Notification Services creates the NSScheduledSubscriptionList 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
A. Get Subscriptions Scheduled for a Specific Date
The following example shows how to produce a report of all scheduled subscriptions for May 24, 2004, for subscriber "karen".
The application uses the default SchemaName settings, which places all application objects in the dbo schema.
EXEC dbo.NSScheduledSubscriptionList
@SubscriberId = N'karen',
@StartDate = '2004-05-24',
@EndDate = '2004-05-24 23:59:59';
B. Get Subscriptions Scheduled for a Specific Time Period
The following example shows how to produce a report of all scheduled subscriptions for the past two days for subscriber "karen".
In this example, the stored procedure (like all other application objects) is in the Stock schema, as specified in the SchemaName element of the ADF.
DECLARE @start datetime;
SET @start = DATEADD(day, -2, GETUTCDATE());
EXEC Stock.NSScheduledSubscriptionList
@SubscriberId = N'karen',
@StartDate = @start;
See Also
Reference
Notification Services Stored Procedures (Transact-SQL)
Other Resources
Notification Services Performance Reports
Subscriber Locale Codes
Enabling and Disabling Instances, Applications, or Components