Export (0) Print
Expand All

NS<SubscriptionClassName>View

SQL Server 2005

Provides a view of the subscription records for the associated subscription class. Notification Services creates one view for each subscription class, using the following naming convention: NSSubscriptionClassNameView.

For example, the Stock sample that ships with Notification Services has two subscription classes: StockSubscriptions and PortfolioSubscriptions. The associated views are named NSStockSubscriptionsView and NSPortfolioSubscriptionsView.

ms173755.note(en-US,SQL.90).gifNote:
You can insert event-driven subscriptions that use simple actions into this view. You cannot insert scheduled subscriptions or subscriptions that use condition actions.

The information about each subscription that the NSSubscriptionClassNameView view contains is shown in the following table.

Column Data type Description

SubscriptionId

bigint

IDENTITY column. If inserting data into this view, the SubscriptionId value must be NULL.

SubscriberId

nvarchar(255)

The ID of the subscriber for the subscription.

Created

datetime

The date and time the subscription record was created. Do not insert values into this column.

Updated

datetime

The date and time the subscription record was last updated. Do not insert values into this column.

Enabled

nvarchar(8)

If the value is Enabled, the subscription is enabled and can generate notifications.

Schedule (scheduled subscriptions only)

nvarchar(2048)

If the subscription class supports scheduled subscriptions, this column appears and shows the schedule for executing scheduled rules for this subscription. If using this view to insert subscriptions, this value must be NULL.

Subscription_class_field_name (1-n)

application-defined

All columns specified in the subscription class also appear in this view. You cannot use this view to insert condition actions.

The NSSubscriptionClassNameView views are located in application databases.

The SELECT permission on this view is available to members of the sysadmin and db_owner server roles, and to the NSAnalysis and NSSubscriberAdmin Notification Services database roles. INSERT, UPDATE, and DELETE permissions are granted to the NSSubscriberAdmin database role.

The following query selects the ID, status, device name, and locale columns from the NSSubscriptionClassNameView view:

USE StockInstanceStock;
GO
SELECT SubscriberId, Enabled, DeviceName, SubscriberLocale
FROM NSStockSubscriptionsView
ORDER BY SubscriberId;
GO

The following query inserts a subscription into NSWeatherSubscriptionsView:

USE TutorialWeather;
GO
INSERT INTO NSWeatherSubscriptionsView 
    (SubscriberId, Enabled, DeviceName, SubscriberLocale, City)
VALUES 
    (N'TestUser1', N'Enabled', N'Work e-mail', N'en-US', N'Phelphs');
GO

Community Additions

ADD
Show:
© 2014 Microsoft