/
SQL Task

SQL Task

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.
 
To the right of list, SQL Task Details for a new SQL task displays.
 

Step 2

Enter/select Details for a new SQL task, using the field descriptions below as a guide.

  • Required fields display an asterisk ( * ) after the field name.
  • Default values for fields, if available, display automatically.

To display more of the Details fields on the screen, you can either:

  • Use the scroll bar.
  • Temporarily hide the list above the Details.
  • Click the  button above the list to display a pop-up version of the Details.

Step 3

Click the  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

User-defined name of this task (Maximum = 255 alphanumeric characters); variables supported. It is the responsibility of the user to develop a workable naming scheme for tasks.

Version

System-supplied; version number of the current record, which is incremented by the Controller every time a user updates a record. Click the Versions tab to view previous versions. For details, see Record Versioning.

Description

Description of this record. Maximum length is 255 characters.

Member of Business Services

User-defined; Allows you to select one or more Business Services that this record belongs to.  (You also can Check All or Uncheck All Business Services for this record.)

You can select up to 62 Business Services for any record type, and enter a maximum of 2048 characters for each Business Service.

If the Business Service Visibility Restricted Universal Controller system property is set to true, depending on your assigned (or inherited) Permissions or Roles, Business Services available for selection may be restricted.

Resolve Name Immediately

If enabled, the Instance Name of the task instance will be resolved immediately at trigger/launch time.

Time Zone Preference

User-defined; Allows you to specify the time zone that will be applied to the task.

Options:

  • – System Default –
    Time zone is based on the value of the Task Time Zone Preference Universal Controller system property: Server or Inherited.
  • Server (xxx)
    Where (xxx) is the time zone ID of the server; time zone is evaluated in the time zone of the server.
  • Inherited
    Time zone is evaluated in the time zone of the Parent Workflow or Trigger / Launch specification in the case there is no Parent Workflow.

Hold on Start

If enabled, when the task is launched it appears in the Activity Monitor with a status of Held. The task runs when the user releases it.

Hold Reason

Information about why the task will be put on hold when it starts.

Virtual Resource Priority

Priority for acquiring a resource when two or more tasks are waiting for the resource. This priority applies to all resources required by the task.

Options: 1 (high) - 100 (low).

Default is 10.

Hold Resources on Failure

If enabled, the task instance will continue to hold Renewable resources if the task instance fails. Renewable resources will be returned only if the task instance status is either Complete, Finished, or Skipped.

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. 

Simulate

Specifies if the instance should execute under simulation mode

Override Previous Instance Wait

Specifies whether or not to override the parent workflow's Previous Instance Wait configuration.

This option only applies for an instance running within a workflow.

Options: 

  • No

    Behavior determined by the parent workflow configuration.
  • Yes / -- None --

    Regardless of the parent workflow configuration, the task instance will never wait for a previous instance to complete.
  • Yes / Wait for Last

    Regardless of the parent workflow configuration, the task instance will remain in Instance Wait until the most recent prior instance of the same task has completed.
  • Yes / Wait for Last / Same Workflow

    Regardless of the parent workflow configuration, the task instance will remain in Instance Wait until the most recent prior instance of the same task, within an instance of the same workflow, have completed.
  • Yes / Wait for All

    Regardless of the parent workflow configuration, the task instance will remain in Instance Wait until all prior instances of the same task has completed.
  • Yes / Wait for All / Same Workflow

    Regardless of the parent workflow configuration, the task instance will remain in Instance Wait until all prior instances of the same task, within an instance of the same workflow, have completed.

SQL Details

This section contains assorted detailed information about the task.

Database Connection


Name of the Universal Controller Database Connection that defines the database. Select a database from the drop-down list or click the icon to create a new database connection.

Database Connection Variable


Indication of whether the Database Connection field is a reference field for selecting a specific Database Connection (unchecked) or a text field for specifying the Database Connection as a variable (checked). Use the format:



The variable must be a supported type as described in Variables and Functions.
 

Note

When updating multiple Tasks, to change from using a Database Connection reference to using a Database Connection variable, you must change the Database Connection Variable field to Yes and specify the Database Connection variable in the Database Connection Unresolved field. Conversely, to change from using a Database Connection variable to using a Database Connection reference, you must change the Database Connection Variable field to No and specify the Database Connection reference in the Database Connection field.


Credentials


Credentials that specify the user and password for connecting to the database.

These Credentials override any Credentials specified on the Database Connection.

If Credentials are not specified in the Database Connection, you must specify them in the task.


Credentials Variable

Indication of whether the Credentials field is a reference field for selecting a specific Credential (unchecked) or a text field for specifying the Credential as a variable (checked). Use the format: ${variable name}.

The variable must be a supported type as described in Variables and Functions.
 

Note

When updating multiple Tasks, to change from using a Credentials reference to using a Credentials variable, you must change the Credentials Variable field to Yes and specify the Credentials variable in the Credentials Unresolved field. Conversely, to change from using a Credentials variable to using a Credentials reference, you must change the Credentials Variable field to No and specify the Credentials reference in the Credentials field.

Maximum Rows


If necessary, specifies a limit to the number of rows you want returned by the SQL/Stored Procedure statement. This value overrides any value you specify in the database connection.

(A default limit can be specified by the SQL/Stored Procedure Maximum Rows Universal Controller system property.)

If you set Maximum Rows to 0, no content from any of the ResultSets will be retrieved (the next() method on ResultSet will not be called). Each ResultSet will be closed, but no data or rows accessed.

Auto Cleanup

When data is retrieved as the result of a SQL task, the data is written into a database table. If Auto Cleanup is enabled, the data is discarded upon the successful completion of the task (or workflow if the task is contained within a workflow).

SQL Command


SQL command being executed against the database. Variables supported.

Result Processing DetailsThis section contains assorted detailed information about result processing for this task.

Result Processing

Specifies how the Controller should determine whether the SQL command failed or completed successfully.

Options:

  • Skip Result Processing
  • Success Exitcode Range - The SQL command is considered completed successfully if its exit code falls within the range specified in the Exit Codes field.
  • Failure Exitcode Range - The SQL command is considered failed if its exit code falls within the range specified in the Exit Codes field.
  • Success Result Set Contains - The SQL command is considered completed successfully depending on the value in the first row of a specific database column (see Column Name, Operator, and Value fields).
  • Failure Result Set Contains - The SQL command is considered failed depending on the value in the first row of a specific database column (see Column Name, Operator, and Value fields).

Exit Codes

Required if Result Processing = Success Exitcode Range or Failure Exitcode Range. Specifies the range. Format: Numeric. Use commas to list a series of exit codes; use hyphens to specify a range. Example: 1,5, 22-30.

Column Name

Required if Result Processing = Success Result Set Contains or Failure Result Set Contains. Specifies the name of a database column that is being checked for a specific value.

Operator

Operator being used for the comparison. Options: =, !=, >, >=, <, <=, regex.

Note

For operators >, >=, <, and <=, if the values being compared are whole numbers or decimal numbers between -9223372036854775808 and 9223372036854775807, they will be compared as numbers; otherwise, they will be compared as text lexicographically.

Value

Value being compared, using the operator specified.

Retry Options

This section contains specifications for retrying the task.

Maximum Retries

User-defined; maximum number of times that the Controller should retry this task after it has started and gone to a failed state.

Retry Indefinitely

User-defined; indicates whether the Controller should continue trying indefinitely to run this task. If you enable this field, it overrides any value placed in the Maximum Retries field.

Retry Interval (Seconds)

User-defined; number of seconds between each retry.

Suppress Intermediate Failures

User-defined; If the task instance is in the Failed status, indicates whether or not the following will be suppressed until all scheduled retry attempts (a Maximum Retries value has been entered or Retry Indefinitely has been enabled) have been made:

  • Workflow conditional path processing; any Successors waiting on a failure path will not be released.

  • Task Monitors will not be notified of the Failed status. Also, any Task Monitor task that has a Time Scope in the past will disqualify any matching task instance in the past with a Failed status if the task instance is scheduled for automatic retry and for which Suppress Intermediate Failures has been enabled.

  • Any Workflow containing the Failed task instance will not transition to the Running/Problems status.


Wait / Delay Options

This section contains specifications for waiting to start and/or delaying on start the task.

Wait To Start

Amount of time to wait before starting a task from the time that it was launched.
 
Options are:

  • – None –
  • Time
  • Relative Time
  • Duration
  • Seconds

Wait Time

If Wait To Start = Time or Relative Time; Time of day (in 24-hour time) to wait until before starting the task. 

Wait Day Constraint

If Wait To Start = Time or Relative Time; Specification for whether or not to advance the wait time to another day.

Valid values:

  • -- None --
    • If Wait To Start = Time; Advance to the next day if the specified wait time is before the time that the task instance is eligible to start; that is, all dependencies have been met. For example: it is not being held, and it is not waiting on any predecessors.
    • If Wait To Start = Relative Time; Advance to the next day if the specified wait time is before the task instance Trigger Time or, if there is no Trigger Time, before the task instance Launch Time. In the latter case, when a task instance is within a workflow, it will inherit the Launch Time of the top-level parent workflow task instance.
  • Same Day
    Do not advance day.
  • Next Day
    Advance to the next day.
  • Next Business Day
    Advance to the next business day.
  • Sunday
    If today is not Sunday, advance to next Sunday.
  • Monday
    If today is not Monday, advance to next Monday.
  • Tuesday
    If today is not Tuesday, advance to next Tuesday.
  • Wednesday
    If today is not Wednesday, advance to next Wednesday.
  • Thursday
    If today is not Thursday, advance to next Thursday.
  • Friday
    If today is not Friday, advance to next Friday.
  • Saturday
    If today is not Saturday, advance to next Saturday.

Default is – None --.

Wait Duration

If Wait To Start = Duration; Number of days, hours, minutes, and seconds to wait before starting the task.

Wait Duration In Seconds

If Wait To Start = Seconds; Number of seconds to wait before starting the task.

Delay On Start

Amount of time to delay the start of a task, after it has been launched, from the time that it is eligible to start; that is, all dependencies have been met. For example: it is not being held, it is not waiting on any predecessors, or there is no wait time specified.
 
Options are:

  • – None –
  • Duration
  • Seconds

Delay Duration

If Delay On Start = Duration; Number of days, hours, minutes, and seconds to delay after starting the task.

Delay Duration In Seconds

If Delay On Start = Seconds; Number of seconds to delay after starting the task.

Workflow Only

Specification for whether or not to apply the Wait To Start and Delay On Start specifications only if the task is in a Workflow.
 
Options are:

Time Options

This section contains time-related specifications for the task.

Late Start

If enabled, and if the task instance starts after the time or period specified, the task instance is flagged as late. You can specify a time or duration to determine a late start (see Late Start Type). To determine whether a task instance started late, open the task instance and locate the Started Late field; the field is checked if the instance started after the specified time. The Started Late field displays in the task instance Details only if the user specified a Late Start in the task Deta