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