How to Execute code in result Set

Transact-SQL
1

Hello,

I am using this script to provide the list of jobs that I want disabled:

SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs

The results are something like:

exec msdb..sp_update_job @job_name = 'BACKUP1', @enabled = 0
exec msdb..sp_update_job @job_name = 'BACKUP2', @enabled = 0
exec msdb..sp_update_job @job_name = 'BACKUP3', @enabled = 0
exec msdb..sp_update_job @job_name = 'BACKUP4', @enabled = 0

How can I take the resuslts above and execute them automatically. I dont want to manually copy the results and paste it and then execute it. I either need a loop, cursor or something or a way to put it all in an SP. Any help would be awesome. Thanks

2 
declare    @sql    nvarchar(max)
SELECT     @sql = isnull(@sql, '') + 'exec msdb..sp_update_job @job_name = '''+name+''', @enabled = 0;' 
FROM     msdb..sysjobs

print    @sql

exec    (@sql)
1 Like
3

Awesome ok now when I want to enable just the jobs that were turned off how can that be done? Thanks

4

check for

enabled = 0
1 Like
5

They all will have 0 though. I will need to set enabled to 1 to enable them back on but just for the ones I turned off.

6

pass in

 @enabled = 1
1 Like
7

Thank you!