One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Friday, March 2, 2012

Clearing Job Notifications - HELP!

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:

EXEC msdb.dbo.sp_update_job @job_name = 'DatabaseBackup - SYSTEM_DATABASES - FULL', @notify_level_email = 0, @notify_email_operator_name = NULL

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!

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

UPDATE: Thanks to further #sqlhelp assistance from @SQLSoldier and @banerjeeamit I was able to get this to work by passing an empty string ('') in for the value of @notify_email_operator rather than the keyword NULL.  This does not require passing a @notify_level_email parameter at all:

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

Many thanks!


2 comments:

  1. Have you tried this: EXEC msdb.dbo.sp_update_job @job_id=',
    @notify_level_email=2,
    @notify_email_operator_name=N''

    ReplyDelete
  2. Additionally something weird, if you try to update the enable state of the JOB which has a notification email - you get same error (seem the value default to 0)...

    ReplyDelete