Export (0) Print
Expand All
Expand Minimize

NSExecuteRuleFiring (Transact-SQL)

SQL Server 2005

Prepares and runs Microsoft SQL Server Notification Services rules. This enables you to manually run the generator, running rules to generate notifications and to manage state.

Regardless of whether the rule firing is successful, this stored procedure attempts to prepare the next rule firing by materializing the appropriate event and scheduled subscription views so that you can debug the rule firing behavior.


[ application_schema_name . ] NSExecuteRuleFiring 
    [ [ @ReportExecutionResults = ] report_flag ]
    [, [ @DisableQuantumLimits = ] limit_flag ]

[@ReportExecutionResults = ] report_flag

Indicates whether the stored procedure reports information about the execution of the rule firing. Nonzero values result in information being returned about the rule firing. report_flag is tinyint and has a default value of 1.

[@DisableQuantumLimits = ] limit_flag

Indicates whether the stored procedure observes the application-specified event chronicle quantum and subscription quantum limits. A nonzero value causes the quantum scheduling to ignore the quantum limits. limit_flag is tinyint and has a default value of 1.

0 (success) or 1 (failure)

NSExecuteRuleFiring produces up to three result sets. The first result set contains information about any scheduled rules.

Column Name Data Type Description

QuantumId

int

ID number of the quantum for the rule firing.

QuantumStartTime

datetime

Start date and time, in UTC (Coordinated Universal Time or Greenwich Mean Time), of the quantum that contains the rule.

QuantumEndTime

datetime

End date and time, in UTC, of the quantum that contains the rule.

RuleName

nvarchar(255)

Name assigned to the rule in the application definition file (ADF). If no rule is ready to run, this value is NULL.

EventClassName

nvarchar(255)

For event-triggered rules, name of the related event class. If the rule is a scheduled subscription rule or if no rule is ready to run, this value is NULL.

SubscriptionClassName

nvarchar(255)

Name of the related subscription class. If the rule is an event chronicle rule or if no rule is ready to run, the value is NULL.

RuleFiringId

int

ID number that identifies the prepared rule firing. The column is NULL if no rule firing is ready to run.

EventBatchId

bigint

ID number that identifies the related event batch. If the rule is not an event chronicle or event subscription rule, the application is not processing events in order, or there is no rule ready to run, the value is NULL.

StartScanTime

datetime

Start date and time, in UTC, for a prepared scheduled subscription rule. The starting date and time must fall between QuantumStartTime and QuantumEndTime.

If the rule is not an event chronicle or event subscription rule, the application is not processing events in order, or there is no rule ready to run, the value is NULL.

EndScanTime

datetime

End date and time, in UTC, for a prepared scheduled subscription rule. The end date and time must fall between QuantumStartTime and QuantumEndTime.

If the rule is not an event chronicle or event subscription rule, the application is not processing events in order, or there is no rule ready to run, the value is NULL.

RemainingQuantumRuleFirings

int

Number of remaining rule firings to complete rule firing for the quantum. This number count includes any prepared rule firings, because they have not run. If there are no rules to prepare, this value is 0.

The second result set appears only if there are possible effects to any chronicles. The result set is ordered by PossibleChronicleReferenceByRule.

Column Name Data Type Description

PossibleChronicleReferenceByRule

nvarchar(255)

Name of the event chronicle associated with a prepared rule firing.

The third result set appears only if the stored procedure created notifications. The result set contains information about the notifications and is grouped by the NotificationClassName value.

Column Name Data Type Description

NotificationClassName

nvarchar(255)

Name of the notification class that produced notifications.

NotificationBatchId

bigint

ID number of the notification batch that contains the generated notifications.

NotificationCount

bigint

Number of notifications generated by the rule firing.

StartGenerationTime

datetime

Start date and time, in UTC, for the notification batch.

EndGenerationTime

datetime

End date and time, in UTC, for the notification batch.

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

The generator must be disabled to run the NSExecuteRuleFiring stored procedure. Because this stored procedure manually operates the generator, allowing the generator to run at the same time would make using this stored procedure difficult.

Use the NSPrepareRuleFiring stored procedure if you only want to prepare the application database for a rule firing and not actually fire the rule.

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

A. Report Results and Ignore Quantum Limits

The following example shows how to set the quantum ID to 1 and then fire the rule for that quantum.. The stored procedure reports results and ignores quantum limits specified in the ADF.

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

EXEC dbo.NSSetQuantumClock 
    @QuantumId = 1;
EXEC dbo.NSExecuteRuleFiring 
    @ReportExecutionResults = 1,
    @DisableQuantumLimits = 0;

When you enable the application after running this example, the quantum clock continues from quantum ID 1; the quantum clock does not automatically reset itself to the most-current quantum when enabled.

B. Use Default Settings

The following example shows how to set the quantum ID to 100 and then fire the rule for that quantum using the default settings. The stored procedure does not report results and ignores quantum limits.

In this example, the stored procedure (like all other application objects) is in the Stock schema, as specified in the SchemaName element of the ADF.

EXEC dbo.NSSetQuantumClock 
    @QuantumId = 100;
EXEC Stock.NSExecuteRuleFiring;

When you enable the application after running this example, the quantum clock continues from quantum ID 100; the quantum clock does not automatically reset itself to the most-current quantum when enabled.

Community Additions

ADD
Show:
© 2014 Microsoft