NSNotificationBatchDetails (Transact-SQL)
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.
Syntax
[ application_schema_name . ] NSNotificationBatchDetails
[ @NotificationClassName = ] 'notification_class_name',
[ @NotificationBatchId = ] notification_batch_id
Arguments
- [ @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.
Return Code Values
None
Result Sets
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. |
Remarks
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.
Permissions
Execute permissions default to members of the NSAnalysis database role, the db_owner fixed database role, and the sysadmin fixed server role.
Examples
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;
See Also
Reference
Notification Services Stored Procedures (Transact-SQL)
Other Resources
Notification Services Performance Reports
SchemaName Element (ADF)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|