Instructs SQLServerAgent to stop the execution of a job.
sp_stop_job [@job_name =] 'job_name'
| [@job_id =] job_id
| [@originating_server =] 'master_server'
| [@server_name =] 'target_server'
[@job_name =] 'job_name'
Is the name of the job to stop. job_name is sysname, with a default of NULL.
[@job_id =] job_id
Is the identification number of the job to stop. job_id is uniqueidentifier, with a default of NULL.
[@originating_server =] 'master_server'
Is the name of the master server. If specified, all multiserver jobs are stopped. master_server is nvarchar(30), with a default of NULL. Specify this parameter only when calling sp_stop_job at a target server.
Note Only one of the first three parameters can be specified.
[@server_name =] 'target_server'
Is the name of the specific target server on which to stop a multiserver job. target_server is nvarchar(30), with a default of NULL. Specify this parameter only when calling sp_stop_job at a master server for a multiserver job.
Return Code Values
0 (success) or 1 (failure)
If a job is currently executing a step of type CmdExec, the process being run (for example, MyProgram.exe) is forced to end prematurely. Premature ending can result in unpredictable behavior such as files in use by the process being held open. Consequently, sp_stop_job should be used only in extreme circumstances if the job contains steps of type CmdExec.
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 stop any job. A user who is not a member of the sysadmin role can use sp_stop_job to stop only the jobs he/she owns.
When sp_stop_job is invoked by a user who is a member of the sysadmin fixed server role, sp_stop_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 group, sp_stop_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_stop_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_stop_job is always executed under the security context of the Windows 9.x user who started SQL Server.
This example stops a job named Archive Tables.
USE msdb EXEC sp_stop_job @job_name = 'Archive Tables'