I have inherited a SQL 2005 server with SQL Agent job notifications in place, and a request to clear the existing notifications on all jobs and then set up new notifications on only a subset of jobs.

Sounds easy right?

Wrote a cursor (gasp) to loop through the jobs and build sp_update_job statements using synamic t-sql (double-gasp - you all know you've done it {-:)  of this form:

The catch is when I run this I get the following error:
 
Msg 14266, Level 16, State 1, Procedure sp_verify_job, Line 249
The specified '@notify_level_email' is invalid (valid values are: 1, 2, 3).

I looked inside the code for sp_verify_job and found the offending line:

 -- If a valid operator is specified the level must be non-zero 
    IF (@notify_level_email = 0) 
    BEGIN 
      RAISERROR(14266, -1, -1, '@notify_level_email', '1, 2, 3') 
      RETURN(1) -- Failure 
    END 

Looking at what appeared to be a Catch-22 (can't clear the notifications unless the name is NULL) I turned to Twitter:

...and I received a response from SQL MCM and all-around knowledgeable source Robert Davis (@SQLSoldier):

I pondered briefly and considered - would the system really let me set the operator to NULL without setting the notify_level to 0?  If it did what would Management Studio show? Wondering what I would find I went ahead and gave it a try...

EXEC msdb.dbo.sp_update_job @job_name = 'DatabaseBackup - USER_DATABASES - FULL', @notify_email_operator_name = NULL

No error!  Did it really work?

Apparently not.

I checked sysjobs to verify:

select left(name,40) as JobName, notify_level_email,notify_email_operator_id
from msdb.dbo.sysjobs where name = 'DatabaseBackup - USER_DATABASES - FULL'

...and found what I expected:

I have officially run out of ideas (that do not involve directly editing the jobs table - sigh) to clear these notifications programmatically - help!

------------