sp_update_job (Transact-SQL)
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
This stored procedure is owned by the db_owner role. You can grant EXECUTE
permissions for any user, but these permissions may 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