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)