MORE INFORMATION
NSDiagnosticFailedNotifications is a stored procedure that
produces the failed notifications report for a Notification Services
application. The report contains a list of failed notifications with one row
for each failed delivery attempt, even if the notifications were eventually
delivered. If a notification fails multiple times, there are multiple rows in
the report for that notification.
Each row in the report includes the
notification and subscription classes that produced the notification, the
subscriber the notification would have been sent to, and information about the
delivery attempt. This information can be used to troubleshoot notification
delivery.
This report does not include delivery failures due to
content formatter errors, such as an XSLT file that is not valid. Formatting
errors are reported in the Application log, which is viewable from the Windows
Event Viewer.
Syntax
NSDiagnosticFailedNotifications
[@ApplicationName =]
'app_name' ,
[, [@StartDateTime =] 'start_date_time']
[,
[@EndDateTime =] 'end_date_time']Arguments
[@ApplicationName =] 'app_name' Is the name of an application, as defined in the
configuration file.
app_name is nvarchar(255) and has no default value.
[@StartDateTime =]'start_date_time'Is the report start date and time in UTC (Universal Time
Coordinate or Greenwich mean time). This report includes all failed delivery
attempts with a SentTime value (as defined below) between the
start_date_time and
end_date_time values.
start_date_time is
datetime. The default value is the system start date and time.
[@EndDateTime =] 'end_date_time'Is the report end date and time in UTC. This report includes
all failed delivery attempts with a
SentTime value between the
start_date_time and
end_date_time values.
end_date_time is
datetime. The default value is the result of the GETUTCDATE function,
which returns the time when the stored procedure is invoked.
Return Code Values
None.
Result Sets
| Column Name | Data Type | Description |
| ApplicationName | nvarchar(255) | Name of the application the failed notification is
from. |
| NotificationClassName | nvarchar(255) | Name of the notification class that generated the
notification. |
| DeliveryChannelName | nvarchar(255) | Name of the delivery channel that attempted to deliver
the notification. |
| NotificationBatchId | bigint | ID of the notification batch that contains the failed
notification. |
| NotificationId | bigint | ID of the failed notification. |
| SubscriberId | nvarchar(255) | ID number of the subscriber who should have received the
notification. |
| SubscriberDeviceAddress | nvarchar(255) | Address, such as an e-mail address, of the device that
should have received the notification. |
| DeliveryRequestTime | datetime | Date and time the distributor sent the notification to
the delivery protocol for delivery. This value is available only if the <LogBeforeDeliveryAttempts> element in the ADF is set to true. |
| SentTime | datetime | Date and time the delivery protocol reported the delivery
status of the notification. |
| NextRetryTime | datetime | Next date and time the distributor will attempt to
deliver the message. If this value is NULL, the distributor will not attempt
another delivery. |
Remarks
The
NSDiagnosticFailedNotifications stored procedure is created when you run
NSControl Create. It is located in each instance database, and calls a stored
procedure in the application database.
The output is ordered by
ApplicationName, NotificationClassName, DeliveryChannelName, and then
SubscriberId.
The report does not relate notifications to individual
subscriptions because more than one subscription can contribute to a
notification.
Notification Services does not store the reason for
failure in the database. The delivery protocol can write the reason to the
Application log if the delivery service supports error logging.
To
determine the current UTC date and time, run SELECT GETUTCDATE() in SQL Query
Analyzer. 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 database role, the
db_owner fixed database role, and the
sysadmin fixed server role.
Example
The following example produces the failed notifications report for
the Stock application. The report includes information for all notifications
that failed between 5:00 P.M. and 6:00 P.M. on May 23, 2002:
EXEC NSDiagnosticFailedNotifications N'Stock',
'2002-05-23 17:00', '2002-05-23 18:00'