Execute SQL Task

The Execute SQL Task functionality allows you to execute custom SQL commands using Stored Procedures.

Important: The Execute SQL Task only executes Stored Procedures available in the database. Any other SQL command will result in task failure.

To schedule an Execute SQL task, complete the following steps:

1. Go to Admin module > Task Scheduler group item > Dashboard group item link. This will open the Task Scheduler Dashboard.
2. Click the Execute SQL link in the Misc section.
3. On the Add Execute SQL Task form, complete the setup steps specific to this task.
4. Enter the SQL command in the Sql Command text box field.
   • The SQL command entered in the Sql Command field must obey the following conditions to succeed in task execution.
     • The Sql command text box on the Add Execute SQL Task form must start with the “Exec” command and be followed a stored procedure name after a space. Note that the text box values are not case-sensitive.
     • The SQL command must not contain a semi-colon (;).
     • The SQL command must not contain below words: SELECT, FROM, DELETE, INSERT, UPDATE, DROP, GRANT, REVOKE, DENY, BACKUP, ALTER, DROP, TRUNCATE, CREATE, RESTORE
   • If the above-mentioned conditions are not met while executing the task, the system will fail the task with the 'Task: Invalid SQL Command Method: An error occurred while trying to run ExecuteSQLTask: Invalid SQL Command' message.
   • Note: You can execute parameterized SQL Stored Procedure, for example, Exec co_customer_x_address_list @cst_key = N'<cst_key>'
5. The type of task being scheduled is listed in the Task Type field. This field is read-only and specific to the task you chose to schedule.
6. Enter the date (MM/DD/YYYY) and time (HH:MM:SS AM/PM) in the Run Task Datetime field. The date and time values must be separated by a space.
   • Clicking the Now link will schedule this task to run as per the value set in the ScheduledTaskDefaultDelayMinutes system option from the current time.
7. Expand the Execute After Task drop-down menu. This will display a list of all currently scheduled tasks. Select the task you wish the task you are currently scheduling to run after from the drop-down menu.
8. If you would like to be notified regarding the status of your scheduled task, select an Email template from the Notify Template drop-down menu.
9. Enter the Email address you wish the notification Email to be delivered to in the Notify E-Mail field.
   • If the user has an Email set on their profile, the Email listed on their profile will be pre-populated in this field.
10. Set the conditions that will send a notification Email by selecting the appropriate Notify Conditions checkboxes. The conditions you have available are:
    • On Start?
    • On Success?
    • On Failure?
    • On Completion With Errors?
11. The Recurrence field is a mandatory field. Select an option as required from the drop-down list.
12. Once you have completed setting up your scheduled task, click the Save button. This will open the Task Scheduler profile. You can view the status and history of the scheduled task on the Task Scheduler profile. 
13. To view the list of all the Execute SQL go to the Task Scheduler group item on the Admin module and click the List Tasks group item link.