CREATE JOB, ALTER JOB, DROP JOB 

Jobs are SQL scripts which can call stored-procedures to do work with your data. Jobs are run by the Orixa Server at times defined by the Administrator.

Jobs are used for

1. Running a backup of your database on a regular basis, to ensure that data is saved.
2. Running a save updates process to save changes you have made to your data into an updates file which can be transferred to another instance of your Orixa App.
3. Copying files between different stores in your Orixa system.
4. Running stored procedures to do other work such as importing or exporting data to or from your Orixa App.

Small points to remember when creating Jobs

• Jobs will not run if the server is switched off at the time-set for them to run.
• Be careful when setting a Job to run "AT SERVER START", some programmes or services may not have set up by the time the Job runs.
• Avoid setting start-times to "simple" times (like 17:00:00) as if several Jobs start at the same time the server may slow down.

SQL Syntax

CREATE JOB [JobName]
RUN AS [UserName, usually "Administrator"]
--date range for activity of JOB
FROM [DATE '2021-01-01'] TO [DATE '2022-01-01']
--define when the job should run
ONCE|HOURLY|DAILY|WEEKLY|MONTHLY|AT SERVER START
--if the job runs DAILY list which days
DAILY ON MON, TUE, WED, THU, FRI, SAT, SUNDAY
--define the hours in the day when the job should run
EVERY [number] MINUTES|HOURS|DAYS|WEEKS
--if the job runs monthly you can define the months when the job should run
MONTHLY ON JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, NOV, DEC
--or set specific days for each month
MONTHLY ON DAY [number between 1 and 31]
BETWEEN [TIME '12:00:00'] AND [TIME '23:59:59']
BEGIN

--Add SQL Script to USE different database and CALL Stored Procedures.
END  
DESCRIPTION 'Optional description'  
VERSION [optional number]  
ATTRIBUTES 'optional attributes text'

To ALTER a JOB, just replace the "CREATE" keyword in the statement above with "ALTER". To permanently remove/delete a JOB call:

DROP JOB [JobName]

Examples

CREATE JOB "RegularBackupAndPruning"
RUN AS "System"
FROM DATE '2015-07-13' TO DATE '2025-11-15'
WEEKLY ON MON, TUE, WED, THU, FRI
BETWEEN TIME '11:16:26' AND TIME '11:20:26.999'
BEGIN  
  USE "SystemDB";
  CALL Maintenance_BackUpDatabase (true, true);
  CALL Maintenance_RenameBUFilesOLD();
  CALL Maintenance_DeleteOLDFiles();
  CALL Maintenance_EmptyTempFolder();
END
DESCRIPTION 'Daily job to maintain basic systems'
VERSION 1.00

Note the use of the specialized SQL keywords "CALL" and "USE" in the above example. USE allows the Developer to set the focus of the Job onto a particular database, CALL allows the Developer to name a specific stored procedure, and pass in parameters to it.