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!
Have you tried this: EXEC msdb.dbo.sp_update_job @job_id=',
ReplyDelete@notify_level_email=2,
@notify_email_operator_name=N''
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