NSExecuteRuleFiring (Transact-SQL)
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.
Syntax
[ application_schema_name . ] NSExecuteRuleFiring
[ [ @ReportExecutionResults = ] report_flag ]
[, [ @DisableQuantumLimits = ] limit_flag ]
Arguments
- [@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.
Return Code Values
0 (success) or 1 (failure)
Result Sets
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. |
Remarks
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.
Permissions
Execute permissions default to members of the NSGenerator and NSRunService database roles, the db_owner fixed database role, and the sysadmin fixed server role.
Examples
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.
See Also
Reference
NSPrepareRuleFiring (Transact-SQL)
NSSetQuantumClock (Transact-SQL)
NSSetQuantumClockDate (Transact-SQL)
Notification Services Stored Procedures (Transact-SQL)
NSPrepareRuleFiring (Transact-SQL)
Other Resources
Notification Services Performance Reports
SchemaName Element (ADF)