Microsoft KB Archive/818352

= Updated Documentation for NSDiagnosticFailedNotifications with SP1 =

Article ID: 818352

Article Last Modified on 11/21/2006

-

APPLIES TO


 * Microsoft SQL Server Notification Services 2.0 Service Pack 1

-



SUMMARY
This article includes new documentation on the stored procedure NSDiagnosticFailedNotifications.



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.

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'

Keywords: kbinfo kbdocfix KB818352

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.