Delete Job by Name

When scripting out jobs using ssms (sql server management studio) the default script for a drop statement is to drop the job according to it’s job_id. This is not beneficial however when pushing code to different environments. Job_id’s are specific to the windows environment in which they are created. To get around this is easy however, you simply need to pass the @job_name parameter to the sp_delete_job stored procedure.

[cc lang=”sql”]
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N’jobnamegoeshere’)
EXEC sp_delete_job @job_name = N’jobnamegoeshere’ , @delete_unused_schedule=1
[/cc]

5 comments
Dave 23 Oct 2013 at 11:45 pm

It would be nice to have a script to delete many jobs at once with a like statement.

RMiranda 17 Apr 2013 at 5:41 pm

Why the hell does Manag/Studio generate the script with the job_id…..

Thanks for the tip, extremely helpful!

Saberi 01 Dec 2011 at 3:04 am

After Run Error :

Server: Msg 14274, Level 16, State 1, Procedure sp_delete_job, Line 86
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

Thanks for help me.

Hassan 15 Jun 2011 at 4:20 am

Very helpful.

Ranjan 13 Apr 2011 at 3:34 am

Thanks for the information.. Nice.

Featured Articles

 Site Author

  • Thanks for visiting!
css.php