Overview
The SQL task allows you to execute one or a series of SQL statements against the database specified in the task.
Note
Before you can run a SQL 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 SQL task to pass data where appropriate:
Creating a SQL Task
Step 1 | From the Automation Center navigation pane, select Tasks > SQL Tasks. The SQL Tasks list displays a list of all currently defined SQL tasks. |
---|---|
Step 2 | Enter/select Details for a new SQL 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 Save 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).
SQL Task Details
The following SQL Task Details is for an existing SQL task.
Depending on the values that you enter / select for these fields, and whether or not the SQL 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 SQL Task Details.
SQL Task Details Field Descriptions
The following table describes the fields, buttons, and tabs that display in the SQL 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. |
SQL 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. |
SQL Command | 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. |
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. |
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 SQL Task Instance
When a SQL 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 SQL Task Details for that task
- Activity Monitor
- Task Instances list
SQL Task Instance Details
The following SQL Task Instance Details contains information on the execution of a SQL task.
SQL Task Instance Details Field Descriptions
The following table describes the fields, buttons, and tabs that display in the SQL 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. |
SQL 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. |
SQL Command | 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. |
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. |
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. |
Running a SQL Task
You can run a SQL task:
- Manually, by clicking the Launch Task or Launch Task with Variables button in the SQL tasks list or SQL 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.