Introduction
You can define special messages to return to the user during SQL Rule execution. These messages allow the user to see if the Rule is progressing between each SQL statement or step.
Configuration
Use the spRegisterProgress stored procedure (SP) to define steps in your SQL Rules:
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<Step>'
, @status = 1 -- Started
, @message = N''
The SP is configured as follows:
| Parameter | Function |
|---|---|
| @activityID | Required parameter that should typically be set to @_activityID |
| @step | Description or name of step |
| @status | Status of the step: 1 (Started), 2 (Completed), 3 (Warning), and 4 (Error) |
| @message | Detailed message to accompany the step |
Setup
1. Copy the SP you need from the list below and paste it before and/or after your SQL statements/steps.
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<Step>'
, @status = 1 -- Started
, @message = N''
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<Step>'
, @status = 2 -- Completed
, @message = N''
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<Step>'
, @status = 3 -- Warning
, @message = N''
EXEC dbo.[spRegisterProgress]
@activityID = @_activityID
, @step = N'<Step>'
, @status = 4 -- Error
, @message = N''
Note: The Warning and Error status messages are intended to be used within conditional constructs.
2. Replace <Step> with a description of the step.
Tip: By using the same step name, the status will update without requiring a new step. E.g., the same progress message can change from Started to Error.
3. Add an optional message to display between the ' ' for @message.
4. Go to Advanced and enable Track Progress in the SQL Rule.
Now when you run the Rule, the Progress tab in the Rule window should appear.
Example
For a SQL Rule to add an Entity, we have put progress messages before and after the logic.
Here's the result when we run the Rule:
Note: The Rule Progress SP applies to only your SQL statements. Any SSAS processes will run after your SQL Rule.