Microsoft KB Archive/811903

= BUG: Stored Procedures Do Not Accurately Report Failed Notifications =

Article ID: 811903

Article Last Modified on 5/10/2007

-

APPLIES TO


 * Microsoft SQL Server Notification Services 2.0

-



SYMPTOMS
When you run the NSDiagnosticFailedNotifications stored procedure for a list of failed notifications, the stored procedure does not report any failed notifications even though there are failed notifications to report.

Additionally, the distribution log list that the NS NotificationDistribution view generates does not include delivery failures that occur because of content formatting errors. As a result, the NSDiagnosticNotificationClass stored procedure logs some failed notifications in the NotificationsNotYetDelivered column because the notifications are not marked as &quot;failed&quot; in the distribution log.



WORKAROUND
To work around this problem, query the NS Notifications table of the application database to obtain the list of failed notifications. The following sample query is a Transact-SQL query that returns all failed notifications that the vacuuming process does not remove.

Note In the following sample query:
 * Replace  with the name of the application database.
 * Replace  with the notification class name.

Use 

GO

SELECT NotificationId, NotificationBatchId FROM NSNotifications WHERE DeliveryStatusCode=4

GO Example: Use FlightInstanceFlight

GO SELECT NotificationId, NotificationBatchId FROM NSFlightNotificationsNotifications WHERE DeliveryStatusCode=4 GO You can then use the NotificationBatchId value with the NSNotificationBatchDetails stored procedure to obtain more information about the failed notifications. To troubleshoot failed notifications, view the error information in the Application log in Event Viewer.



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000 Notification Services RTM Version.



MORE INFORMATION
For more information about how to monitor and troubleshoot SQL Server Notification Services, visit the following Microsoft Web sites:

Choosing Monitoring Tools

Configuring Notification Services Event Logging

Notification Services Troubleshooting

NSNotificationBatchDetails

For information about corrections to SQL Server Notification Services Books Online, visit the following Microsoft Web site:

Corrections to Notification Services Books Online

Keywords: kbsysadmin kbbug KB811903

-

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

© Microsoft Corporation. All rights reserved.