/*
Add a job that all alerts will use then add it to all alerts
The job being called here is a simple task that adds an email 
to a table that servers a queue for an SMTP email relay agent. 
IMPORTANT! look down a few lines and add the appropriate 
servers and recipients for your organization

execure this line to test
	raiserror ('testing alert notification using safe_sendmail, please ignore',19,1) with log
*/
declare @rc int
	, @category_name sysname
	, @job_name sysname
	, @alert_name sysname
	, @alert_id int
	, @SQLStr varchar(4000)
	, @Recipients varchar(100)

-- define recipients to be notified when an alert fires
if @@servername in ('Goofey','Doc')
	set @Recipients = 'cinderella@houseinthewoods.net'

if @@servername in ('roudolph','donner','blitzer')
	set @Recipients = 'santa@NorthPole.com'

if @@servername = ('Production')
	set @Recipients = 'ALLDBAs@MyCompany.com'

if @Recipients is null
	set @Recipients = 'JustMe@MyCompany.com'

-- BELOW HERE YOU SHOULDN"T NEED TO CHANGE ANYTHING

set @category_name = 'SQLAgent Alert'
set @job_name = 'w Alert Notification'

set @SQLStr = 'exec sysmon.dbo.safe_sendmail 
	@recipients = ''' + @Recipients + '''
	, @subject = ''SQL Server Alert System: alert condition occurred on [SRVR]''
	, @message = ''Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG]		

Check the [SRVR] SQL Server ErrorLog and the Application event log on the server for additional details'''

-- add the category if it's not there
if not exists (select 1 from msdb.dbo.syscategories 
		where name = @category_name)
	exec msdb.dbo.sp_add_category 
		@class = 'JOB' 
		, @type = 'Local'
		, @name = @category_name

-- always delete any existing job in case it is changing here
if exists (select 1 from msdb.dbo.sysjobs 
		where name = @job_name)
	exec msdb.dbo.sp_delete_job 
		@job_name = @job_name 

exec msdb.dbo.sp_add_job 
	@job_name = @job_name 
	, @enabled =  1 
	, @description = 'SQLAgent alert system safe_sendmail notification' 
	, @start_step_id = 1
	, @category_name = @category_name
	, @owner_login_name = 'sa'

exec msdb.dbo.sp_add_jobstep
	@job_name = @job_name 
	, @step_id = 1
	, @step_name = 'submit a notification'
	, @command = @SQLStr
	, @database_name = 'master' 
	, @database_user_name = 'dbo' 

exec msdb.dbo.sp_add_jobserver 
	@job_name = @job_name
	, @server_name = '(local)'

print 'Job Creation complete.'

select @alert_id = min(id) from msdb.dbo.sysalerts
while @alert_id is not null
	begin
		select @alert_name = name
		from msdb.dbo.sysalerts
		where id = @alert_id
		
		print 'adding notification for alert ' + @alert_name
		exec msdb.dbo.sp_update_alert 
			@name = @alert_name
			, @job_name = @job_name 

		select @alert_id = min(id) 
		from msdb.dbo.sysalerts
		where id > @alert_id
	end