n00bDBA (8/30/2012)
Have a done something quite silly and other things needed to be changed for this to work properly.. Or have i found a much simpler solution!
I don't know? Did you?
EXEC msdb.sys.sp_helptext
@objname = N'dbo.sp_update_job';
I would not get into the habit of updating the tables in msdb directly. Here is a simpler way for you to produce the desired result next time you need to do something like this without using a cursor:
USE msdb;
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + 'EXEC msdb.dbo.sp_update_job @job_name=N''' + name + ''',
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2;
'
FROM msdb.dbo.sysjobs;
PRINT @sql; -- << output may be truncated but EXEC will execute complete content of variable
-- uncomment to exec
--EXEC(@sql);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato