August 1, 2012 at 4:46 am
Im using SQL Server 2012 and I have a case where I have to delete sql jobs. I found two approaches which are as follows:
Approach 1
DECLARE @jobId binary(16)
WHILE (1=1)
BEGIN
SET @jobId = NULL
SELECT TOP 1 @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name like N'Abc%')
IF @@ROWCOUNT = 0
BREAK
IF (@jobId IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_delete_job @jobId
END
END
Approach 2
DECLARE @listStr VARCHAR(MAX)=null
SELECT @listStr = COALESCE(@listStr+'exec msdb.dbo.sp_delete_job ' ,'') + '''' + convert(varchar(max),job_id) + '''; '
FROM msdb.dbo.sysjobs WHERE (name like N'$(TestPublisherServer)-$(TestPublisherDB)%')
IF @listStr is not null
BEGIN
PRINT 'exec msdb.dbo.sp_delete_job ' + @listStr
EXEC ('exec msdb.dbo.sp_delete_job ' + @listStr)
END
Both the approaches will delete the jobs, but I want to know which is the best way or you can suggest me more efficient or correct ways to delete the jobs.
And one more question is, do we have to stop/disable the job before deleting it.
TIA Harsha
August 1, 2012 at 10:52 am
sriharsha2410 (8/1/2012)
Im using SQL Server 2012 and I have a case where I have to delete sql jobs. I found two approaches which are as follows:Approach 1
DECLARE @jobId binary(16)
WHILE (1=1)
BEGIN
SET @jobId = NULL
SELECT TOP 1 @jobId = job_id FROM msdb.dbo.sysjobs WHERE (name like N'Abc%')
IF @@ROWCOUNT = 0
BREAK
IF (@jobId IS NOT NULL)
BEGIN
EXEC msdb.dbo.sp_delete_job @jobId
END
END
Approach 2
DECLARE @listStr VARCHAR(MAX)=null
SELECT @listStr = COALESCE(@listStr+'exec msdb.dbo.sp_delete_job ' ,'') + '''' + convert(varchar(max),job_id) + '''; '
FROM msdb.dbo.sysjobs WHERE (name like N'$(TestPublisherServer)-$(TestPublisherDB)%')
IF @listStr is not null
BEGIN
PRINT 'exec msdb.dbo.sp_delete_job ' + @listStr
EXEC ('exec msdb.dbo.sp_delete_job ' + @listStr)
END
Both the approaches will delete the jobs, but I want to know which is the best way or you can suggest me more efficient or correct ways to delete the jobs.
A loop as implemented in approach 1 is typically a poor choice for data processing but for admin work it is fine. I prefer approach 2 myself and use the technique in my admin work instead of loops where possible. Approach 2 is more readable and easier to debug IMHO.
And one more question is, do we have to stop/disable the job before deleting it.
The enabled flag has no bearing on the outcome when deleting a job.
If a job is running however, and you delete it, it will cause the job to fail immediately.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply