SQL Alerts – turn on mail notification

Today I found out that a colleague of mine had forgotten to set SQL Alert Mail notifications

I have written the script below to correct this.

-- set operator
declare @operator_name nvarchar(50) = '<your_operator_name, varchar(50), value>'

-- begin proces
declare @name sysname
declare SetNotificationCursor cursor for
select name from msdb.dbo.sysalerts

open SetNotificationCursor
fetch next from SetNotificationCursor into @name

while @@FETCH_STATUS = 0
begin 
  begin try
    exec (N'EXEC msdb.dbo.sp_add_notification @alert_name=N'''+@name+''', @operator_name=N'''+@operator_name+''', @notification_method = 1')
    end try 
  begin catch
    print  ERROR_MESSAGE()
  end catch
  fetch next from SetNotificationCursor into @name
end

close SetNotificationCursor
deallocate SetNotificationCursor
GO
-- end