= @last_response_time,
      flags                       = @x_flags,
      performance_condition       = @performance_condition,
      category_id                 = @category_id
  WHERE (name = @name)

  -- Notify SQLServerAgent of the change
  IF (@cached_attribute_modified = 1)
    EXECUTE msdb.dbo.sp_sqlagent_notify @op_type     = N'A',
                                        @alert_id    = @alert_id,
                                        @action_type = N'U'
  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_DELETE_ALERT                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_delete_alert...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_alert')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_alert
go
CREATE PROCEDURE sp_delete_alert
  @name sysname
AS
BEGIN
  DECLARE @alert_id    INT
  DECLARE @return_code INT

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @name = LTRIM(RTRIM(@name))

  -- Only a sysadmin can do this
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  -- Check if SQLServerAgent is in the process of starting
  EXECUTE @return_code = msdb.dbo.sp_is_sqlagent_starting
  IF (@return_code <> 0)
    RETURN(1) -- Failure

  -- Check if this Alert exists
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysalerts
                  WHERE (name = @name)))
  BEGIN
    RAISERROR(14262, 16, 1, '@name', @name)
    RETURN(1) -- Failure
  END

  -- Convert the Name to it's ID
  SELECT @alert_id = id
  FROM msdb.dbo.sysalerts
  WHERE (name = @name)

  BEGIN TRANSACTION

    -- Delete sysnotifications entries
    DELETE FROM msdb.dbo.sysnotifications
    WHERE (alert_id = @alert_id)

    -- Finally, do the actual DELETE
    DELETE FROM msdb.dbo.sysalerts
    WHERE (id = @alert_id)

  COMMIT TRANSACTION

  -- Notify SQLServerAgent of the change
  EXECUTE msdb.dbo.sp_sqlagent_notify @op_type     = N'A',
                                      @alert_id    = @alert_id,
                                      @action_type = N'D'
  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_HELP_ALERT                                              */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_help_alert...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_help_alert')
              AND (type = 'P')))
  DROP PROCEDURE sp_help_alert
go
CREATE PROCEDURE sp_help_alert
  @alert_name    sysname = NULL,
  @order_by      sysname = N'name',
  @alert_id      INT     = NULL,
  @category_name sysname = NULL
AS
BEGIN
  DECLARE @alert_id_as_char NVARCHAR(10)

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @alert_name    = LTRIM(RTRIM(@alert_name))
  SELECT @order_by      = LTRIM(RTRIM(@order_by))
  SELECT @category_name = LTRIM(RTRIM(@category_name))

  -- Turn [nullable] empty string parameters into NULLs
  IF (@category_name = N'') SELECT @category_name = NULL
  IF (@alert_name = N'')    SELECT @alert_name = NULL

  -- Check alert name
  IF (@alert_name IS NOT NULL)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysalerts
                    WHERE (name = @alert_name)))
    BEGIN
      RAISERROR(14262, -1, -1, '@alert_name', @alert_name)
      RETURN(1) -- Failure
    END
  END

  -- Check alert id
  IF (@alert_id IS NOT NULL)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysalerts
                    WHERE (id = @alert_id)))
    BEGIN
      SELECT @alert_id_as_char = CONVERT(VARCHAR, @alert_id)
      RAISERROR(14262, -1, -1, '@alert_id', @alert_id_as_char)
      RETURN(1) -- Failure
    END
  END

  IF (@order_by NOT LIKE N'job_name%')
    SELECT @order_by = N'sa.' + @order_by

  IF (@alert_id IS NOT NULL)
    SELECT @alert_id_as_char = CONVERT(VARCHAR, @alert_id)
  ELSE
    SELECT @alert_id_as_char = N'NULL'

  -- Double up any single quotes in @alert_name
  IF (@alert_name IS NOT NULL)
    SELECT @alert_name = REPLACE(@alert_name, N'''', N'''''')

  -- Double up any single quotes in @category_name
  IF (@category_name IS NOT NULL)
    SELECT @category_name = REPLACE(@category_name, N'''', N'''''')

  EXECUTE (N'SELECT sa.id,
                    sa.name,
                    sa.event_source,
                    sa.event_category_id,
                    sa.event_id,
                    sa.message_id,
                    sa.severity,
                    sa.enabled,
                    sa.delay_between_responses,
                    sa.last_occurrence_date,
                    sa.last_occurrence_time,
                    sa.last_response_date,
                    sa.last_response_time,
                    sa.notification_message,
                    sa.include_event_description,
                    sa.database_name,
                    sa.event_description_keyword,
                    sa.occurrence_count,
                    sa.count_reset_date,
                    sa.count_reset_time,
                    sjv.job_id,
                    job_name = sjv.name,
                    sa.has_notification,
                    sa.flags,
                    sa.performance_condition,
                    category_name = sc.name,
                    type = CASE ISNULL(performance_condition, ''!'')
                             WHEN ''!'' THEN
                               CASE event_source
                                 WHEN ''MSSQLSERVER'' THEN 1 -- SQL Server event alert
                                 ELSE 3                      -- Non SQL Server event alert
                               END
                             ELSE 2                          -- SQL Server performance condition alert
                           END
             FROM msdb.dbo.sysalerts                     sa
                  LEFT OUTER JOIN msdb.dbo.sysjobs_view  sjv ON (sa.job_id = sjv.job_id)
                  LEFT OUTER JOIN msdb.dbo.syscategories sc  ON (sa.category_id = sc.category_id)
             WHERE ((N''' + @alert_name + N''' = N'''') OR (sa.name = N''' + @alert_name + N'''))
               AND ((' + @alert_id_as_char + N' IS NULL) OR (sa.id = ' + @alert_id_as_char + N'))
               AND ((N''' + @category_name + N''' = N'''') OR (sc.name = N''' + @category_name + N'''))
             ORDER BY ' + @order_by)

  RETURN(@@error) -- 0 means success
END
go

/**************************************************************/
/* SP_VERIFY_OPERATOR                                         */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_verify_operator...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_verify_operator')
              AND (type = 'P')))
  DROP PROCEDURE sp_verify_operator
go
CREATE PROCEDURE sp_verify_operator
  @name                      sysname,
  @enabled                   TINYINT,
  @pager_days                TINYINT,
  @weekday_pager_start_time  INT,
  @weekday_pager_end_time    INT,
  @saturday_pager_start_time INT,
  @saturday_pager_end_time   INT,
  @sunday_pager_start_time   INT,
  @sunday_pager_end_time     INT,
  @category_name             sysname,
  @category_id               INT OUTPUT
AS
BEGIN
  DECLARE @return_code     TINYINT
  DECLARE @res_valid_range NVARCHAR(100)

  SET NOCOUNT ON

  SELECT @res_valid_range = FORMATMESSAGE(14209)

  -- Remove any leading/trailing spaces from parameters
  SELECT @name          = LTRIM(RTRIM(@name))
  SELECT @category_name = LTRIM(RTRIM(@category_name))

  -- The name must be unique
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysoperators
              WHERE (name = @name)))
  BEGIN
    RAISERROR(14261, 16, 1, '@name', @name)
    RETURN(1) -- Failure
  END

  -- Enabled must be 0 or 1
  IF (@enabled NOT IN (0, 1))
  BEGIN
    RAISERROR(14266, 16, 1, '@enabled', '0, 1')
    RETURN(1) -- Failure
  END

  -- Check PagerDays
  IF (@pager_days < 0) OR (@pager_days > 127)
  BEGIN
    RAISERROR(14266, 16, 1, '@pager_days', @res_valid_range)
    RETURN(1) -- Failure
  END

  -- Check Start/End Times
  EXECUTE @return_code = sp_verify_job_time @weekday_pager_start_time, '@weekday_pager_start_time'
  IF (@return_code <> 0)
    RETURN(1)

  EXECUTE @return_code = sp_verify_job_time @weekday_pager_end_time, '@weekday_pager_end_time'
  IF (@return_code <> 0)
    RETURN(1)

  EXECUTE @return_code = sp_verify_job_time @saturday_pager_start_time, '@saturday_pager_start_time'
  IF (@return_code <> 0)
    RETURN(1)

  EXECUTE @return_code = sp_verify_job_time @saturday_pager_end_time, '@saturday_pager_end_time'
  IF (@return_code <> 0)
    RETURN(1)

  EXECUTE @return_code = sp_verify_job_time @sunday_pager_start_time, '@sunday_pager_start_time'
  IF (@return_code <> 0)
    RETURN(1)

  EXECUTE @return_code = sp_verify_job_time @sunday_pager_end_time, '@sunday_pager_end_time'
  IF (@return_code <> 0)
    RETURN(1)

  -- Check category name
  IF (@category_name = N'[DEFAULT]')
    SELECT @category_id = 99
  ELSE
  BEGIN
    SELECT @category_id = category_id
    FROM msdb.dbo.syscategories
    WHERE (category_class = 3) -- Operators
      AND (category_type = 3) -- None
      AND (name = @category_name)
  END
  IF (@category_id IS NULL)
  BEGIN
    RAISERROR(14262, -1, -1, '@category_name', @category_name)
    RETURN(1) -- Failure
  END

  RETURN(0)
END
go

/**************************************************************/
/* SP_ADD_OPERATOR                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_add_operator...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_add_operator')
              AND (type = 'P')))
  DROP PROCEDURE sp_add_operator
go
CREATE PROCEDURE sp_add_operator
  @name                      sysname,
  @enabled                   TINYINT       = 1,
  @email_address             NVARCHAR(100) = NULL,
  @pager_address             NVARCHAR(100) = NULL,
  @weekday_pager_start_time  INT           = 090000, -- HHMMSS using 24 hour clock
  @weekday_pager_end_time    INT           = 180000, -- As above
  @saturday_pager_start_time INT           = 090000, -- As above
  @saturday_pager_end_time   INT           = 180000, -- As above
  @sunday_pager_start_time   INT           = 090000, -- As above
  @sunday_pager_end_time     INT           = 180000, -- As above
  @pager_days                TINYINT       = 0,      -- 1 = Sunday .. 64 = Saturday
  @netsend_address           NVARCHAR(100) = NULL,   -- New for 7.0
  @category_name             sysname       = NULL    -- New for 7.0
AS
BEGIN
  DECLARE @return_code TINYINT
  DECLARE @category_id INT

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @name            = LTRIM(RTRIM(@name))
  SELECT @email_address   = LTRIM(RTRIM(@email_address))
  SELECT @pager_address   = LTRIM(RTRIM(@pager_address))
  SELECT @netsend_address = LTRIM(RTRIM(@netsend_address))
  SELECT @category_name   = LTRIM(RTRIM(@category_name))

  -- Turn [nullable] empty string parameters into NULLs
  IF (@email_address   = N'') SELECT @email_address   = NULL
  IF (@pager_address   = N'') SELECT @pager_address   = NULL
  IF (@netsend_address = N'') SELECT @netsend_address = NULL
  IF (@category_name   = N'') SELECT @category_name   = NULL

  -- Only a sysadmin can do this
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  IF (@category_name IS NULL)
  BEGIN
    SELECT @category_name = name
    FROM msdb.dbo.syscategories
    WHERE (category_id = 99)
  END

  -- Verify the operator
  EXECUTE @return_code = sp_verify_operator @name,
                                            @enabled,
                                            @pager_days,
                                            @weekday_pager_start_time,
                                            @weekday_pager_end_time,
                                            @saturday_pager_start_time,
                                            @saturday_pager_end_time,
                                            @sunday_pager_start_time,
                                            @sunday_pager_end_time,
                                            @category_name,
                                            @category_id OUTPUT
  IF (@return_code <> 0)
    RETURN(1) -- Failure

  -- Finally, do the INSERT
  INSERT INTO msdb.dbo.sysoperators
         (name,
          enabled,
          email_address,
          last_email_date,
          last_email_time,
          pager_address,
          last_pager_date,
          last_pager_time,
          weekday_pager_start_time,
          weekday_pager_end_time,
          saturday_pager_start_time,
          saturday_pager_end_time,
          sunday_pager_start_time,
          sunday_pager_end_time,
          pager_days,
          netsend_address,
          last_netsend_date,
          last_netsend_time,
          category_id)
  VALUES (@name,
          @enabled,
          @email_address,
          0,
          0,
          @pager_address,
          0,
          0,
          @weekday_pager_start_time,
          @weekday_pager_end_time,
          @saturday_pager_start_time,
          @saturday_pager_end_time,
          @sunday_pager_start_time,
          @sunday_pager_end_time,
          @pager_days,
          @netsend_address,
          0,
          0,
          @category_id)

  RETURN(@@error) -- 0 means success
END
go

/**************************************************************/
/* SP_UPDATE_OPERATOR                                         */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_update_operator...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_update_operator')
              AND (type = 'P')))
  DROP PROCEDURE sp_update_operator
go
CREATE PROCEDURE sp_update_operator
  @name                      sysname,
  @new_name                  sysname       = NULL,
  @enabled                   TINYINT       = NULL,
  @email_address             NVARCHAR(100) = NULL,
  @pager_address             NVARCHAR(100) = NULL,
  @weekday_pager_start_time  INT           = NULL, -- HHMMSS using 24 hour clock
  @weekday_pager_end_time    INT           = NULL, -- As above
  @saturday_pager_start_time INT           = NULL, -- As above
  @saturday_pager_end_time   INT           = NULL, -- As above
  @sunday_pager_start_time   INT           = NULL, -- As above
  @sunday_pager_end_time     INT           = NULL, -- As above
  @pager_days                TINYINT       = NULL,
  @netsend_address           NVARCHAR(100) = NULL, -- New for 7.0
  @category_name             sysname       = NULL  -- New for 7.0
AS
BEGIN
  DECLARE @x_enabled                   TINYINT
  DECLARE @x_email_address             NVARCHAR(100)
  DECLARE @x_pager_address             NVARCHAR(100)
  DECLARE @x_weekday_pager_start_time  INT
  DECLARE @x_weekday_pager_end_time    INT
  DECLARE @x_saturday_pager_start_time INT
  DECLARE @x_saturday_pager_end_time   INT
  DECLARE @x_sunday_pager_start_time   INT
  DECLARE @x_sunday_pager_end_time     INT
  DECLARE @x_pager_days                TINYINT
  DECLARE @x_netsend_address           NVARCHAR(100)
  DECLARE @x_category_id               INT

  DECLARE @return_code                 INT
  DECLARE @notification_method         INT
  DECLARE @alert_fail_safe_operator    sysname
  DECLARE @current_msx_server          NVARCHAR(30)
  DECLARE @category_id                 INT

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @name            = LTRIM(RTRIM(@name))
  SELECT @new_name        = LTRIM(RTRIM(@new_name))
  SELECT @email_address   = LTRIM(RTRIM(@email_address))
  SELECT @pager_address   = LTRIM(RTRIM(@pager_address))
  SELECT @netsend_address = LTRIM(RTRIM(@netsend_address))
  SELECT @category_name   = LTRIM(RTRIM(@category_name))

  -- Only a sysadmin can do this
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  -- Check if this Operator exists
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysoperators
                  WHERE (name = @name)))
  BEGIN
    RAISERROR(14262, 16, 1, '@name', @name)
    RETURN(1) -- Failure
  END

  -- Check if this operator is 'MSXOperator'
  IF (@name = N'MSXOperator')
  BEGIN
    -- Disallow the update operation if we're at a TSX for all callers other than xp_msx_enlist
    EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                           N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                           N'MSXServerName',
                                           @current_msx_server OUTPUT,
                                           N'no_output'
    IF ((@current_msx_server IS NOT NULL) AND (PROGRAM_NAME() <> N'xp_msx_enlist'))
    BEGIN
      RAISERROR(14223, 16, 1, 'MSXOperator', 'TSX')
      RETURN(1) -- Failure
    END
  END

  -- Get existing (@x_) operator property values
  SELECT @x_enabled                   = enabled,
         @x_email_address             = email_address,
         @x_pager_address             = pager_address,
         @x_weekday_pager_start_time  = weekday_pager_start_time,
         @x_weekday_pager_end_time    = weekday_pager_end_time,
         @x_saturday_pager_start_time = saturday_pager_start_time,
         @x_saturday_pager_end_time   = saturday_pager_end_time,
         @x_sunday_pager_start_time   = sunday_pager_start_time,
         @x_sunday_pager_end_time     = sunday_pager_end_time,
         @x_pager_days                = pager_days,
         @x_netsend_address           = netsend_address,
         @x_category_id               = category_id
  FROM msdb.dbo.sysoperators
  WHERE (name = @name)

  -- Fill out the values for all non-supplied parameters from the existsing values
  IF (@enabled                   IS NULL) SELECT @enabled                   = @x_enabled
  IF (@email_address             IS NULL) SELECT @email_address             = @x_email_address
  IF (@pager_address             IS NULL) SELECT @pager_address             = @x_pager_address
  IF (@weekday_pager_start_time  IS NULL) SELECT @weekday_pager_start_time  = @x_weekday_pager_start_time
  IF (@weekday_pager_end_time    IS NULL) SELECT @weekday_pager_end_time    = @x_weekday_pager_end_time
  IF (@saturday_pager_start_time IS NULL) SELECT @saturday_pager_start_time = @x_saturday_pager_start_time
  IF (@saturday_pager_end_time   IS NULL) SELECT @saturday_pager_end_time   = @x_saturday_pager_end_time
  IF (@sunday_pager_start_time   IS NULL) SELECT @sunday_pager_start_time   = @x_sunday_pager_start_time
  IF (@sunday_pager_end_time     IS NULL) SELECT @sunday_pager_end_time     = @x_sunday_pager_end_time
  IF (@pager_days                IS NULL) SELECT @pager_days                = @x_pager_days
  IF (@netsend_address           IS NULL) SELECT @netsend_address           = @x_netsend_address
  IF (@category_name             IS NULL) SELECT @category_name = name FROM msdb.dbo.syscategories WHERE (category_id = @x_category_id)

  IF (@category_name IS NULL)
  BEGIN
    SELECT @category_name = name
    FROM msdb.dbo.syscategories
    WHERE (category_id = 99)
  END

  -- Turn [nullable] empty string parameters into NULLs
  IF (@email_address   = N'') SELECT @email_address   = NULL
  IF (@pager_address   = N'') SELECT @pager_address   = NULL
  IF (@netsend_address = N'') SELECT @netsend_address = NULL
  IF (@category_name   = N'') SELECT @category_name   = NULL

  -- Verify the operator
  EXECUTE @return_code = sp_verify_operator @new_name,
                                            @enabled,
                                            @pager_days,
                                            @weekday_pager_start_time,
                                            @weekday_pager_end_time,
                                            @saturday_pager_start_time,
                                            @saturday_pager_end_time,
                                            @sunday_pager_start_time,
                                            @sunday_pager_end_time,
                                            @category_name,
                                            @category_id OUTPUT
  IF (@return_code <> 0)
    RETURN(1) -- Failure

  -- If no new name is supplied, use the old one
  -- NOTE: We must do this AFTER calling sp_verify_operator.
  IF (@new_name IS NULL)
    SELECT @new_name = @name
  ELSE
  BEGIN
    -- You can't rename the MSXOperator
    IF (@name = N'MSXOperator')
    BEGIN
      RAISERROR(14222, 16, 1, 'MSXOperator')
      RETURN(1) -- Failure
    END
  END

  -- Do the UPDATE
  UPDATE msdb.dbo.sysoperators
  SET name                      = @new_name,
      enabled                   = @enabled,
      email_address             = @email_address,
      pager_address             = @pager_address,
      weekday_pager_start_time  = @weekday_pager_start_time,
      weekday_pager_end_time    = @weekday_pager_end_time,
      saturday_pager_start_time = @saturday_pager_start_time,
      saturday_pager_end_time   = @saturday_pager_end_time,
      sunday_pager_start_time   = @sunday_pager_start_time,
      sunday_pager_end_time     = @sunday_pager_end_time,
      pager_days                = @pager_days,
      netsend_address           = @netsend_address,
      category_id               = @category_id
  WHERE (name = @name)

  -- Check if the operator is 'MSXOperator', in which case we need to re-enlist all the targets
  -- so that they will download the new MSXOperator details
  IF ((@name = N'MSXOperator') AND ((SELECT COUNT(*) FROM msdb.dbo.systargetservers) > 0))
    EXECUTE msdb.dbo.sp_post_msx_operation 'RE-ENLIST', 'SERVER', 0x00

  -- Check if this operator is the FailSafe Operator
  EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                         N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                         N'AlertFailSafeOperator',
                                         @alert_fail_safe_operator OUTPUT,
                                         N'no_output'

  -- If it is, we update the 4 'AlertFailSafe...' registry entries and AlertNotificationMethod
  IF (LTRIM(RTRIM(@alert_fail_safe_operator)) = @name)
  BEGIN
    -- Update AlertFailSafeX values
    EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
                                            N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                            N'AlertFailSafeOperator',
                                            N'REG_SZ',
                                            @new_name
    EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
                                            N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                            N'AlertFailSafeEmailAddress',
                                            N'REG_SZ',
                                            @email_address
    EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
                                            N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                            N'AlertFailSafePagerAddress',
                                            N'REG_SZ',
                                            @pager_address
    EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
                                            N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                            N'AlertFailSafeNetSendAddress',
                                            N'REG_SZ',
                                            @netsend_address

    -- Update AlertNotificationMethod values
    EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                           N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                           N'AlertNotificationMethod',
                                           @notification_method OUTPUT,
                                           N'no_output'
    IF (LTRIM(RTRIM(@email_address)) IS NULL)
      SELECT @notification_method = @notification_method & ~1
    IF (LTRIM(RTRIM(@pager_address)) IS NULL)
      SELECT @notification_method = @notification_method & ~2
    IF (LTRIM(RTRIM(@netsend_address)) IS NULL)
      SELECT @notification_method = @notification_method & ~4
    EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
                                            N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                            N'AlertNotificationMethod',
                                            N'REG_DWORD',
                                            @notification_method

    -- And finally, let SQLServerAgent know of the changes
    EXECUTE msdb.dbo.sp_sqlagent_notify @op_type = N'G'
  END

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_DELETE_OPERATOR                                         */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_delete_operator...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_operator')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_operator
go
CREATE PROCEDURE sp_delete_operator
  @name                 sysname,
  @reassign_to_operator sysname = NULL
AS
BEGIN
  DECLARE @id                         INT
  DECLARE @alert_fail_safe_operator   sysname
  DECLARE @job_id                     UNIQUEIDENTIFIER
  DECLARE @job_id_as_char             VARCHAR(36)
  DECLARE @notify_email_operator_id   INT
  DECLARE @notify_netsend_operator_id INT
  DECLARE @notify_page_operator_id    INT
  DECLARE @reassign_to_id             INT
  DECLARE @cmd                        NVARCHAR(512)
  DECLARE @current_msx_server         NVARCHAR(30)

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @name                 = LTRIM(RTRIM(@name))
  SELECT @reassign_to_operator = LTRIM(RTRIM(@reassign_to_operator))

  -- Turn [nullable] empty string parameters into NULLs
  IF (@reassign_to_operator = N'') SELECT @reassign_to_operator = NULL

  -- Only a sysadmin can do this
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  -- Check if this Operator exists
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysoperators
                  WHERE (name = @name)))
  BEGIN
    RAISERROR(14262, 16, 1, '@name', @name)
    RETURN(1) -- Failure
  END

  -- Check if this operator the FailSafe Operator
  EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                         N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                         N'AlertFailSafeOperator',
                                         @alert_fail_safe_operator OUTPUT,
                                         N'no_output'

  -- If it is, we disallow the delete operation
  IF (LTRIM(RTRIM(@alert_fail_safe_operator)) = @name)
  BEGIN
    RAISERROR(14504, 16, 1, @name, @name)
    RETURN(1) -- Failure
  END

  -- Check if this operator is 'MSXOperator'
  IF (@name = N'MSXOperator')
  BEGIN
    DECLARE @server_type VARCHAR(3)

    -- Disallow the delete operation if we're an MSX or a TSX
    EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                           N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                           N'MSXServerName',
                                           @current_msx_server OUTPUT,
                                           N'no_output'
    IF (@current_msx_server IS NOT NULL)
      SELECT @server_type = 'TSX'

    IF ((SELECT COUNT(*)
         FROM msdb.dbo.systargetservers) > 0)
      SELECT @server_type = 'MSX'

    IF (@server_type IS NOT NULL)
    BEGIN
      RAISERROR(14223, 16, 1, 'MSXOperator', @server_type)
      RETURN(1) -- Failure
    END
  END

  -- Convert the Name to it's ID
  SELECT @id = id
  FROM msdb.dbo.sysoperators
  WHERE (name = @name)

  IF (@reassign_to_operator IS NOT NULL)
  BEGIN
    -- On a TSX or standalone server, disallow re-assigning to the MSXOperator
    IF (@reassign_to_operator = N'MSXOperator') AND
       (NOT EXISTS (SELECT *
                    FROM msdb.dbo.systargetservers))
    BEGIN
      RAISERROR(14251, -1, -1, @reassign_to_operator)
      RETURN(1) -- Failure
    END

    SELECT @reassign_to_id = id
    FROM msdb.dbo.sysoperators
    WHERE (name = @reassign_to_operator)

    IF (@reassign_to_id IS NULL)
    BEGIN
      RAISERROR(14262, -1, -1, '@reassign_to_operator', @reassign_to_operator)
      RETURN(1) -- Failure
    END
  END

  -- Double up any single quotes in @reassign_to_operator
  IF (@reassign_to_operator IS NOT NULL)
    SELECT @reassign_to_operator = REPLACE(@reassign_to_operator, N'''', N'''''')

  BEGIN TRANSACTION

    -- Reassign (or delete) any sysnotifications rows that reference this operator
    IF (@reassign_to_operator IS NOT NULL)
    BEGIN
      UPDATE msdb.dbo.sysnotifications
      SET operator_id = @reassign_to_id
      WHERE (operator_id = @id)
        AND (NOT EXISTS (SELECT *
                         FROM msdb.dbo.sysnotifications sn2
                         WHERE (sn2.alert_id = msdb.dbo.sysnotifications.alert_id)
                           AND (sn2.operator_id = @reassign_to_id)))
    END

    DELETE FROM msdb.dbo.sysnotifications
    WHERE (operator_id = @id)

    -- Update any jobs that reference this operator
    DECLARE jobs_referencing_this_operator CURSOR LOCAL
    FOR
    SELECT job_id,
           notify_email_operator_id,
           notify_netsend_operator_id,
           notify_page_operator_id
    FROM msdb.dbo.sysjobs
    WHERE (notify_email_operator_id = @id)
       OR (notify_netsend_operator_id = @id)
       OR (notify_page_operator_id = @id)

    OPEN jobs_referencing_this_operator
    FETCH NEXT FROM jobs_referencing_this_operator INTO @job_id,
                                                        @notify_email_operator_id,
                                                        @notify_netsend_operator_id,
                                                        @notify_page_operator_id
    WHILE (@@fetch_status = 0)
    BEGIN
      SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
      SELECT @cmd = N'msdb.dbo.sp_update_job @job_id = ''' + @job_id_as_char + N''', '

      IF (@notify_email_operator_id = @id)
        IF (@reassign_to_operator IS NOT NULL)
          SELECT @cmd = @cmd + N'@notify_email_operator_name = N''' + @reassign_to_operator + N''', '
        ELSE
          SELECT @cmd = @cmd + N'@notify_email_operator_name = N'''', @notify_level_email = 0, '

      IF (@notify_netsend_operator_id = @id)
        IF (@reassign_to_operator IS NOT NULL)
          SELECT @cmd = @cmd + N'@notify_netsend_operator_name = N''' + @reassign_to_operator + N''', '
        ELSE
          SELECT @cmd = @cmd + N'@notify_netsend_operator_name = N'''', @notify_level_netsend = 0, '

      IF (@notify_page_operator_id = @id)
        IF (@reassign_to_operator IS NOT NULL)
          SELECT @cmd = @cmd + N'@notify_page_operator_name = N''' + @reassign_to_operator + N''', '
        ELSE
          SELECT @cmd = @cmd + N'@notify_page_operator_name = N'''', @notify_level_page = 0, '

      SELECT @cmd = SUBSTRING(@cmd, 1, (DATALENGTH(@cmd) / 2) - 2)
      EXECUTE (N'EXECUTE ' + @cmd)

      FETCH NEXT FROM jobs_referencing_this_operator INTO @job_id,
                                                          @notify_email_operator_id,
                                                          @notify_netsend_operator_id,
                                                          @notify_page_operator_id
    END
    DEALLOCATE jobs_referencing_this_operator

    -- Finally, do the actual DELETE
    DELETE FROM msdb.dbo.sysoperators
    WHERE (id = @id)

  COMMIT TRANSACTION

  RETURN(@@error) -- 0 means success
END
go

/**************************************************************/
/* SP_HELP_OPERATOR                                           */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_help_operator...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_help_operator')
              AND (type = 'P')))
  DROP PROCEDURE sp_help_operator
go
CREATE PROCEDURE sp_help_operator
  @operator_name sysname = NULL,
  @operator_id   INT     = NULL
AS
BEGIN
  DECLARE @operator_id_as_char VARCHAR(10)

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @operator_name = LTRIM(RTRIM(@operator_name))
  IF (@operator_name = '') SELECT @operator_name = NULL

  -- Check operator name
  IF (@operator_name IS NOT NULL)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysoperators
                    WHERE (name = @operator_name)))
    BEGIN
      RAISERROR(14262, -1, -1, '@operator_name', @operator_name)
      RETURN(1) -- Failure
    END
  END

  -- Check operator id
  IF (@operator_id IS NOT NULL)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysoperators
                    WHERE (id = @operator_id)))
    BEGIN
      SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id)
      RAISERROR(14262, -1, -1, '@operator_id', @operator_id_as_char)
      RETURN(1) -- Failure
    END
  END

  SELECT so.id,
         so.name,
         so.enabled,
         so.email_address,
         so.last_email_date,
         so.last_email_time,
         so.pager_address,
         so.last_pager_date,
         so.last_pager_time,
         so.weekday_pager_start_time,
         so.weekday_pager_end_time,
         so.saturday_pager_start_time,
         so.saturday_pager_end_time,
         so.sunday_pager_start_time,
         so.sunday_pager_end_time,
         so.pager_days,
         so.netsend_address,
         so.last_netsend_date,
         so.last_netsend_time,
         category_name = sc.name
  FROM msdb.dbo.sysoperators                  so
       LEFT OUTER JOIN msdb.dbo.syscategories sc ON (so.category_id = sc.category_id)
  WHERE ((@operator_name IS NULL) OR (so.name = @operator_name))
    AND ((@operator_id IS NULL) OR (so.id = @operator_id))
  ORDER BY so.name

  RETURN(@@error) -- 0 means success
END
go

/**************************************************************/
/* SP_HELP_OPERATOR_JOBS                                      */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_help_operator_jobs...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = 'sp_help_operator_jobs')
              AND (type = 'P')))
  DROP PROCEDURE sp_help_operator_jobs
go
CREATE PROCEDURE sp_help_operator_jobs
  @operator_name sysname = NULL
AS
BEGIN
  DECLARE @operator_id INT

  SET NOCOUNT ON

  -- Check operator name
  SELECT @operator_id = id
  FROM msdb.dbo.sysoperators
  WHERE (name = @operator_name)
  IF (@operator_id IS NULL)
  BEGIN
    RAISERROR(14262, -1, -1, '@operator_name', @operator_name)
    RETURN(1) -- Failure
  END

  -- Get the job info
  SELECT job_id, name, notify_level_email, notify_level_netsend, notify_level_page
  FROM msdb.dbo.sysjobs_view
  WHERE ((notify_email_operator_id = @operator_id)   AND (notify_level_email <> 0))
     OR ((notify_netsend_operator_id = @operator_id) AND (notify_level_netsend <> 0))
     OR ((notify_page_operator_id = @operator_id)    AND (notify_level_page <> 0))

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_VERIFY_NOTIFICATION                                     */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_verify_notification...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_verify_notification')
              AND (type = 'P')))
  DROP PROCEDURE sp_verify_notification
go
CREATE PROCEDURE sp_verify_notification
  @alert_name          sysname,
  @operator_name       sysname,
  @notification_method TINYINT,
  @alert_id            INT OUTPUT,
  @operator_id         INT OUTPUT
AS
BEGIN
  DECLARE @res_valid_range NVARCHAR(100)

  SET NOCOUNT ON

  SELECT @res_valid_range = FORMATMESSAGE(14208)

  -- Remove any leading/trailing spaces from parameters
  SELECT @alert_name    = LTRIM(RTRIM(@alert_name))
  SELECT @operator_name = LTRIM(RTRIM(@operator_name))

  -- Check if the AlertName is valid
  SELECT @alert_id = id
  FROM msdb.dbo.sysalerts
  WHERE (name = @alert_name)

  IF (@alert_id IS NULL)
  BEGIN
    RAISERROR(14262, 16, 1, '@alert_name', @alert_name)
    RETURN(1) -- Failure
  END

  -- Check if the OperatorName is valid
  SELECT @operator_id = id
  FROM msdb.dbo.sysoperators
  WHERE (name = @operator_name)

  IF (@operator_id IS NULL)
  BEGIN
    RAISERROR(14262, 16, 1, '@operator_name', @operator_name)
    RETURN(1) -- Failure
  END

  -- If we're at a TSX, we disallow using operator 'MSXOperator'
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.systargetservers)) AND
     (@operator_name = N'MSXOperator')
  BEGIN
    RAISERROR(14251, -1, -1, @operator_name)
    RETURN(1) -- Failure
  END

  -- Check if the NotificationMethod is valid
  IF ((@notification_method < 1) OR (@notification_method > 7))
  BEGIN
    RAISERROR(14266, 16, 1, '@notification_method', @res_valid_range)
    RETURN(1) -- Failure
  END

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_ADD_NOTIFICATION                                        */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_add_notification...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_add_notification')
              AND (type = 'P')))
  DROP PROCEDURE sp_add_notification
go
CREATE PROCEDURE sp_add_notification
  @alert_name          sysname,
  @operator_name       sysname,
  @notification_method TINYINT -- 1 = Email, 2 = Pager, 4 = NetSend, 7 = All
AS
BEGIN
  DECLARE @alert_id             INT
  DECLARE @operator_id          INT
  DECLARE @notification         NVARCHAR(512)
  DECLARE @retval               INT
  DECLARE @old_has_notification INT
  DECLARE @new_has_notification INT
  DECLARE @res_notification     NVARCHAR(100)

  SET NOCOUNT ON

  SELECT @res_notification = FORMATMESSAGE(14210)

  -- Remove any leading/trailing spaces from parameters
  SELECT @alert_name    = LTRIM(RTRIM(@alert_name))
  SELECT @operator_name = LTRIM(RTRIM(@operator_name))