Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

NSNotificationBatchDetails (Transact-SQL)

SQL Server 2005

Updated: 14 April 2006

Produces the notification batch details report for a Microsoft SQL Server Notification Services application. The report contains information about a specified notification batch, such as the rule firing that produced the batch and the notifications contained in the batch. Use this stored procedure to troubleshoot notification generation and to analyze the progression of notifications through the application.


[ application_schema_name . ] NSNotificationBatchDetails 
    [ @NotificationClassName = ] 'notification_class_name',
    [ @NotificationBatchId = ] notification_batch_id
[ @NotificationClassName = ] 'notification_class_name'

Is the name of a notification class. notification_class_name is nvarchar(255) and has no default value.

[ @NotificationBatchId = ] notification_batch_id

Is the unique identifier used to identify a notification batch. notification_batch_id is bigint and has no default value.

NSNotificationBatchDetails produces three result sets. The first result set provides general information about the notification batch.

Column Name Data Type Description

RuleFiringId

int

Unique ID of the rule firing that produced the notification batch.

StartGenerationTime

datetime

Start date and time, in UTC (Coordinated Universal Time or Greenwich Mean Time), of the notification batch generation.

EndGenerationTime

datetime

End date and time, in UTC, of the notification batch generation.

GenerationTimeInMS

int

Time, in milliseconds, to complete the generation of the notification batch.

NotificationBatchExpirationTime

datetime

Date and time, in UTC, at which the notification batch expires. After the expiration time, any remaining undelivered notifications are abandoned. Delivery of these notifications will not be attempted again, regardless of the retry schedule. If this value is NULL, the batch has no expiration time.

NotificationCount

int

Number of notifications in the notification batch.

StatusDescription

nvarchar(255)

Current status of the notification batch.

The second result set provides information about how the notifications in the batch were distributed across the delivery channels in the instance.

Column Name Data Type Description

DeliveryChannelName

nvarchar(255)

Name of the delivery channel.

LastDistributionStartTime

datetime

Last time the distributor started to deliver notifications from this notification batch via this delivery channel.

NotificationsWithNoDeliveryAttempt

int

Number of notifications the distributor has yet to deliver from this notification batch via this delivery channel.

NotificationsSuccessfullyDelivered

int

Number of successful notification deliveries from this notification batch via this delivery channel.

NotificationsFailedToDeliver

int

Number of failed notification deliveries from this notification batch for this delivery channel.

RetryCount

int

Number of times the distributor has picked up the notification batch for delivery via this delivery channel.

NextRetryTime

datetime

Next time at which the distributor will attempt to deliver notifications from this notification batch via this delivery channel.

The third result set provides detailed information about notifications in the notification batch.

Column Name Data Type Description

NotificationId

bigint

ID of the notification within the notification batch.

SubscriberId

nvarchar(255)

ID of the subscriber for the notification.

DeviceName

nvarchar(255)

Name of the subscriber device for the notification.

SubscriberLocale

nvarchar(10)

Name of the locale for the notification.

DeliveryStatus

nvarchar(255)

Description of the current delivery status for the notification.

SentTime

datetime

The last date and time, in UTC, at which a delivery attempt for the notification was completed. If this value is NULL, delivery of the notification has either not been attempted, or has not yet completed.

LinkedParentNotification

bigint

If the notification was sent in a digest or multicast message, ID of the head notification in the digest or multicast group.

DeliveryChannel

nvarchar(255)

Name of the delivery channel that sent the notification.

DeliveryAttempts

int

Number of delivery attempts made for the notification.

Notification_class_field_name

application defined

The name of a notification class field, as defined in the application definition file (ADF). Each field in the notification class is represented by a column in the result set.

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

This stored procedure is in the application's schema, which is specified by the SchemaName element of the application definition file (ADF). If no schema name is provided, the default schema is dbo.

Execute permissions default to members of the NSAnalysis database role, the db_owner fixed database role, and the sysadmin fixed server role.

This example produces the three result sets specified earlier in this section for notification batch 1 of the StockNotifications notification class:

The application uses the default SchemaName settings, which places all application objects in the dbo schema.

EXEC dbo.NSNotificationBatchDetails 
    @NotificationClassName = N'StockNotifications', 
    @NotificationBatchId = 1;

Release History

14 April 2006

Changed content:
  • Fixed column descriptions in the second result set.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.