NSDiagnosticDeliveryChannel (Transact-SQL)

Produces the delivery channel report for a Microsoft SQL Server Notification Services application. This report can help you analyze delivery channel activity and failed notifications.

Syntax

[ schema_name . ] NSDiagnosticDeliveryChannel 
    [ @ApplicationName = ] 'app_name' ,
    [ @DeliveryChannelName = ] 'delivery_channel_name' 
    [, [@ReportingInterval = ] interval ] 
    [, [@StartDateTime = ] 'start_date_time' ] 
    [, [@EndDateTime = ] 'end_date_time' ] 

Arguments

  • [ @ApplicationName = ] 'app_name'
    Is the name of a Notification Services application, as defined in the configuration file. app_name is nvarchar(255) and has no default value.
  • [ @DeliveryChannelName = ] 'delivery_channel_name'
    Is the name of a delivery channel, as defined in the configuration file. delivery_channel_name is nvarchar(255) and has no default value.
  • [ @ReportingInterval = ] interval
    Is the number of generator quanta in each reporting interval. The report contains one row per interval. interval is int and has a default value of 1, which indicates one generator quantum per interval.

    The quantum duration is defined in the application definition file (ADF).

  • [ @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 @EndDateTime - (5 * QuantumDuration * @ReportingInterval). Using the default value, the result set contains at most five rows, each row representing one reporting interval.
  • [ @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

0 (success) or 1 (failure)

Result Sets

Column Name Data Type Description

IntervalStartDateTime

datetime

Start date and time for the reporting interval in UTC.

IntervalEndDateTime

datetime

End date and time for the reporting interval in UTC.

NotificationBatchesDelivered

int

Number of notification batches delivered during the reporting interval.

NotificationDeliveryAttempts

int

Number of notification deliveries attempted on the delivery channel during the reporting interval.

NotificationSuccessfulDeliveries

int

Number of notifications successfully sent on the delivery channel during the reporting interval.

NotificationAttemptsFailed

int

Number of notification delivery attempts that failed on the delivery channel during the reporting interval.

NotificationToMessageRatio

float

During the reporting interval, ratio of notifications generated to messages sent.

For digest or multicast delivery, one message can include multiple notifications. This column indicates how many notifications, on average, are included in digest or multicast messages.

Remarks

Notification Services creates the NSDiagnosticDeliveryChannel 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.

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 Reporting Interval, Start Time, and End Time

The following example produces the delivery channel report for the Flight application and the FileChannel delivery channel. The instance uses the default database settings, which places all instance objects in the dbo schema.

The report includes 50 generator quanta in each row. The report starts at 5:00 P.M. on May 23, 2004, and ends at 6:00 P.M. the same day:

EXEC dbo.NSDiagnosticDeliveryChannel 
    @ApplicationName = N'Flight', 
    @DeliveryChannelName = N'FileChannel', 
    @ReportingInterval = 50, 
    @StartDateTime = '2004-05-23 17:00',
    @EndDateTime = '2004-05-23 18:00';

B. Use Default Values, Named Schema

The following example produces the delivery channel report for the Flight application and the FileChannel delivery channel. 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 one generator quanta in each row and show five generator quanta total.

EXEC FlightInstance.NSDiagnosticDeliveryChannel 
    @ApplicationName = N'Flight', 
    @DeliveryChannelName = N'FileChannel';

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