NSSnapshotDeliveryChannels (Transact-SQL)
Produces the delivery channels report, which provides information about the current state of the delivery channels configured across an instance of Microsoft SQL Server Notification Services.
Syntax
[ schema_name . ] NSSnapshotDeliveryChannels
Return Code Values
0 (success) or 1 (failure)
Result Sets
| Column Name | Data Type | Description |
|---|---|---|
ApplicationName |
nvarchar(255) |
Name of an application. |
ApplicationActivationState |
nvarchar(255) |
Activation state for the application. The state can be enabled or disabled. |
DistributorName |
nvarchar(255) |
Name of a distributor used by the application. |
DistributorActivationState |
nvarchar(255) |
Activation state for the distributor in the application. The state can be enabled, enabled pending, disabled, or disabled pending. |
SystemName |
nvarchar(255) |
Name of the computer system where the distributor is running. |
DeliveryChannelName |
nvarchar(255) |
Name of a delivery channel used by the application. |
LastNotificationClassDelivered |
nvarchar(255) |
Name of the notification class that last had notifications delivered. |
LastTimeProcessedWorkItem |
datetime |
Date and time the last work item was processed by the delivery channel. |
SecondsSinceWorkItemProcessed |
int |
Elapsed time, in seconds, since the last work item was processed. |
AttemptedNotificationsSent |
bigint |
Number of notifications the delivery channel attempted to send. |
SuccessfullySentNotifications |
bigint |
Number of notifications the delivery channel successfully sent. |
FailedSendNotificationAttempts |
bigint |
Number of notifications the delivery channel attempted to send, but which ended as failed. |
NotificationToMessageRatio |
float |
Ratio of notifications generated for the delivery channel to messages sent by the delivery channel. For digest or multicast delivery, one message can include multiple notifications. This column indicates how many notifications are sent, on average, in a digest or multicast message. |
Remarks
Notification Services creates the NSSnapshotDeliveryChannels 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 result set is ordered by the ApplicationName value, then by the DeliveryChannelName value, and then by the DistributorName value.
Permissions
Execute permissions default to members of the NSAnalysis database role, db_owner fixed database role, and sysadmin fixed server role.
Examples
The following example produces the delivery channels snapshot report for all applications that are hosted by an instance. In this example, the stored procedure (and all other instance objects) is in the StockInstance schema, as specified in the SchemaName element of the ICF.
EXEC StockInstance.NSSnapshotDeliveryChannels;
See Also
Reference
Notification Services Stored Procedures (Transact-SQL)
Other Resources
Notification Services Performance Reports
SchemaName Element (ICF)