How to create a SQL Server Agent Job (Transact-SQL) – with only needed fields


In this post you will see two major sections:

I. Detailed description for every needed field in system stored procedures.

II. Full working example related to above description. Here you need only to replace the names if the default configuration is suitable.

I. To create a SQL Server Agent job you need the folowing system stored procedures to be executed. Please note that the order of execution is important.

  1. Declarations and initialization
  2. Execute sp_add_job to create a job
  3. The name of the job. The name must be unique and cannot contain the percent (%) character. job_name is nvarchar(128), with no default

    Indicates the status of the added job. enabled is tinyint, with a default of 1 (enabled). If 0, the job is not enabled and does not run according to its schedule; however, it can be run manually.

    The description of the job. description is nvarchar(512), with a default of NULL. If description is omitted, “No description available” is used.

  4. Execute sp_add_jobstep to create one or more job steps
  5. The identification number of the job to which to add the step. job_id is uniqueidentifier, with a default of NULL.

    The sequence identification number for the job step. Step identification numbers start at 1 and increment without gaps. If a step is inserted in the existing sequence, the sequence numbers are adjusted automatically. A value is provided if step_id is not specified. step_id is int, with a default of NULL.

    The name of the step. step_name is sysname, with no default.

    The subsystem used by the SQL Server Agent service to execute command. subsystem is nvarchar(40), and can be one of these values.

    Value Description
    CMDEXEC Operating-system command or executable program
    DISTRIBUTION Replication Distribution Agent job
    SNAPSHOT Replication Snapshot Agent job
    LOGREADER Replication Log Reader Agent job
    MERGE Replication Merge Agent job
    QueueReader Replication Queue Reader Agent job
    ANALYSISQUERY Analysis Services query (MDX, DMX).
    ANALYSISCOMMAND Analysis Services command (XMLA).
    Dts Integration Services package execution
    PowerShell PowerShell Script
    TSQL‘ (default) Transact-SQL statement

    The commands to be executed by SQLServerAgent service through subsystem. command is nvarchar(max), with a default of NULL. SQL Server Agent provides token substitution that gives you the same flexibility that variables provide when you write software programs.

    The action to perform if the step succeeds. success_actionis tinyint, and can be one of these values.

    Value Description (action)
    1 (default) Quit with success
    2 Quit with failure
    3 Go to next step
    4 Go to step on_success_step_id

    The name of the database in which to execute a Transact-SQL step. database is sysname, with a default of NULL, in which case the master database is used. Names that are enclosed in brackets ([ ]) are not allowed. For an ActiveX job step, the database is the name of the scripting language that the step uses.

  6. Execute sp_add_schedule to create a schedule
  7. The name of the schedule. schedule_nameis sysname, with no default.

    Indicates the current status of the schedule. enabledis tinyint, with a default of 1 (enabled). If 0, the schedule is not enabled. When the schedule is not enabled, no jobs will run on this schedule.

    A value indicating when a job is to be executed. freq_typeis int, with a default of 0, and can be one of these values.

    Value Description
    1 Once
    4 Daily
    8 Weekly
    16 Monthly
    32 Monthly, relative to freq_interval
    64 Run when SQLServerAgent service starts
    128 Run when the computer is idle

    The days that a job is executed. freq_interval is int, with a default of 1, and depends on the value of freq_type.

    Value of freq_type Effect on freq_interval
    1 (once) freq_interval is unused.
    4 (daily) Every freq_interval days.
    8 (weekly) freq_interval is one or more of the following (combined with an OR logical operator):

    1 = Sunday

    2 = Monday

    4 = Tuesday

    8 = Wednesday

    16 = Thursday

    32 = Friday

    64 = Saturday

    16 (monthly) On the freq_interval day of the month.
    32 (monthly relative) freq_interval is one of the following:

    1 = Sunday

    2 = Monday

    3 = Tuesday

    4 = Wednesday

    5 = Thursday

    6 = Friday

    7 = Saturday

    8 = Day

    9 = Weekday

    10 = Weekend day

    64 (when SQLServerAgent service starts) freq_interval is unused.
    128 freq_interval is unused.

    Specifies the units for freq_subday_interval. freq_subday_typeis int, with a default of 0, and can be one of these values.

    Value Description (unit)
    0x1 At the specified time
    0x2 Seconds
    0x4 Minutes
    0x8 Hours

    The number of freq_subday_type periods to occur between each execution of a job. freq_subday_intervalis int, with a default of 0. freq_subday_interval is ignored in those cases where freq_subday_type is equal to 1. Note: Interval should be longer than 10 seconds.

    The date on which execution of a job can stop. active_end_dateis int, with a default of 99991231, which indicates December 31, 9999. Formatted as YYYYMMDD.

    The time on any day between active_start_date and active_end_date to begin execution of a job. active_start_timeis int, with a default of 000000, which indicates 12:00:00 A.M. on a 24-hour clock, and must be entered using the form HHMMSS.

    The name of the server principal that owns the schedule. owner_login_name is sysname, with a default of NULL, which indicates that the schedule is owned by the creator.

  8. Execute sp_attach_schedule to attach a schedule to the job
  9. Execute sp_add_jobserver to set the server for the job
  10. Optional you can delete the job
  11. Either job_id or job_namemust be specified; both cannot be specified.

Here you can find more datails about rest of the fields from system stored procedures.

A real example from our projects:

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.

By continuing to use the site, you agree to the use of cookies. More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.