Instructs SQL Server Agent to execute a job immediately.
sp_start_job [@job_name =] 'job_name' | [@job_id =] job_id
[,[@error_flag =] error_flag]
[,[@server_name =] 'server_name']
[,[@step_name =] 'step_name']
[,[@output_flag =] output_flag]
[@job_name =] 'job_name'
Is the name of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_name is sysname, with a default of NULL.
[@job_id =] job_id
Is the identification number of the job to start. Either job_id or job_name must be specified, but both cannot be specified. job_id is uniqueidentifier, with a default of NULL.
[@error_flag =] error_flag
[@server_name =] 'server_name'
Is the target server on which to start the job. server_name is nvarchar(30), with a default of NULL. server_name must be one of the target servers to which the job is currently targeted.
[@step_name =] 'step_name'
Is the name of the step at which to begin execution of the job. Applies only to local jobs. step_name is sysname, with a default of NULL
[@output_flag =] output_flag
Return Code Values
0 (success) or 1 (failure)
Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.
When sp_start_job is invoked by a user who is a member of the sysadmin fixed server role, sp_start_job will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin fixed server role, sp_start_job will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, sp_start_job will fail. This is only true for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and sp_start_job is always executed under the security context of the Windows 9.x user who started SQL Server.
This example starts a job named Nightly Backup.
USE msdb EXEC sp_start_job @job_name = 'Nightly Backup'