Share via


NSDiagnosticFailedNotifications (Transact-SQL)

Produces the failed notifications report for a Microsoft SQL Server Notification Services application. The report contains a list of failed notification delivery attempts. Each row in the report includes the notification and subscription classes that produced the notification, the subscriber to which the notification would have been sent, and information about a delivery attempt. Use this report to troubleshoot notification delivery.

Syntax

[ schema_name . ] 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 (Coordinated Universal Time or Greenwich Mean Time). 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. end_date_time is datetime. The default value is the time at which you invoke the stored procedure (in UTC).

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.

If the result is NULL, the subscription is not associated with a valid subscriber device and therefore could not be assigned to a delivery channel.

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 at which the distributor requested the delivery protocol to deliver the notification.

SentTime

datetime

Date and time the delivery protocol reported that the notification delivery failed.

NextRetryTime

datetime

Next date and time the distributor will attempt to deliver the message.

Remarks

Notification Services creates the NSDiagnosticFailedNotifications stored procedure in the instance database when you create the instance. When you update the instance, Notification Services recompiles the stored procedure.

This stored procedure is in the instance schema, which is specified by the SchemaName element of the instance configuration file (ICF). If no schema name is provided, the default schema is dbo.

The output is ordered by ApplicationName, NotificationClassName, DeliveryChannelName, and then SubscriberId.

The report does not relate notifications to individual subscriptions because a single subscription can generate more than one notification and more than one subscription can contribute to a single notification (depending on the application logic).

If the delivery service supports error logging, it can log the failure of a particular notification to the database. You can view this data using the NSNotificationClassNameNotificationDistribution view.

To determine the current UTC date and time, run SELECT GETUTCDATE() in SQL Server Management Studio. 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.

Examples

A. Specify Start Time and End Time Values

The following example produces the failed notifications report for the Flight application. The instance uses the default database settings, which places all instance objects in the dbo schema.

The report includes information for all notifications that failed between 5:00 P.M. and 6:00 P.M. on 23 May, 2004.

EXEC dbo.NSDiagnosticFailedNotifications 
    @ApplicationName = N'Flight', 
    @StartDateTime = '2004-05-23 17:00', 
    @EndDateTime = '2004-05-23 18:00';

B. Use Default Values, Named Schema

The following example produces the failed notifications report for the Flight application. In this example, the stored procedure (like all other instance objects) is in the FlightInstance schema, as specified in the SchemaName element of the ICF.

The report uses default values, which specifies to show all data since the application started.

EXEC FlightInstance.NSDiagnosticFailedNotifications 
    @ApplicationName = N'Flight';

See Also

Reference

Notification Services Stored Procedures (Transact-SQL)

Other Resources

Notification Services Performance Reports
SchemaName Element (ICF)

Help and Information

Getting SQL Server 2005 Assistance