Tuesday, July 5, 2011

Disable/Enable all scheduled jobs in SQL Server 2005

Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs

WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 0 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END


Declare @job_id Nvarchar(200)
DECLARE @String1 NVARCHAR(1000)
DECLARE @jobs TABLE (job_id NVARCHAR(200))
SET NOCOUNT ON
-- job id
INSERT @jobs
select job_id from msdb..sysjobs

WHILE EXISTS (SELECT TOP 1 job_id FROM @jobs WHERE job_id IS NOT NULL)
BEGIN
SELECT TOP 1 @job_id = job_id
FROM @jobs
SET @String1 ='EXEC msdb..sp_update_job @job_id = ''' +@job_id + ''', @enabled = 1 '
print @String1
EXECUTE sp_executesql @String1
DELETE FROM @jobs WHERE job_id = @job_id
END