• 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