SQL Server Agent Job - Exists then Drop?
Try something like this:
DECLARE @jobId binary(16)SELECT @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Name of Your Job')IF (@jobId IS NOT NULL)BEGIN EXEC msdb.dbo.sp_delete_job @jobIdENDDECLARE @ReturnCode intEXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Name of Your Job'
Best to read the docs on all the parameters required for 'sp_add_job' and 'sp_delete_job'
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Your Job Name')EXEC msdb.dbo.sp_delete_job @job_name=N'Your Job Name' , @delete_unused_schedule=1
If you generate the SQL script for a job (tested with enterprise manager), it automatically builds the check for existance and drop statements for you. Example below: -
DECLARE @JobID BINARY(16) DECLARE @ReturnCode INT SELECT @ReturnCode = 0 -- Delete the job with the same name (if it exists) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'My test job') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''My test job'' since there is already a multi-server job with this name.', 16, 1) END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'My test job' SELECT @JobID = NULL END