TSQL : Agent Job Notififications via email

A quick run through of setting email notifications for emails

1) Set Sql agent to use a database mail profile.

-- Set default database mail profile for sql agent
-- My dbamail profile name is 'SQL Operator'

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'SQL Operator'
GO

2) Add an operator (email recipient) for the alerts.

-- Add Operator Recipient for Sql Agent jobs
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'Agent Job Operator', 
  @enabled=1, 
  @pager_days=0, 
  @email_address=N'dba@mydomain.co.uk'
GO

3) Set the jobs to send emails on failure.

USE [msdb]
GO
sp_update_job 
select 'exec sp_update_job @job_name =  '''+name+''' , @notify_email_operator_name = ''Agent Job Operator'' , @notify_level_email = 2'
from msdb.dbo.sysjobs
where enabled = 1
/*
note values for @notify_level_email signify when to send emails are - 
0 - never
1 - success
2 - failure
3 - always
*/