NS<NotificationClassName>NotificationDistribution View
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. |
Remarks
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:
|
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.
Permissions
This view is available to members of the sysadmin and db_owner server roles, and to the NSAnalysis role of Notification Services.
Examples
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';
See Also
Reference
Other Resources
Configuring Distributor Logging