Icon CREATE JOB

Creates a new job.

Syntax
CREATE JOB <Name>
RUN AS <UserName>
FROM <StartDate> TO <EndDate>
<IntervalDefinition>
[CATEGORY <CategoryName>]
<BodyDefinition>
[DESCRIPTION <Description>]
[VERSION <VersionNumber>]
[ATTRIBUTES <CustomAttributes>]

<BodyDefinition> =

BEGIN
   [<Declaration>;]
   [<Declaration>;]
   [<Statement>;]
   [<Statement>;]
[EXCEPTION
   [<Statement>;]]
END

<IntervalDefinition> =

<IntervalType>|<SpecificInterval>

<IntervalType> =

ONCE|HOURLY|DAILY|WEEKLY|MONTHLY|AT SERVER START
[ON <DaysDefinition>|ON <DaysDefinition> OF <MonthsDefinition>
BETWEEN <StartTime> AND <EndTime>]

<SpecificInterval> =

EVERY <Interval> MINUTES|HOURS|DAYS|WEEKS
ON <DaysDefinition>
BETWEEN <StartTime> AND <EndTime>

HOURLY/DAILY/WEEKLY/EVERY <Interval> MINUTES/HOURS/WEEKS Interval

<DaysDefinition> =

[MON] [,TUE] [,WED]....

MONTHLY Interval

<DaysDefinition> =

DAY <DayNumber>|
FIRST|SECOND|THIRD|FOURTH|LAST MON|TUE|WED|THU|FRI|SAT|SUN

<DayNumber> = 1..31

<MonthsDefinition> =

[JAN] [,FEB] [,MAR] [,APR]...

Usage
Use this statement to create a new job. Jobs are configuration-level procedures that are executed, by default, within the context of the system-defined Configuration database as the current database. Jobs accept no parameters and cannot return cursors or values.

Examples
-- The following job backs up all tables in all databases
-- defined in the current system at 11:00 PM every evening.

CREATE JOB Backup
RUN AS "System"
FROM DATE '2006-01-01' TO DATE '2010-12-31'
DAILY
BETWEEN TIME '11:00 PM' AND TIME '11:30 PM'
CATEGORY 'Backup'
BEGIN
   DECLARE DBCursor CURSOR FOR DBStmt;
   DECLARE DBName VARCHAR DEFAULT '';

   PREPARE DBStmt FROM 'SELECT * FROM Databases';

   OPEN DBCursor;

   FETCH FIRST FROM DBCursor ('Name') INTO DBName;

   WHILE NOT EOF(DBCursor) DO
      IF (DBName <> 'Configuration') THEN
         EXECUTE IMMEDIATE 'BACKUP DATABASE "' + DBName + '" AS "' +
                           CAST(CURRENT_DATE AS VARCHAR(10)) +
                           '-' + DBName + '" TO STORE "Backups" INCLUDE CATALOG';
      END IF;
      FETCH NEXT FROM DBCursor ('Name') INTO DBName;
   END WHILE;

   CLOSE DBCursor;
END

Required Privileges
The current user must be granted the system-defined Administrators role in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
ExtensionThis SQL statement is an ElevateDB extension.
Image