sp_update_job (T-SQL)

Changes the attributes of a job.

Syntax

sp_update_job [@job_id =] job_id | [@job_name =] 'job_name'
    [, [@new_name =] 'new_name']
    [, [@enabled =] enabled]
    [, [@description =] 'description']
    [, [@start_step_id =] step_id]
    [, [@category_name =] 'category']
    [, [@owner_login_name =] 'login']
    [, [@notify_level_eventlog =] eventlog_level]
    [, [@notify_level_email =] email_level]
    [, [@notify_level_netsend =] netsend_level]
    [, [@notify_level_page =] page_level]
    [, [@notify_email_operator_name =] 'email_name']
    [, [@notify_netsend_operator_name =] 'netsend_operator']
    [, [@notify_page_operator_name =] 'page_operator']
    [, [@delete_level =] delete_level]
    [, [@automatic_post =] automatic_post]

Arguments

[@job_id =] job_id

Is the identification number of the job to be updated. job_id is uniqueidentifier, with a default of NULL.

[@job_name =] 'job_name'

Is the name of the job. job_name is sysname, with a default of NULL.

Note Either job_id or job_name must be specified, but both cannot be specified.

[@new_name =] 'new_name'

Is the new name for the job. new_name is sysname, with a default of NULL.

[@enabled =] enabled

Specifies whether the job is enabled (1) or not abled (0). enabled is tinyint, with a default of NULL.

[@description =] 'description'

Is the description of the job. description is nvarchar(512), with a default of NULL.

[@start_step_id =] step_id

Is the identification number of the first step to execute for the job. step_id is int, with a default of NULL.

[@category_name =] 'category'

Is the category of the job. category is sysname, with a default of NULL.

[@owner_login_name =] 'login'

Is the name of the login that owns the job. login is sysname, with a default of NULL. Only members of the sysadmin fixed server role can change job ownership.

[@notify_level_eventlog =] eventlog_level

Specifies when to place an entry in the Microsoft® Windows NT® application log for this job. eventlog_level is int, with a default of NULL, and can be one of these values.

 

Value	Description (action)
0	Never
1	On success
2	On failure
3	Always

[@notify_level_email =] email_level

Specifies when to send an e-mail upon the completion of this job. email_level is int, with a default of NULL. email_level uses the same values as eventlog_level.

[@notify_level_netsend =] netsend_level

Specifies when to send a network message upon the completion of this job. netsend_level is int, with a default of NULL. netsend_level uses the same values as eventlog_level.

[@notify_level_page =] page_level

Specifies when to send a page upon the completion of this job. page_level is int, with a default of NULL. page_level uses the same values as eventlog_level.

[@notify_email_operator_name =] 'email_name'

Is the e-mail name of the person to whom the e-mail is sent when email_level is reached. email_name is sysname, with a default of NULL.

[@notify_netsend_operator_name =] 'netsend_operator'

Is the name of the operator to whom the network message is sent. netsend_operator is sysname, with a default of NULL.

[@notify_page_operator_name =] 'page_operator'

Is the name of the operator to whom a page is sent. page_operator is sysname, with a default of NULL.

[@delete_level =] delete_level

Specifies when to delete the job. delete_value is int, with a default of NULL. delete_level uses the same values as eventlog_level.

[@automatic_post =] automatic_post

Reserved.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

sp_update_job must be run from the msdb database.

sp_update_job changes only those settings for which parameter values are supplied. If a parameter is omitted, the current setting is retained.

Permissions

Execute permissions default to the public role.

Examples

This example changes the name and description, and disables the job Archive Tables.

USE msdb

EXEC sp_update_job @job_name  = 'Archive Tables',

    @new_name = 'Archive Tables - Disabled'

    @description = 'Job disabled until end of project'

    @enabled = 0

See Also