Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Adds a new job executed by the SQLServerAgent service.
Applies to: SQL Server (SQL Server 2008 through current version).
sp_add_job [ @job_name = ] 'job_name' [ , [ @enabled = ] enabled ] [ , [ @description = ] 'description' ] [ , [ @start_step_id = ] step_id ] [ , [ @category_name = ] 'category' ] [ , [ @category_id = ] category_id ] [ , [ @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_name' ] [ , [ @notify_page_operator_name = ] 'page_name' ] [ , [ @delete_level = ] delete_level ] [ , [ @job_id = ] job_id OUTPUT ]
@originating_server exists in sp_add_job, but is not listed under Arguments. @originating_server is reserved for internal use.
After sp_add_job has been executed to add a job, sp_add_jobstep can be used to add steps that perform the activities for the job. sp_add_jobschedule can be used to create the schedule that the SQL Server Agent service uses to execute the job. Use sp_add_jobserver to set the SQL Server instance where the job executes, and sp_delete_jobserver to remove the job from the SQL Server instance.
If the job will execute on one or more target servers in a multiserver environment, use sp_apply_job_to_targets to set the target servers or target server groups for the job. To remove jobs from target servers or target server groups, use sp_remove_job_from_targets.
SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.
To run this stored procedure, users must be a member of the sysadmin fixed server role, or be granted one of the following SQL Server Agent fixed database roles, which reside in the msdb database:
For information about the specific permissions that are associated with each of these fixed database roles, see SQL Server Agent Fixed Database Roles.
Only members of the sysadmin fixed server role can set or change the value for @owner_login_name. If users who are not members of the sysadmin role set or change the value of @owner_login_name, execution of this stored procedure fails and an error is returned.
A. Adding a job
This example adds a new job named NightlyBackups.
USE msdb ; GO EXEC dbo.sp_add_job @job_name = N'NightlyBackups' ; GO
B. Adding a job with pager, e-mail, and net send information
This example creates a job named Ad hoc Sales Data Backup that notifies François Ajenstat (by pager, e-mail, or network pop-up message) if the job fails, and deletes the job upon successful completion.
This example assumes that an operator named François Ajenstat and a login named françoisa already exist.
USE msdb ; GO EXEC dbo.sp_add_job @job_name = N'Ad hoc Sales Data Backup', @enabled = 1, @description = N'Ad hoc backup of sales data', @owner_login_name = N'françoisa', @notify_level_eventlog = 2, @notify_level_email = 2, @notify_level_netsend = 2, @notify_level_page = 2, @notify_email_operator_name = N'François Ajenstat', @notify_netsend_operator_name = N'François Ajenstat', @notify_page_operator_name = N'François Ajenstat', @delete_level = 1 ; GO