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. Below the list, Stored Procedure Task Details for a new Stored Procedure task displays.
Step 2	Enter/select Details for a new Stored Procedure 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 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	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.
Stored Procedure 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).
Stored Procedure Name	Name of the file containing the stored procedure being executed against the database. Variables supported.
Result Processing Details	This section contains assorted detailed information about result processing for this task.
Result Processing	Specifies how the Controller should determine whether the Stored Procedure failed or completed successfully. Options: Skip Result Processing. Success Exitcode Range - The Stored Procedure is considered completed successfully if its exit code falls within the range specified in the Exit Codes field. Failure Exitcode Range - The Stored Procedure is considered failed if its exit code falls within the range specified in the Exit Codes field. Success Result Set Contains - The Stored Procedure is considered completed successfully depending on the value in a specific database column (see Column Name, Operator, and Value fields). Failure Result Set Contains - The Stored Procedure is considered failed depending on the value in a specific database column (see Column Name, Operator, and Value fields). Success Output Parameter - The Stored Procedure is considered completed successfully if its output parameter satisfies the condition specified in the associated Parameter Position, Operator, and Value fields. Failure Output Parameter - The Stored Procedure is considered failed if its output parameter satisfies the condition specified in the associated Parameter Position, 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.
Parameter Position	If Result Processing = Success Output Parameter or Failure Output Parameter; position of this parameter within a list of parameters.
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: All Actions (Abort, Email Notification, Set Variable, SNMP Notification, and System Operation) defined for the task instance on a Failed status. 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: - - System Default - - Apply the Wait To Start and Delay On Start specifications as defined by the System Default Wait/Delay Workflow Only system property. (Default is yes.) Yes Apply the Wait To Start and Delay On Start specifications only if the task is in a Workflow. No Apply the Wait To Start and Delay On Start specifications whether or not the task is in a Workflow.
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 Details.
Late Start Type	Required if Late Start is enabled. Options: Time - Flag the task if it starts after the specified time. Duration - Flag the task if it starts a certain amount of time after the programmed start time. The task must have a specific start time.
Late Start Time	If Late Start Type = Time; Time after which the task start time is considered late. Use HH:MM, 24-hour time.
Late Start Day Constraint	If Late Start Type = Time; Specification for whether or not to advance the late start time to another day. Valid values: -- None -- Advance to the next day if the specified late start time is before the Created time of the 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. Nth Day Advance to a specific number of days in the future. Default is – None --.
Late Start Nth Amount	If Late Start Day Constraint = Nth Day; Number of days to advance.
Late Start Duration	If Late Start Type = Duration; Duration (amount of relative time) after which the task is considered to have started late. For a task within a workflow, the duration is the period between the time the workflow starts and the time the task itself starts. For example, a task might have a Late Start Duration of 60 minutes. If the workflow starts at 9:00 a.m. but the task itself does not start until 10:30, the task has started late. For a task that is not within a workflow, Late Start Duration has meaning only if the task has been held upon starting. For example, if a task has a Late Start Duration of 60 minutes and the Hold on Start field is enabled, if the task is not released from hold within the amount of time specified in the Late Start Duration field, the task has started late.
Late Finish	If enabled, and if the task instance finishes after the time or period specified, the task instance is flagged as late. You can specify a time or duration to determine a late finish (see Late Finish Type). To determine whether a task instance finished late, open the task instance and locate the Finished Late field; the field is checked if the instance finished after the specified time or lasted longer than expected. This field only appears on the task instance if the user specified a Late Finish in the task definition.
Late Finish Type	Required if Late Finish is enabled. Options: Time - Flag the task if it finishes after the specified time (see Late Finish Time). Duration - Flag the task if it finishes a certain amount of time after the programmed finish time (see Late Finish Duration). The task must have a specific finish time. Average Duration - Flag the task if it finishes before the average duration (see Average Instance Time) for the task, less an offset (see Late Finish Offset Type), if specified.
Late Finish Offset Type	If Late Finish Type = Average Duration; Options: Percentage Duration
Late Finish Percentage Offset ( + )	Required if Late Finish Offset Type = Percentage; Percentage of Average Duration to use as an offset. The late finish time is calculated by adding the offset to the Average Duration.
Late Finish Duration Offset ( + )	Required if Late Finish Offset Type = Duration; Duration to use as an offset. The late finish time is calculated by adding the offset to the Average Duration.
Late Finish Duration Offset Unit	If Late Finish Offset Type = Duration; Options: Seconds Minutes Hours
Late Finish Time	If Late Finish Type = Time; Time after which the task finish time is considered late. Use HH:MM, 24-hour time.
Late Finish Day Constraint	If Late Finish Type = Time; Specification for whether or not to advance the late finish time to another day. Valid values: -- None -- Advance to the next day if the specified late finish time is before the Created time of the 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. Nth Day Advance to a specific number of days in the future. Default is – None --.
Late Finish Nth Amount	If Late Finish Day Constraint = Nth Day; Number of days to advance.
Late Finish Duration	If Late Finish Type = Duration; Longest amount of time this task instance should take to run.
Early Finish	If enabled, and if the task instance finishes before the time or period specified, the task instance is flagged as early. You can specify a time or duration to determine an early finish (see Early Finish Type). To determine whether a task instance finished early, open the task instance and locate the Finished Early field; the field is checked if the instance finished before the specified time or did not last as long as expected. This field only appears on the task instance if the user added Early Finish specifications to the task definition.
Early Finish Type	Required if Early Finish is enabled. Options: Time - Flag the task if it finishes before the specified time (see Early Finish Time). Duration - Flag the task if it finishes a certain amount of time before the programmed finish time (see Early Finish Duration). The task must have a specific finish time. Average Duration - Flag the task if it finishes before the average duration (see Average Instance Time) for the task, less an offset (see Early Finish Offset Type), if specified.
Early Finish Offset Type	If Early Finish Type = Average Duration; Options: Percentage Duration
Early Finish Percentage Offset ( - )	Required if Early Finish Offset Type = Percentage; Percentage of Average Duration to use as an offset. The early finish time is calculated by subtracting the offset from the Average Duration.
Early Finish Duration Offset ( - )	Required if Early Finish Offset Type = Duration; Duration to use as an offset. The early finish time is calculated by subtracting the offset from the Average Duration.
Early Finish Duration Offset Unit	If Early Finish Offset Type = Duration; Options: Seconds Minutes Hours
Early Finish Time	If Early Finish Type = Time; Time before which the task finish time is considered early. That is, enter a time at which the task should still be running. Use HH:MM, 24-hour time.
Early Finish Day Constraint	If Early Finish Type = Time; Specification for whether or not to advance the early finish time to another day. Valid values: -- None -- Advance to the next day if the specified early finish time is before the Created time of the 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. Nth Day Advance to a specific number of days in the future. Default is – None --.
Early Finish Nth Amount	If Early Finish Day Constraint = Nth Day; Number of days to advance.
Early Finish Duration	If Early Finish Type = Duration; Shortest amount of time this task instance should take to run.
User Estimated Duration	Required if Early Finish Type or Late Finish Type = Average Duration; Estimated amount of time it should normally take to run this task. The Controller uses this information to calculate the User Estimated End Time on a task instance record. User Estimated Duration is used when the Average Duration is not available; for example, on the first launch of a task.
Critical Path Options	This section contains Critical Path-related specifications for the task.
CP Duration	Optional; Allows you to override the estimated Critical Path Duration of the task when running in a Workflow; used in conjunction with the CP Duration Unit field. In most cases, this field should be left blank, which implies that the Controller will estimate the Critical Path Duration based on historical executions. Valid values are any integer equal to or greater than 0. Variables and Functions are supported.
CP Duration (Resolved)	Displays the current resolved value of the CP Duration field, which may contain variables or functions that will be displayed as unresolved until the task instance starts. The CP Duration (Resolved) field can continue to change value until the task instance starts, at which time CP Duration will display as resolved and CP Duration (Resolved) will no longer be visible unless there was an issue resolving the variables and/or functions contained within CP Duration. If the Controller is unable to resolve CP Duration or it resolves to an invalid value, CP Duration will be ignored and the Controller will estimate the Critical Path Duration based on historical executions.
CP Duration Unit	Type of CP Duration; used in conjunction with the CP Duration field. For example, for a CP Duration of two minutes, specify 2 in the CP Duration field and select Minutes in this field. Options: Seconds Minutes Hours Default is Minutes.
Workflow Execution Options	This section contains Execution Restriction specifications for the task if it is within a Workflow.
Execution Restriction	Specification for whether or not there is a restriction for this task to be run, skipped, or held. Options are: -- None -- No restriction for this task. Run Restriction for when this task will be run. Skip Restriction for when this task will be skipped. Hold Restriction for when this task will be held. If Execution Restriction on a task is Run or Skip, then when it is part of a Workflow that is being launched, the Restriction Period is evaluated. The task instance will be skipped if Execution Restriction is Skip and the date is within the Restriction Period or Execution Restriction is Run and the date is not within the Restriction Period. Execution Restriction can be set to Skip with a Restriction Period of - None -, meaning the restriction is always active and the task will be skipped when it is part of a Workflow.
Restriction Period	If Execution Restriction = Run, Skip, or Hold; Period of time when the task is restricted. Options are: – None – No period of restriction for this task. Before Restriction is valid if the date is before the Before Date value. After Restriction is valid if the date is after the After Date value. Span Restriction is valid if the date is before the Before Date value and after After Date value. On Restriction is valid if the date is one of the Date List values.
Before Date	If Restriction Period = Before or Span; Date before which the restriction is valid.
Before Time	If Restriction Period = Before or Span; Time on the selected date before which the restriction is valid.
After Date	If Restriction Period = After or Span; Date after which the restriction is valid.
After Time	If Restriction Period = After or Span; Time on the selected date after which the restriction is valid.
Date List	If Restriction Period = On; Date(s) on which the restriction is valid.
Statistics	This section contains time-related statistics for task instances of the task.
First Execution	System-supplied; End Time of the first instance of this task to complete.
Last Execution	System-supplied; End Time of the last instance of this task to complete.
Last Instance Duration	System-supplied; Amount of time the task took to run the last time it ran.
Lowest Instance Time	System-supplied; Lowest amount of time this task has taken to run.
Average Instance Time	System-supplied; Average amount of time this task takes to run.
Highest Instance Time	System-supplied; Highest amount of time this task has taken to run.
Number of Instances	System-supplied; Number of instances in the database for this task.
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	Saves updates to the record.
Launch Task	Manually launches the task.
View Parents	Displays a list of any parent Workflow tasks for this task.
Copy	Creates a copy of this task, which you are prompted to rename.
Delete	Deletes the current record. Note You cannot delete a task if it is either: Specified in an enabled Trigger. The only task specified in a