Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

NS<NotificationClassName>NotificationDistribution View

SQL Server 2005

Combines data from several internal Microsoft SQL Server Notification Services tables to provide information about notification distribution attempts. Notification Services creates one view for each notification class using the following naming convention: NSNotificationClassNameNotificationDistribution.

For example, the Stock sample that ships with Notification Services has two notification classes: StockNotifications and PortfolioNotifications. The associated views are named NSStockNotificationsNotificationDistribution and NSPortfolioNotificationsNotificationDistribution.

The NSNotificationClassNameNotificationDistribution views contain the columns shown in the following table.

Column Data type Description

NotificationId

bigint

The notification ID number.

DeliveryChannelName

nvarchar (255)

The name of the delivery channel used to deliver the notification.

DistributorName

nvarchar(255)

The name of the distributor used to deliver the notification.

DeliveryRequestTime

datetime

The date and time that the distributor handed the notification data to the delivery protocol for delivery.

SentTime

datetime

The time the notification was actually sent.

DeliveryStatusDescription

nvarchar(255)

The status of the delivery attempt: not attempted, succeeded, or failed. For more information, see the Remarks section.

DeliveryStatusInfo

nvarchar(2048)

The status message that the delivery protocol returned to the distributor when reporting notification status. This is typically used to report the reason for a failure. NULL indicates no status message was sent.

NotificationText

nvarchar(2048)

The text of the notification.

SubscriberId

nvarchar(255)

The ID of the subscriber for the notification.

DeviceName

nvarchar(255)

The name of the delivery device specified in the subscription.

SubscriberLocale

nvarchar(10)

The locale of the subscriber for the notification.

Notification class fields (1-n)

application-defined

All columns specified in the notification class also appear in this view.

LinkNotificationId

bigint

The ID of the first notification included in the digest when using digest delivery. All subsequent notifications in the digest contain this ID. NULL indicates that the notification is not a subsequent notification in a digest.

The NSNotificationClassNameNotificationDistribution views are located in application databases.

The amount of data available in the view is controlled per application via the distributor logging settings in the application definition. These application execution settings control what is written to the distribution log, therefore controlling what is available to this view. For more information, see Configuring Distributor Logging.

A single notification can have multiple rows in the NSNotificationClassNameNotificationDistribution view, one for each delivery attempt. For example, if a notification delivery attempt fails twice and is then successful, the notification would have three rows in this view, until the vacuuming process removes the data.

Use the DeliveryStatusDescription column to determine notification delivery status. The following table contains descriptions of the status codes.

DeliveryStatusDescription value Description

Delivery never attempted: first try pending

The distributor has not yet attempted to deliver the notification. This might be due to application settings, a backlog of notifications to distribute, a distribution problem such as a disabled distributor, or too many failures (which is controlled by the FailuresBeforeAbort setting).

Delivery succeeded

The delivery protocol returned a value that indicates a successful delivery.

Delivery failed

One of the following events occurred:

  • A failure in the content formatting step.
  • An error while calling the delivery protocol.
  • A delivery failure reported by the delivery protocol.

To troubleshoot failed deliveries, look for error messages in the Application log in Microsoft Windows Event Viewer. Note that multiple errors might be logged for a single delivery failure; look at the description of each Notification Services error message.

This view is available to members of the sysadmin and db_owner server roles, and to the NSAnalysis role of Notification Services.

Run the following query on the Stock sample to determine whether any notification delivery attempts have failed:

USE StockInstanceStock;
SELECT NotificationId, DeliveryStatusInfo 
FROM NSStockNotificationsNotificationDistribution
WHERE DeliveryStatusDescription = N'Delivery failed';

The above query returns all failed notifications that have not been removed through the vacuuming process. If one notification has had multiple failed delivery attempts, the query returns one row for each failure.

To return a list of the notifications from the Stock sample that have failed once or more, run the following query:

USE StockInstanceStock;
SELECT DISTINCT(NotificationId) 
FROM NSStockNotificationsNotificationDistribution
WHERE DeliveryStatusDescription = N'Delivery failed';

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.