Speaking in Code

Friday, February 08, 2013

Apply SQL Agent notification to all jobs that lack it | SQL Tidbits

Based on the script I found here:
Create notifications for failed jobs with one easy script | SQL Tidbits:

I slightly revised to only script notification addition if it wasn't already present:

DECLARE @QuotedIdentifier CHAR(1);

SET @QuotedIdentifier = '' -- use '''' for single quote

DECLARE @ListDelimeter CHAR(1);

SET @ListDelimeter = ';'

DECLARE @CSVlist VARCHAR(max) --use varchar(8000) for SQL Server 2000

--no event log, email on failure
SELECT @CSVlist = COALESCE(@CSVlist + @ListDelimeter, '') + @QuotedIdentifier + '
EXEC msdb.dbo.sp_update_job @job_id=N''' + convert(VARCHAR(max), [job_id]) + ''',
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_email_operator_name=N''DbaOperator''' + @QuotedIdentifier
FROM msdb.dbo.sysjobs j
WHERE j.[enabled] = 1
AND j.[notify_level_email] <> 2

--print @csvlist
EXEC (@CSVlist)

0 Comments:

Post a Comment

<< Home