SQL Task
With SQL tasks, you can perform automated calculations on the database, aggregate data or enrich data in the database
Example: Calculate purchase metrics based on information in the ORDERS and ORDERLINE tables which does not contain aggregate data.
NOTE: Stored Procedures created directly within SQLServer are not available for use in Selligent tasks.
When creating a new SQL task, the following window is displayed.
In this dialog, new procedures are added and existing ones are edited or removed. Click the 'Add' button to add a new task. The Procedure dialog pops up:
- Procedure — Select the Stored Procedure to execute. Only registered Stored Procedures are listed.
- Description — Enter a description of what the procedure does
- Generate statistics — Flag the check box when required. An XML file is generated.
NOTE: Statistics are generated in XML through a standard SQL server function. This function checks each operation and should only be used in case problems occur and troubleshooting is a necessity. It is not recommended to set this option active all the time.
- Fail constraint — This option is used in combination
with the 'OnSuccess' and
'OnFail' column in the list
of tasks making up the SQL job.
When a Stored Procedure is successful, by default value 0 is returned. In the 'Fail constraint' section, the user defines when the procedure is considered as failed. (Example: if the return value of the Stored Procedure <>1, the job has failed. This implies indirectly that the task is considered successful when the returned value=1.) This information is later used in the sequence of tasks, to determine the next steps. - Parameters — When the Stored Procedure requires parameters, these are listed within the 'Parameters' section. Parameters can be of type INPUT or OUTPUT. The value of an OUTPUT parameter can be set as a variable and this variable can on its turn be used as an INPUT parameter in another Stored Procedure.
Variables are set as following: @VARNAME
NOTE: Variables can only be used within the context of one and the same SQL task and can only be passed on to other Stored Procedures within the same SQL task.
Parameters can also be assigned a value. Click the [...] button in the Value column to enter a predefined value or a user value.
Example:
The Value editor for a parameter of type Datetime:
A predefined value would be GETDATE()
A user values would be a date and time selected by the user.
When more than one procedure is selected a sequence can by defined by moving these procedures up and down in the list.
Additionally, it is possible to define a kind of logic. When a procedure is successful, the user can indicate if the complete job fails or passes, or if the next step has to be executed. The same options exist when a procedure fails.
NOTE: If the job is set as 'Pass' when a task succeeds, the remainder of the tasks within the same job is not executed. The job is considered as done. If the job is set as 'Fail', the SQL job is terminated reporting a failure.
