jobs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

jobs

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-12-17 : 05:05:59

Hi

Any way to disable all the job in SQL 2005 in a single query

Thanks

saralstalin
Starting Member

11 Posts

Posted - 2009-12-17 : 07:42:28

Run the below query

UPDATE msdb.dbo.sysjobs
SET enabled = 0

Saral S Stalin

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 01:24:38

Another method is



declare @sql varchar(max)

set @sql=''
select 
        @sql=@sql+' EXEC msdb..sp_update_job @job_id='''+cast(job_id as varchar(100))+''',@enabled=0;' 
from 
        msdb..sysjobs 
where 
        enabled=1

exec(@sql)

Madhivanan

Failing to plan is Planning to fail

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2009-12-23 : 08:57:06

Test is a test.


declare @sql varchar(max)
set @sql=''
select @sql=@sql+' EXEC msdb..sp_update_job @job_id='''+cast(job_id as varchar(100))+''',@enabled=0;' 
from msdb..sysjobs 
where enabled=1
exec(@sql)

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 09:02:36

Graz.

Strange. Now I copy your code and try to post but it hangs

Madhivanan

Failing to plan is Planning to fail

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2009-12-23 : 09:52:48

Got it. When I post as a non-administrator I get the same error you do. I'll look into it over the holidays.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 10:16:20

quote:
Originally posted by graz

Got it. When I post as a non-administrator I get the same error you do. I'll look into it over the holidays.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

Thanks

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-08 : 03:02:24

I will try too (copy from graz):

declare @sql varchar(max)
set @sql=''
select @sql=@sql+' EXEC msdb..sp_update_job @job_id='''+cast(job_id as varchar(100))+''',@enabled=0;'
from msdb..sysjobs
where enabled=1
exec(@sql)

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-08 : 03:51:12

It didn't work for me as I specified earlier
That was why I formatted differently and posted (see my first reply)

Madhivanan

Failing to plan is Planning to fail

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-08 : 09:53:55

I disable SQL Agent when I need to disable all jobs

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-08 : 09:59:00

quote:
Originally posted by saralstalin

Run the below query

UPDATE msdb.dbo.sysjobs
SET enabled = 0

Saral S Stalin

This works not reliable, better use sp_updatejob like madhi wrote.
We have had a problem with a solution like yours in the past. The agent has shown the empty box which means job is not enabled but the job startet anyway...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources