-- First we need to create an operator that will receive notifications.

Use msdb

Go

If Not Exists(Select name From msdb.dbo.sysoperators

Where name = N’DBAdministrator’)

            Exec msdb.dbo.sp_add_operator @name=N’DBAdministrator’,

                                   @enabled=1,

                                   @pager_days=0,

                                   @email_address=N'account@mailserver.com'

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert1 : insufficient resources.

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N'Insufficient resources')

            Exec msdb.dbo.sp_add_alert @name= N'Insufficient resources',

                                   @message_id=0,

                                   @severity=17,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N'Insufficient resources'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N'Insufficient resources',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert2 : recoverable internal error.

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N'Recoverable internal error ')

            Exec msdb.dbo.sp_add_alert @name= N'Recoverable internal error ',

                                   @message_id=0,

                                   @severity=18,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N'Recoverable internal error '

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N'Recoverable internal error ',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert3 : fatal error resource.

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N'Fatal error resource ')

            Exec msdb.dbo.sp_add_alert @name= N'Fatal error resource ',

                                   @message_id=0,

                                   @severity=19,

                                    @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N'Fatal error resource '

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error resource ',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert4 : fatal error during processing.

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N'Fatal error during processing')

            Exec msdb.dbo.sp_add_alert @name= N'Fatal error during processing',

                                   @message_id=0,

                                   @severity=20,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N'Fatal error during processing'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error during processing',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert5 : fatal error in the treatment database.

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N' Fatal error in the treatment database’)

            Exec msdb.dbo.sp_add_alert @name= N' Fatal error in the treatment database’,

                                   @message_id=0,

                                   @severity=21,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N' Fatal error in the treatment database’

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N' Fatal error in the treatment database’,

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert6 : fatal error (questionable integrity of the table).

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N'Fatal error (questionable integrity of the table)')

            Exec msdb.dbo.sp_add_alert @name= N'Fatal error (questionable integrity of the table)',

                                   @message_id=0,

                                   @severity=22,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N'Fatal error (questionable integrity of the table)'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error (questionable integrity of the table)',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert7 : fatal error : dubious integrity of the database.

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N' Fatal error : dubious integrity of the database')

            Exec msdb.dbo.sp_add_alert @name= N' Fatal error : dubious integrity of the database',

                                   @message_id=0,

                                   @severity=23,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N' Fatal error : dubious integrity of the database'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N' Fatal error : dubious integrity of the database',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert8 : fatal error (hardware error).

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N'Fatal error (hardware error)')

            Exec msdb.dbo.sp_add_alert @name= N'Fatal error (hardware error)',

                                   @message_id=0,

                                   @severity=24,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N'Fatal error (hardware error)'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error (hardware error)',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert9 : fatal error.

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N'Fatal error')

            Exec msdb.dbo.sp_add_alert @name= N'Fatal error',

                                   @message_id=0,

                                   @severity=25,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N'Fatal error'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert10 : error number 823 (hardware or system problem)

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N'Error Number 823 (hardware or system problem)')

            Exec msdb.dbo.sp_add_alert @name= N'Error Number 823 (hardware or system problem)',

                                   @message_id=823,

                                   @severity=0,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @notification_message= N'Error Number 823 (hardware or system problem)',

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N'Error Number 823 (hardware or system problem)'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N'Error Number 823 (hardware or system problem)',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert11 : error number 824 (I/O logic error).

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N' Error Number 824 (I/O logic error)')

            Exec msdb.dbo.sp_add_alert @name= N' Error Number 824 (I/O logic error)',

                                   @message_id=824,

                                   @severity=0,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @notification_message=N'Erreur logique I/O',

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N' Error Number 824 (I/O logic error)'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N' Error Number 824 (I/O logic error)',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------

-- Alert12 : error number 825 (rerun reading - major error with the disk drive).

If Not Exists(Select name From msdb.dbo.sysalerts

Where name = N' Error Number 825 (rerun reading - major error with the disk drive)')

            Exec msdb.dbo.sp_add_alert @name= N' Error Number 825 (rerun reading - major error with the disk drive)',

                                   @message_id=825,

                                   @severity=0,

                                   @enabled=1,

                                   @delay_between_responses=0,

                                   @include_event_description_in=1,

                                   @notification_message= N' Error Number 825 (rerun reading - major error with the disk drive)',

                                   @job_id=N'00000000-0000-0000-0000-000000000000'

Go

-- Notify the operator.

If Not Exists(Select notifications.notification_method, alerts.name, operators.name

                                                           From msdb.dbo.sysnotifications as notifications

                                                           inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id

                                                           inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id

                                                           Where alerts.name = N' Error Number 825 (rerun reading - major error with the disk drive)'

                                                           and operators.name = N’DBAdministrator’

                                                           and  notification_method = 1)

            Exec msdb.dbo.sp_add_notification @alert_name= N' Error Number 825 (rerun reading - major error with the disk drive)'',

                                   @operator_name=N’DBAdministrator’, @notification_method = 1

Go

---------------------------------------------------------------------------------------------------------------------