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: