Edit

Share via

sp_update_job (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Updates the attributes of an existing job created in the SQL Server Agent service.

Transact-SQL syntax conventions

Syntax

sp_update_job
    [ [ @job_id = ] 'job_id' ]
    [ , [ @job_name = ] N'job_name' ]
    [ , [ @new_name = ] N'new_name' ]
    [ , [ @enabled = ] enabled ]
    [ , [ @description = ] N'description' ]
    [ , [ @start_step_id = ] start_step_id ]
    [ , [ @category_name = ] N'category_name' ]
    [ , [ @owner_login_name = ] N'owner_login_name' ]
    [ , [ @notify_level_eventlog = ] notify_level_eventlog ]
    [ , [ @notify_level_email = ] notify_level_email ]
    [ , [ @notify_level_netsend = ] notify_level_netsend ]
    [ , [ @notify_level_page = ] notify_level_page ]
    [ , [ @notify_email_operator_name = ] N'notify_email_operator_name' ]
    [ , [ @notify_netsend_operator_name = ] N'notify_netsend_operator_name' ]
    [ , [ @notify_page_operator_name = ] N'notify_page_operator_name' ]
    [ , [ @delete_level = ] delete_level ]
    [ , [ @automatic_post = ] automatic_post ]
[ ; ]

Arguments

[ @job_id = ] 'job_id'

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

Either @job_id or @job_name must be specified, but both can't be specified.

[ @job_name = ] N'job_name'

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

Either @job_id or @job_name must be specified, but both can't be specified.

[ @new_name = ] N'new_name'

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 enabled (0). @enabled is tinyint, with a default of NULL.

[ @description = ] N'description'

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

[ @start_step_id = ] start_step_id

The identification number of the first step to execute for the job. @start_step_id is int, with a default of NULL.

[ @category_name = ] N'category_name'

The category of the job. @category_name is sysname, with a default of NULL.

[ @owner_login_name = ] N'owner_login_name'

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

[ @notify_level_eventlog = ] notify_level_eventlog

Specifies when to place an entry in the Microsoft Windows application log for this job. @notify_level_eventlog is int, and can be one of these values.

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

[ @notify_level_email = ] notify_level_email

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

[ @notify_level_netsend = ] notify_level_netsend

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

[ @notify_level_page = ] notify_level_page

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

[ @notify_email_operator_name = ] N'notify_email_operator_name'

The name of the operator to whom the e-mail is sent when email_level is reached. @notify_email_operator_name is sysname, with a default of NULL.

[ @notify_netsend_operator_name = ] N'notify_netsend_operator_name'

The name of the operator to whom the network message is sent. @notify_netsend_operator_name is sysname, with a default of NULL.

[ @notify_page_operator_name = ] N'notify_page_operator_name'

The name of the operator to whom a page is sent. @notify_page_operator_name is sysname, with a default of NULL.

[ @delete_level = ] delete_level

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

[ @automatic_post = ] automatic_post

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return code values

0 (success) or 1 (failure).

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.

This stored procedure shares the name of sp_update_job with a similar object for the Azure Elastic Jobs service for Azure SQL Database. For information about the elastic jobs version, see jobs.sp_update_job (Azure Elastic Jobs).

Permissions

You can grant EXECUTE permissions on this procedure, but these permissions might be overridden during a SQL Server upgrade.

Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Only members of sysadmin can use this stored procedure to edit the attributes of jobs that are owned by other users.

Examples

The following example changes the name, description, and enabled status of the job NightlyBackups.

USE msdb;
GO

EXEC dbo.sp_update_job
    @job_name = N'NightlyBackups',
    @new_name = N'NightlyBackups -- Disabled',
    @description = N'Nightly backups disabled during server migration.',
    @enabled = 0;
GO

Related content