Overview
A Stored Procedure task allows you to execute a stored procedure against the database specified in the task.
Note
Before you can run a Stored Procedure task, you first must create a Database Connection, which defines the information needed to locate and access the database.
Built-In Variables
The following built-in variables can be used in a Stored Procedure task to pass data where appropriate:
Creating a Stored Procedure Task
Step 1 | From the Automation Center navigation pane, select Tasks > Stored Procedure Tasks. The Stored Procedure Tasks list displays a list of all currently defined Stored Procedure tasks. |
---|---|
Step 2 | Enter/select Details for a new Stored Procedure task, using the field descriptions below as a guide.
To display more of the Details fields on the screen, you can either:
|
Step 3 | Click a button. The task is added to the database, and all buttons and tabs in the Task Details are enabled. |
Note
To open an existing record on the list, either:
- Click a record in the list to display its record Details below the list. (To clear record Details below the list, click the New button that displays above and below the Details.)
- Clicking the Details icon next to a record name in the list, or right-click a record in the list and then click Open in the Action menu that displays, to display a pop-up version of the record Details.
- Right-click a record in the a list, or open a record and right-click in the record Details, and then click Open In Tab in the Action menu that displays, to display the record Details under a new tab on the record list page (see Record Details as Tabs).
Stored Procedure Task Details
The following Stored Procedure Task Details is for an existing Stored Procedure task.
Depending on the values that you enter / select for these fields, and whether or not the Stored Procedure task has ever been launched, more (or less) fields may display. See the field descriptions, below, for a description of all fields that may display in the Stored Procedure Task Details.
Stored Procedure Task Details Field Descriptions
The following table describes the fields, buttons, and tabs that display in the Stored Procedure Task Details.
Field Name | Description |
---|---|
General | This section contains general information about the task. |
Name | Unable to render {include} The included page could not be found. |
Version | Unable to render {include} The included page could not be found. |
Description | Unable to render {include} The included page could not be found. |
Member of Business Services | Unable to render {include} The included page could not be found. |
Resolve Name Immediately | Unable to render {include} The included page could not be found. |
Time Zone Preference | Unable to render {include} The included page could not be found. |
Hold on Start | Unable to render {include} The included page could not be found. |
Hold Reason | Unable to render {include} The included page could not be found. |
Virtual Resource Priority | Unable to render {include} The included page could not be found. |
Hold Resources on Failure | Unable to render {include} The included page could not be found. |
Mutually Exclusive With Self | If enabled, the task will not be allowed to run concurrently with itself. Task will not start until the instance that is running finishes. An instance will transition to Exclusive Wait status if it cannot start due to another instance already running. |
Stored Procedure Details | This section contains assorted detailed information about the task. |
Database Connection | Unable to render {include} The included page could not be found. |
Database Connection Variable | Unable to render {include} The included page could not be found. |
Credentials | Unable to render {include} The included page could not be found. |
Credentials Variable | Unable to render {include} The included page could not be found. |
Maximum Rows | Unable to render {include} The included page could not be found. |
Auto Cleanup | Unable to render {include} The included page could not be found. |
Stored Procedure Name | Unable to render {include} The included page could not be found. |
Result Processing Details | This section contains assorted detailed information about result processing for this task. |
Result Processing | Unable to render {include} The included page could not be found. |
Exit Codes | Unable to render {include} The included page could not be found. |
Parameter Position | Unable to render {include} The included page could not be found. |
Column Name | Unable to render {include} The included page could not be found. |
Operator | Unable to render {include} The included page could not be found. |
Value | Unable to render {include} The included page could not be found. |
Retry Options | This section contains specifications for retrying the task. |
Maximum Retries | Unable to render {include} The included page could not be found. |
Retry Indefinitely | Unable to render {include} The included page could not be found. |
Retry Interval (Seconds) | Unable to render {include} The included page could not be found. |
Suppress Intermediate Failures | Unable to render {include} The included page could not be found. |
Wait / Delay Options | This section contains specifications for waiting to start and/or delaying on start the task. |
Wait To Start | Unable to render {include} The included page could not be found. |
Wait Time | Unable to render {include} The included page could not be found. |
Wait Day Constraint | Unable to render {include} The included page could not be found. |
Wait Duration | Unable to render {include} The included page could not be found. |
Wait Duration In Seconds | Unable to render {include} The included page could not be found. |
Delay On Start | Unable to render {include} The included page could not be found. |
Delay Duration | Unable to render {include} The included page could not be found. |
Delay Duration In Seconds | Unable to render {include} The included page could not be found. |
Workflow Only | Unable to render {include} The included page could not be found. |
Time Options | This section contains time-related specifications for the task. |
Late Start | Unable to render {include} The included page could not be found. |
Late Start Type | Unable to render {include} The included page could not be found. |
Late Start Time | Unable to render {include} The included page could not be found. |
Late Start Day Constraint | Unable to render {include} The included page could not be found. |
Late Start Nth Amount | Unable to render {include} The included page could not be found. |
Late Start Duration | Unable to render {include} The included page could not be found. |
Late Finish | Unable to render {include} The included page could not be found. |
Late Finish Type | Unable to render {include} The included page could not be found. |
Late Finish Offset Type | Unable to render {include} The included page could not be found. |
Late Finish Percentage Offset ( + ) | Unable to render {include} The included page could not be found. |
Late Finish Duration Offset ( + ) | Unable to render {include} The included page could not be found. |
Late Finish Duration Offset Unit | Unable to render {include} The included page could not be found. |
Late Finish Time | Unable to render {include} The included page could not be found. |
Late Finish Day Constraint | Unable to render {include} The included page could not be found. |
Late Finish Nth Amount | Unable to render {include} The included page could not be found. |
Late Finish Duration | Unable to render {include} The included page could not be found. |
Early Finish | Unable to render {include} The included page could not be found. |
Early Finish Type | Unable to render {include} The included page could not be found. |
Early Finish Offset Type | Unable to render {include} The included page could not be found. |
Early Finish Percentage Offset ( - ) | Unable to render {include} The included page could not be found. |
Early Finish Duration Offset ( - ) | Unable to render {include} The included page could not be found. |
Early Finish Duration Offset Unit | Unable to render {include} The included page could not be found. |
Early Finish Time | Unable to render {include} The included page could not be found. |
Early Finish Day Constraint | Unable to render {include} The included page could not be found. |
Early Finish Nth Amount | Unable to render {include} The included page could not be found. |
Early Finish Duration | Unable to render {include} The included page could not be found. |
User Estimated Duration | Unable to render {include} The included page could not be found. |
Critical Path Options | This section contains Critical Path-related specifications for the task. |
CP Duration | Unable to render {include} The included page could not be found. |
CP Duration (Resolved) | Unable to render {include} The included page could not be found. |
CP Duration Unit | Unable to render {include} The included page could not be found. |
Workflow Execution Options | This section contains Execution Restriction specifications for the task if it is within a Workflow. |
Execution Restriction | Unable to render {include} The included page could not be found. |
Restriction Period | Unable to render {include} The included page could not be found. |
Before Date | Unable to render {include} The included page could not be found. |
Before Time | Unable to render {include} The included page could not be found. |
After Date | Unable to render {include} The included page could not be found. |
After Time | Unable to render {include} The included page could not be found. |
Date List | Unable to render {include} The included page could not be found. |
Statistics | This section contains time-related statistics for task instances of the task. |
First Execution | Unable to render {include} The included page could not be found. |
Last Execution | Unable to render {include} The included page could not be found. |
Last Instance Duration | Unable to render {include} The included page could not be found. |
Lowest Instance Time | Unable to render {include} The included page could not be found. |
Average Instance Time | Unable to render {include} The included page could not be found. |
Highest Instance Time | Unable to render {include} The included page could not be found. |
Number of Instances | Unable to render {include} The included page could not be found. |
Metadata | This section contains Metadata information about this record. |
UUID | Universally Unique Identifier of this record. |
Updated By | Name of the user that last updated this record. |
Updated | Date and time that this record was last updated. |
Created By | Name of the user that created this record. |
Created | Date and time that this record was created. |
Buttons | This section identifies the buttons displayed above and below the Task Details that let you perform various actions. |
Save | Saves a new task record in the Controller database. |
Save & New | Saves a new record in the Controller database and redisplays empty Details so that you can create another new record. |
Save & View | Saves a new record in the Controller database and continues to display that record. |
New | Displays empty (except for default values) Details for creating a new task. |
Update | Unable to render {include} The included page could not be found. |
Launch Task | Unable to render {include} The included page could not be found. |
View Parents | Unable to render {include} The included page could not be found. |
Copy | Creates a copy of this task, which you are prompted to rename. |
Delete | Unable to render {include} The included page could not be found. Note You cannot delete a task if it is either:
|
Refresh | Refreshes any dynamic data displayed in the Details. |
Close | For pop-up view only; closes the pop-up view of this task. |
Tabs | This section identifies the tabs across the top of the Task Details that provide access to additional information about the task instance. |
Stored Procedure Parameters | See Adding Stored Procedure Parameters, below. |
Variables | Unable to render {include} The included page could not be found. |
Actions | Unable to render {include} The included page could not be found. |
Virtual Resources | Unable to render {include} The included page could not be found. |
Mutually Exclusive | Unable to render {include} The included page could not be found. |
Instances | Lists all instances of the task. |
Triggers | Unable to render {include} The included page could not be found. |
Notes | Unable to render {include} The included page could not be found. |
Versions | Unable to render {include} The included page could not be found. |
Viewing a Stored Procedure Task Instance
When a Stored Procedure task is launched, the Controller creates a task instance record of that task.
A task instance contains detailed information about a single execution of that task.
You can access a task instance from:
- Instances tab on the Stored Procedure Task Details for that task
- Activity Monitor
- Task Instances list
Stored Procedure Task Instance Details
The following Stored Procedure Task Instance Details contains information on the execution of the task shown in the Stored Procedure Task Details.
Stored Procedure Task Instance Details Field Descriptions
The following table describes the fields, buttons, and tabs that display in Stored Procedure Task Instance Details.
Field Name | Description |
---|---|
General | This section contains general information about the task instance. |
Instance Name | Name of this task instance. |
Instance Number | Unable to render {include} The included page could not be found. |
Description | Unable to render {include} The included page could not be found. |
Member of Business Services | Unable to render {include} The included page could not be found. |
Task | Unable to render {include} The included page could not be found. |
Source Version | Unable to render {include} The included page could not be found. |
Launch Source | Unable to render {include} The included page could not be found. |
Source Instance | Unable to render {include} The included page could not be found. |
Invoked by | Unable to render {include} The included page could not be found. |
Execution User | Unable to render {include} The included page could not be found. |
Calendar | Unable to render {include} The included page could not be found. |
Time Zone Preference | Unable to render {include} The included page could not be found. |
Virtual Resource Priority | Unable to render {include} The included page could not be found. |
Hold Resources on Failure | Unable to render {include} The included page could not be found. |
Mutually Exclusive With Self | If enabled, the task will not be allowed to run concurrently with itself. Task will not start until the instance that is running finishes. An instance will transition to Exclusive Wait status if it cannot start due to another instance already running. |
Status | This section contains information about the current status of the task instance. |
Status | Unable to render {include} The included page could not be found. |
Exit Code | Unable to render {include} The included page could not be found. |
Status Description | Unable to render {include} The included page could not be found. |
Operational Memo | Unable to render {include} The included page could not be found. |
Evaluation Time | Unable to render {include} The included page could not be found. |
Critical | Unable to render {include} The included page could not be found. |
Wait Until Time | Unable to render {include} The included page could not be found. |
Queued Time | Unable to render {include} The included page could not be found. |
Trigger Time | Unable to render {include} The included page could not be found. |
Launch Time | Unable to render {include} The included page could not be found. |
Start Time | Unable to render {include} The included page could not be found. |
End Time | Unable to render {include} The included page could not be found. |
Duration | Unable to render {include} The included page could not be found. |
SQL State | Unable to render {include} The included page could not be found. |
Rows Retrieved | Unable to render {include} The included page could not be found. |
SQL Error Message | Unable to render {include} The included page could not be found. |
Stored Procedure Details | This section contains assorted detailed information about the task instance. |
Database Connection | Unable to render {include} The included page could not be found. |
Database Connection Variable | Unable to render {include} The included page could not be found. |
Credentials | Unable to render {include} The included page could not be found. |
Credentials Variable | Unable to render {include} The included page could not be found. |
Maximum Rows | Unable to render {include} The included page could not be found. |
Auto Cleanup | Unable to render {include} The included page could not be found. |
Stored Procedure Name | Unable to render {include} The included page could not be found. |
Result Processing Details | This section contains assorted detailed information about result processing for this task. |
Result Processing | Unable to render {include} The included page could not be found. |
Exit Codes | Unable to render {include} The included page could not be found. |
Parameter Position | Unable to render {include} The included page could not be found. |
Column Name | Unable to render {include} The included page could not be found. |
Operator | Unable to render {include} The included page could not be found. |
Value | Unable to render {include} The included page could not be found. |
Retry Options | This section contains specifications for retrying the task. |
Maximum Retries | Unable to render {include} The included page could not be found. |
Retry Indefinitely | Unable to render {include} The included page could not be found. |
Retry Interval (Seconds) | Unable to render {include} The included page could not be found. |
Current Retry Count | Unable to render {include} The included page could not be found. |
Suppress Intermediate Failures | Unable to render {include} The included page could not be found. |
Next Retry Time | Unable to render {include} The included page could not be found. |
Wait / Delay Options | This section contains specifications for waiting to start and/or delaying on start the task. |
Wait To Start | Unable to render {include} The included page could not be found. |
Wait Time | Unable to render {include} The included page could not be found. |
Wait Day Constraint | Unable to render {include} The included page could not be found. |
Wait Duration | Unable to render {include} The included page could not be found. |
Wait Duration In Seconds | Unable to render {include} The included page could not be found. |
Delay On Start | Unable to render {include} The included page could not be found. |
Delay Duration | Unable to render {include} The included page could not be found. |
Delay Duration In Seconds | Unable to render {include} The included page could not be found. |
Time Options | This section contains time-related specifications for the task instance. |
Late Start | Unable to render {include} The included page could not be found. |
Started Late | Unable to render {include} The included page could not be found. |
Late Start Type | Unable to render {include} The included page could not be found. |
Late Start Time | Unable to render {include} The included page could not be found. |
Late Start Day Constraint | Unable to render {include} The included page could not be found. |
Late Start Nth Amount | Unable to render {include} The included page could not be found. |
Late Start Duration | Unable to render {include} The included page could not be found. |
Computed Late Start Time | Unable to render {include} The included page could not be found. |
Late Finish | Unable to render {include} The included page could not be found. |
Finished Late | Unable to render {include} The included page could not be found. |
Late Finish Type | Unable to render {include} The included page could not be found. |
Late Finish Offset Type | Unable to render {include} The included page could not be found. |
Late Finish Percentage Offset ( + ) | Unable to render {include} The included page could not be found. |
Late Finish Duration Offset ( + ) | Unable to render {include} The included page could not be found. |
Late Finish Duration Offset Unit | Unable to render {include} The included page could not be found. |
Late Finish Time | Unable to render {include} The included page could not be found. |
Late Finish Day Constraint | Unable to render {include} The included page could not be found. |
Late Finish Nth Amount | Unable to render {include} The included page could not be found. |
Late Finish Duration | Unable to render {include} The included page could not be found. |
Computed Late Finish Time | Unable to render {include} The included page could not be found. |
Early Finish | Unable to render {include} The included page could not be found. |
Finished Early | Unable to render {include} The included page could not be found. |
Early Finish Type | Unable to render {include} The included page could not be found. |
Early Finish Offset Type | Unable to render {include} The included page could not be found. |
Early Finish Percentage Offset ( - ) | Unable to render {include} The included page could not be found. |
Early Finish Duration Offset ( - ) | Unable to render {include} The included page could not be found. |
Early Finish Duration Offset Unit | Unable to render {include} The included page could not be found. |
Early Finish Time | Unable to render {include} The included page could not be found. |
Early Finish Day Constraint | Unable to render {include} The included page could not be found. |
Early Finish Nth Amount | Unable to render {include} The included page could not be found. |
Early Finish Duration | Unable to render {include} The included page could not be found. |
Projected Late | Unable to render {include} The included page could not be found. |
Critical Path Options | This section contains Critical Path-related specifications for the task. |
CP Duration | Unable to render {include} The included page could not be found. |
CP Duration (Resolved) | Unable to render {include} The included page could not be found. |
CP Duration Unit | Unable to render {include} The included page could not be found. |
Workflow Execution Options | This section contains Execution Restriction specifications for the task if it is within a Workflow. |
Execution Restriction | Unable to render {include} The included page could not be found. |
Restriction Period | Unable to render {include} The included page could not be found. |
Before Date | Unable to render {include} The included page could not be found. |
Before Time | Unable to render {include} The included page could not be found. |
After Date | Unable to render {include} The included page could not be found. |
After Time | Unable to render {include} The included page could not be found. |
Date List | Unable to render {include} The included page could not be found. |
Statistics | This section contains time-related statistics for the task instance. |
User Estimated End Time | Unable to render {include} The included page could not be found. |
Lowest Estimated End Time | Unable to render {include} The included page could not be found. |
Average Estimated End Time | Unable to render {include} The included page could not be found. |
Highest Estimated End Time | Unable to render {include} The included page could not be found. |
Projected End Time | System-supplied; projected end time of the task instance, calculated by the Controller based on the projected end time of its predecessor (or the maximum projected end time of all its predecessors, if more than one path exists to that task instance) plus its estimated critical path duration. |
Metadata | This section contains Metadata information about this record. |
UUID | Universally Unique Identifier of this record. |
Updated By | Name of the user that last updated this record. |
Updated | Date and time that this record was last updated. |
Created By | Name of the user that created this record. |
Created | Date and time that this record was created. |
Status History | History of all statuses that the task instance has gone through. |
Buttons | This section identifies the buttons displayed above and below the Task Instance Details that let you perform various actions. |
Update | Unable to render {include} The included page could not be found. |
Force Finish | Unable to render {include} The included page could not be found. |
Hold | Unable to render {include} The included page could not be found. |
Skip | Unable to render {include} The included page could not be found. |
Re-run | Unable to render {include} The included page could not be found. |
View Parent | Unable to render {include} The included page could not be found. |
Delete | Unable to render {include} The included page could not be found. |
Refresh | Refreshes any dynamic data displayed in the Details. |
Close | For pop-up view only; closes the pop-up view of this task instance. |
Tabs | This section identifies the tabs across the top of the Task Instance Details that provide access to additional information about the task instance. |
Stored Procedure Parameters | See Adding Stored Procedure Parameters, below. |
SQL Results | Unable to render {include} The included page could not be found. |
SQL Warnings | Unable to render {include} The included page could not be found. |
Virtual Resources | Unable to render {include} The included page could not be found. |
Exclusive Requests | Unable to render {include} The included page could not be found. |
Notes | Unable to render {include} The included page could not be found. |
Adding Stored Procedure Parameters
You can enter one or more parameters for each stored procedure, as described below.
Adding a Parameter
Step 1 | Open the Stored Procedure task to which you want to add the parameter. |
---|---|
Step 2 | Click the Stored Procedure Parameters tab. The Stored Procedure Parameters list displays a list of all currently defined Stored Procedure parameters. |
Step 3 | Click the New button to display Stored Procedures Parameter Details for a new parameter. |
Step 4 | Use the field descriptions, below, to define the parameter. |
Step 5 | Click the Save button. |
Stored Procedure Parameter Field Descriptions
Field Name | Description |
---|---|
Parameter Position | Position of this parameter within a list of parameters. |
Parameter Mode | Mode of this parameter.
|
Parameter Type | Type of parameter.
|
Value is Null | If Parameter Mode = Input or Input/Output; Value for the parameter is a database NULL value; applies to the input part of a stored procedure parameter. That is, if a value in a database is undefined, it is NULL, which means it has no set value. An input value can be NULL and is represented by selecting Value is Null. |
Input Value | If Parameter Mode = Input or Input/Output; Input value of the parameter (up to a maximum of 4000 characters), if any. |
Description | Description of this parameter. |
Variable Scope | If Parameter Mode = Output or Input/Output; applies to parameters associated with a task in a workflow. Scope of the variable to assign the Output value.
|
Variable Name | If Parameter Mode = Output or Input/Output; name of variable to assign the Output value. |
Deleting a Parameter
To delete a single parameter, either:
- Right-click the parameter on the Stored Procedure Parameters list and click Delete on the Action menu.
- Open the Stored Procedure Parameter record and click the Delete button.
Running a Stored Procedure Task
You can run a Stored Procedure task:
- Manually, by clicking the Launch Task or Launch Task with Variables button in the Stored Procedure tasks list or Stored Procedure Task Details Action menu.
- As part of a workflow.
- Specify triggers that run the task automatically based on times or events.
Monitoring Task Execution
You can monitor all system activity from the Activity Monitor and can view activity history from the History list.