Defining the Notification Schema

Subscription rules insert notifications into a notification view. This is a view over the notification table and is named after the notification class. The schemas of the view and underlying table are derived from the notification class schema. You define the fields that make up this schema in the notification class.

This topic describes the default fields that Notification Services adds to the schema, and how to create custom fields, including custom computed fields, for notification data.

Default Notification Fields

When creating that table that stores notifications for a notification class, Notification Services creates one column in the table for each declared notification field and adds five other columns used for notification formatting and delivery:

• NotificationId is a bigint column. Notification Services provides a value for this field for each notification. The value is unique within the notification class.
• NotificationBatchId is a bigint column. Each rule firing that generates notifications produces one or more batches of notifications. Notification Services automatically inserts the batch number into the notification table.
• SubscriberId is an nvarchar(255) column that contains the ID of the subscriber for the generated notification. Notification Services uses the subscriber ID to look up delivery information for the notification. The subscription rule that generates notifications must insert this value.
• DeviceName is an nvarchar(255) column that contains the name of a subscriber device, as stored in the SubscriberDevices table in the instance database. Notification Services uses the device name to get the delivery information, and Notification Services can provide the device name to a content formatter for custom formatting. The subscription rule that generates notifications must insert this value. Note that the SubscriberId and DeviceName pair must match a record in the SubscriberDevices table.
• SubscriberLocale is an nvarchar(10) column that contains a locale code. This locale code must be one of the supported locale codes, and must be inserted by the subscription rule. Notification Services passes this information to the content formatter so that it can do locale-specific formatting. For a list of supported codes, see Subscriber Locale Codes.

The subsequent columns in the notification table are defined by your notification fields in the notification class. After your custom fields, Notification Services adds three additional fields for tracking notification delivery:

• DeliveryStatusCode is a tinyint column that contains a code for delivery status. This information is not intended to be used for reporting.
• SentTime is a datetime column that contains the time at which the delivery protocol reported the notification delivery status.
• LinkNotificationId is a bigint column that is only relevant when using digest delivery. If the notification was included in a digest, and was not the first notification in that digest, this field contains the ID of the first notification. The first notification contains the status and time information.

These fields are not intended for debugging and reporting. Instead, use the NS<NotificationClassName>NotificationDistribution View, which provides this information in a more readable format.

Custom Notification Fields

Custom fields are the fields that store data that subscribers are interested in or that are otherwise used to generate the final notification.

Defining the custom fields for a notification class requires that you know what data you want to send, and must match the data produced by the subscription rule that generates notifications.

For each custom field, you must define the field name and data type. If you want to send notifications that include weather forecasts, you might define the following notification fields:

• Field name: City; Field type: nvarchar(35)
• Field name: State; Field type: nvarchar(35)
• Field name: Forecast; Field type: nvarchar(3500)

The data types for these fields must be compatible with the source data types used when generating the notifications.

If you are using digest delivery, and you want to group all of a subscriber's notifications for the same state, you set digest grouping to true for that field. The new definition for the State field is the following:

• Field name: State; Field type: nvarchar(35); digest grouping: true

For more information about digest delivery, see Specifying Digest or Multicast Delivery.

To define fields for a notification class

• If you are defining an application using XML, define individual fields in the Fields Element for Schema (ADF).
• If you are defining an application using Notification Services Management Objects (NMO), create individual fields using NotificationField objects and add them to a NotificationFieldCollection using the NotificationFields property.

Computed Fields

Computed fields allow you to compute notification data immediately before the content is passed to the content formatter.

Computed fields use Transact-SQL expressions to generate a value for a notification field immediately before the notification is passed to the content formatter. You can use any Transact-SQL expression that can be evaluated as part of a SELECT query in the application database. This includes the use of arbitrary constants and making function calls.

If you want to add a computed field to the notification class, you define a field name and the Transact-SQL expression. For example, if you want to convert a price into a formatted string, you might specify the following in the computed field definition:

• Field name: FormattedStockPrice;
• Transact-SQL expression: CONVERT(NVARCHAR(10), StockPrice, 1)

When a notification is sent to the content formatter, this computed field takes the value in the StockPrice field and converts it into a formatted string. For example, if the StockPrice field is a money value of 1000.70, the FormattedStockPrice will be the following string: 1,000.70.

You can also specify that a computed field be used to determine which notifications are grouped for digest delivery. To group notifications with the same computed field value, set the digest grouping property to true for the computed field.

To define computed fields for a notification class

• If you are defining an application in XML, define each field in a ComputedFields Element (ADF).
• If you are defining an application programmatically, create individual fields in NotificationComputedField objects and add them to a NotificationComputedFieldCollection in a NotificationClass object using the NotificationComputedFields property.

See Also

Concepts

Configuring Content Formatters
Specifying Digest or Multicast Delivery
Specifying the Notification Batch Size
Specifying the Notification Expiration Age
Specifying Digest or Multicast Delivery

Other Resources

Defining Notification Classes
Configuring Delivery Protocols
Expressions (Transact-SQL)
CREATE TABLE (Transact-SQL)
Defining Notification Classes
Defining Notification Services Applications

Help and Information

Getting SQL Server 2005 Assistance