Export (0) Print
Expand All
Expand Minimize

NSScheduledSubscriptionList (Transact-SQL)

SQL Server 2005

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.


[ schema_name . ] NSScheduledSubscriptionList 
    [ @SubscriberId = ] 'subscriber_id',
    [ @StartDate = ] 'start_date' ]
    [, [ @EndDate = ] 'end_date' ]
    [, [ @Language = ] 'language_code' ]

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

0 (success) or 1 (failure)

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.

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.

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

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;

Community Additions

ADD
Show:
© 2014 Microsoft