Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

sp_delete_jobstep (Transact-SQL)

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).

Removes a job step from a job.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_delete_jobstep { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } , 
     [ @step_id = ] step_id 

[ @job_id= ] job_id

The identification number of the job from which the step will be removed. job_idis uniqueidentifier, with a default of NULL.

[ @job_name= ] 'job_name'

The name of the job from which the step will be removed. job_nameis sysname, with a default of NULL.

Note Note

Either job_id or job_name must be specified; both cannot be specified.

[ @step_id= ] step_id

The identification number of the step being removed. step_idis int, with no default.

0 (success) or 1 (failure)

Removing a job step automatically updates the other job steps that reference the deleted step.

For more information about the steps associated with a particular job, run sp_help_jobstep.

Note Note

Calling sp_delete_jobstep with a step_id value of zero deletes all job steps for the job.

Microsoft SQL Server Management Studio provides an easy, graphical way to manage jobs, and is the recommended way to create and manage the job infrastructure.

By default, members of the sysadmin fixed server role can execute this stored procedure. 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 delete a job step that is owned by another user.

The following example removes job step 1 from the job Weekly Sales Data Backup.

USE msdb ;

EXEC dbo.sp_delete_jobstep
    @job_name = N'Weekly Sales Data Backup',
    @step_id = 1 ;

Community Additions

© 2015 Microsoft