  CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL, job_is_cached INT NOT NULL)

  -- Do the delete (for a specific job)
  IF (@job_id IS NOT NULL)
  BEGIN
    INSERT INTO #temp_jobs_to_delete
    SELECT job_id, (SELECT COUNT(*) 
                    FROM msdb.dbo.sysjobservers 
                    WHERE (job_id = @job_id) 
                      AND (server_id = 0))
    FROM msdb.dbo.sysjobs_view
    WHERE (job_id = @job_id)

    -- Check if we have any work to do
    IF (NOT EXISTS (SELECT *
                    FROM #temp_jobs_to_delete))
      RETURN(0) -- Success

    -- Post the delete to any target servers (need to do this BEFORE deleting the job itself,
    -- but AFTER clearing all all pending download instructions).  Note that if the job is
    -- NOT a multi-server job then sp_post_msx_operation will catch this and will do nothing.
    DELETE FROM msdb.dbo.sysdownloadlist
    WHERE (object_id = @job_id)
    EXECUTE msdb.dbo.sp_post_msx_operation 'DELETE', 'JOB', @job_id

    -- Must do this before deleting the job itself since sp_sqlagent_notify does a lookup on sysjobs_view
    EXECUTE msdb.dbo.sp_delete_job_references

    -- Delete all traces of the job
    BEGIN TRANSACTION

    DELETE FROM msdb.dbo.sysjobs
    WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

    DELETE FROM msdb.dbo.sysjobservers
    WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)
 
    DELETE FROM msdb.dbo.sysjobsteps
    WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

    DELETE FROM msdb.dbo.sysjobschedules
    WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

    IF (@delete_history = 1)
      DELETE FROM msdb.dbo.sysjobhistory
      WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

    COMMIT TRANSACTION

  END
  ELSE
  -- Do the delete (for all jobs originating from the specific server)
  IF (@originating_server IS NOT NULL)
  BEGIN
    EXECUTE msdb.dbo.sp_delete_all_msx_jobs @msx_server = @originating_server

    -- NOTE: In this case there is no need to propagate the delete via sp_post_msx_operation
    --       since this type of delete is only ever performed on a TSX.
  END

  DROP TABLE #temp_jobs_to_delete

  -- If misc. replication job, then update global replication status table.
  -- @category_id will have a value ONLY if @job_name or @job_id is provided.
  IF (@category_id IS NOT NULL AND @category_id IN (11, 12, 16, 17, 18))
  BEGIN
    -- Nothing can be done if this fails, so don't worry about the return code
    EXECUTE master.dbo.sp_MSupdate_replication_status
      @publisher = '',
      @publisher_db = '',
      @publication = '',
      @publication_type = -1,
      @agent_type = 5,
      @agent_name = @job_name,
      @status = -1 -- Delete
  END

  RETURN(0) -- 0 means success
END
go

/**************************************************************/
/* SP_GET_COMPOSITE_JOB_INFO                                  */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_get_composite_job_info...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_get_composite_job_info')
              AND (type = 'P')))
  DROP PROCEDURE sp_get_composite_job_info
go
CREATE PROCEDURE sp_get_composite_job_info
  @job_id             UNIQUEIDENTIFIER = NULL,
  @job_type           VARCHAR(12)      = NULL,  -- LOCAL or MULTI-SERVER
  @owner_login_name   sysname          = NULL,
  @subsystem          NVARCHAR(40)     = NULL,
  @category_id        INT              = NULL,
  @enabled            TINYINT          = NULL,
  @execution_status   INT              = NULL,  -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
  @date_comparator    CHAR(1)          = NULL,  -- >, < or =
  @date_created       DATETIME         = NULL,
  @date_last_modified DATETIME         = NULL,
  @description        NVARCHAR(512)    = NULL   -- We do a LIKE on this so it can include wildcards
AS
BEGIN
  DECLARE @is_sysadmin INT
  DECLARE @job_owner   sysname

  SET NOCOUNT ON

  -- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.
  -- This proc should only ever be called by sp_help_job, so we don't verify the
  -- parameters (sp_help_job has already done this).

  -- Step 1: Create intermediate work tables
  CREATE TABLE #job_execution_state (job_id                  UNIQUEIDENTIFIER NOT NULL,
                                     date_started            INT              NOT NULL,
                                     time_started            INT              NOT NULL,
                                     execution_job_status    INT              NOT NULL,
                                     execution_step_id       INT              NULL,
                                     execution_step_name     sysname          NULL,
                                     execution_retry_attempt INT              NOT NULL,
                                     next_run_date           INT              NOT NULL,
                                     next_run_time           INT              NOT NULL,
                                     next_run_schedule_id    INT              NOT NULL)
  CREATE TABLE #filtered_jobs (job_id                   UNIQUEIDENTIFIER NOT NULL,
                               date_created             DATETIME         NOT NULL,
                               date_last_modified       DATETIME         NOT NULL,
                               current_execution_status INT              NULL,
                               current_execution_step   sysname          NULL,
                               current_retry_attempt    INT              NULL,
                               last_run_date            INT              NOT NULL,
                               last_run_time            INT              NOT NULL,
                               last_run_outcome         INT              NOT NULL,
                               next_run_date            INT              NULL,
                               next_run_time            INT              NULL,
                               next_run_schedule_id     INT              NULL,
                               type                     INT              NOT NULL)
  CREATE TABLE #xp_results (job_id                UNIQUEIDENTIFIER NOT NULL,
                            last_run_date         INT              NOT NULL,
                            last_run_time         INT              NOT NULL,
                            next_run_date         INT              NOT NULL,
                            next_run_time         INT              NOT NULL,
                            next_run_schedule_id  INT              NOT NULL,
                            requested_to_run      INT              NOT NULL, -- BOOL
                            request_source        INT              NOT NULL,
                            request_source_id     sysname          NULL,
                            running               INT              NOT NULL, -- BOOL
                            current_step          INT              NOT NULL,
                            current_retry_attempt INT              NOT NULL,
                            job_state             INT              NOT NULL)

  -- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
  SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
  SELECT @job_owner = SUSER_SNAME()

  INSERT INTO #xp_results
  EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

  INSERT INTO #job_execution_state
  SELECT xpr.job_id,
         xpr.last_run_date,
         xpr.last_run_time,
         xpr.job_state,
         sjs.step_id,
         sjs.step_name,
         xpr.current_retry_attempt,
         xpr.next_run_date,
         xpr.next_run_time,
         xpr.next_run_schedule_id
  FROM #xp_results                          xpr
       LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
       msdb.dbo.sysjobs_view                sjv
  WHERE (sjv.job_id = xpr.job_id)

  -- Step 3: Filter on everything but dates and job_type
  IF ((@subsystem        IS NULL) AND
      (@owner_login_name IS NULL) AND
      (@enabled          IS NULL) AND
      (@category_id      IS NULL) AND
      (@execution_status IS NULL) AND
      (@description      IS NULL) AND
      (@job_id           IS NULL))
  BEGIN
    -- Optimize for the frequently used case...
    INSERT INTO #filtered_jobs
    SELECT sjv.job_id,
           sjv.date_created,
           sjv.date_modified,
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
           CASE ISNULL(jes.execution_step_id, 0)
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in #job_execution_state
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
           END,
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in #job_execution_state
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in #job_execution_state
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in #job_execution_state
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in #job_execution_state
           0   -- type placeholder             (we'll fix it up in step 3.4)
    FROM msdb.dbo.sysjobs_view                sjv
         LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
  END
  ELSE
  BEGIN
    INSERT INTO #filtered_jobs
    SELECT DISTINCT
           sjv.job_id,
           sjv.date_created,
           sjv.date_modified,
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in #job_execution_state (NOTE: 4 = STATE_IDLE)
           CASE ISNULL(jes.execution_step_id, 0)
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in #job_execution_state
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
           END,
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in #job_execution_state
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in #job_execution_state
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in #job_execution_state
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in #job_execution_state
           0   -- type placeholder             (we'll fix it up in step 3.4)
    FROM msdb.dbo.sysjobs_view                sjv
         LEFT OUTER JOIN #job_execution_state jes ON (sjv.job_id = jes.job_id)
         LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)
    WHERE ((@subsystem        IS NULL) OR (sjs.subsystem            = @subsystem))
      AND ((@owner_login_name IS NULL) OR (sjv.owner_sid            = SUSER_SID(@owner_login_name)))
      AND ((@enabled          IS NULL) OR (sjv.enabled              = @enabled))
      AND ((@category_id      IS NULL) OR (sjv.category_id          = @category_id))
      AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status)) 
                                       OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))
      AND ((@description      IS NULL) OR (sjv.description       LIKE @description))
      AND ((@job_id           IS NULL) OR (sjv.job_id               = @job_id))
  END

  -- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'
  UPDATE #filtered_jobs
  SET current_execution_status = NULL
  WHERE (current_execution_status = 4)
    AND (job_id IN (SELECT job_id
                    FROM msdb.dbo.sysjobservers
                    WHERE (server_id <> 0)))

  -- Step 3.2: Check that if the user asked to see idle jobs that we still have some.
  --           If we don't have any then the query should return no rows.
  IF (@execution_status = 4) AND
     (NOT EXISTS (SELECT *
                  FROM #filtered_jobs
                  WHERE (current_execution_status = 4)))
  BEGIN
    TRUNCATE TABLE #filtered_jobs
  END

  -- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for
  --           multi-server jobs there are multiple last run details in sysjobservers, so
  --           we simply choose the most recent].
  IF (EXISTS (SELECT *
              FROM msdb.dbo.systargetservers))
  BEGIN
    UPDATE #filtered_jobs
    SET last_run_date = sjs.last_run_date,
        last_run_time = sjs.last_run_time,
        last_run_outcome = sjs.last_run_outcome
    FROM #filtered_jobs         fj,
         msdb.dbo.sysjobservers sjs
    WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =
           (SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)
            FROM msdb.dbo.sysjobservers
            WHERE (job_id = sjs.job_id))
      AND (fj.job_id = sjs.job_id)
  END
  ELSE
  BEGIN
    UPDATE #filtered_jobs
    SET last_run_date = sjs.last_run_date,
        last_run_time = sjs.last_run_time,
        last_run_outcome = sjs.last_run_outcome
    FROM #filtered_jobs         fj,
         msdb.dbo.sysjobservers sjs
    WHERE (fj.job_id = sjs.job_id)
  END

  -- Step 3.4 : Set the type of the job to local (1) or multi-server (2)
  --            NOTE: If the job has no jobservers then it wil have a type of 0 meaning
  --                  unknown.  This is marginally inconsistent with the behaviour of
  --                  defaulting the category of a new job to [Uncategorized (Local)], but
  --                  prevents incompletely defined jobs from erroneously showing up as valid
  --                  local jobs.
  UPDATE #filtered_jobs
  SET type = 1 -- LOCAL
  FROM #filtered_jobs         fj,
       msdb.dbo.sysjobservers sjs
  WHERE (fj.job_id = sjs.job_id)
    AND (server_id = 0)
  UPDATE #filtered_jobs
  SET type = 2 -- MULTI-SERVER
  FROM #filtered_jobs         fj,
       msdb.dbo.sysjobservers sjs
  WHERE (fj.job_id = sjs.job_id)
    AND (server_id <> 0)

  -- Step 4: Filter on job_type
  IF (@job_type IS NOT NULL)
  BEGIN
    IF (UPPER(@job_type) = 'LOCAL')
      DELETE FROM #filtered_jobs
      WHERE (type <> 1) -- IE. Delete all the non-local jobs
    IF (UPPER(@job_type) = 'MULTI-SERVER')
      DELETE FROM #filtered_jobs
      WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs
  END

  -- Step 5: Filter on dates
  IF (@date_comparator IS NOT NULL)
  BEGIN
    IF (@date_created IS NOT NULL)
    BEGIN
      IF (@date_comparator = '=')
        DELETE FROM #filtered_jobs WHERE (date_created <> @date_created)
      IF (@date_comparator = '>')
        DELETE FROM #filtered_jobs WHERE (date_created <= @date_created)
      IF (@date_comparator = '<')
        DELETE FROM #filtered_jobs WHERE (date_created >= @date_created)
    END
    IF (@date_last_modified IS NOT NULL)
    BEGIN
      IF (@date_comparator = '=')
        DELETE FROM #filtered_jobs WHERE (date_last_modified <> @date_last_modified)
      IF (@date_comparator = '>')
        DELETE FROM #filtered_jobs WHERE (date_last_modified <= @date_last_modified)
      IF (@date_comparator = '<')
        DELETE FROM #filtered_jobs WHERE (date_last_modified >= @date_last_modified)
    END
  END

  -- Return the result set (NOTE: No filtering occurs here)
  SELECT sjv.job_id,
         sjv.originating_server,
         sjv.name,
         sjv.enabled,
         sjv.description,
         sjv.start_step_id,
         category = ISNULL(sc.name, FORMATMESSAGE(14205)),
         owner = SUSER_SNAME(sjv.owner_sid),
         sjv.notify_level_eventlog,
         sjv.notify_level_email,
         sjv.notify_level_netsend,
         sjv.notify_level_page,
         notify_email_operator   = ISNULL(so1.name, FORMATMESSAGE(14205)),
         notify_netsend_operator = ISNULL(so2.name, FORMATMESSAGE(14205)),
         notify_page_operator    = ISNULL(so3.name, FORMATMESSAGE(14205)),
         sjv.delete_level,
         sjv.date_created,
         sjv.date_modified,
         sjv.version_number,
         fj.last_run_date,
         fj.last_run_time,
         fj.last_run_outcome,
         next_run_date = ISNULL(fj.next_run_date, 0),                                 -- This column will be NULL if the job is non-local
         next_run_time = ISNULL(fj.next_run_time, 0),                                 -- This column will be NULL if the job is non-local
         next_run_schedule_id = ISNULL(fj.next_run_schedule_id, 0),                   -- This column will be NULL if the job is non-local
         current_execution_status = ISNULL(fj.current_execution_status, 0),           -- This column will be NULL if the job is non-local
         current_execution_step = ISNULL(fj.current_execution_step, N'0 ' + FORMATMESSAGE(14205)), -- This column will be NULL if the job is non-local
         current_retry_attempt = ISNULL(fj.current_retry_attempt, 0),                 -- This column will be NULL if the job is non-local
         has_step = (SELECT COUNT(*) 
                     FROM msdb.dbo.sysjobsteps sjst
                     WHERE (sjst.job_id = sjv.job_id)),
         has_schedule = (SELECT COUNT(*) 
                         FROM msdb.dbo.sysjobschedules sjsch 
                         WHERE (sjsch.job_id = sjv.job_id)),
         has_target = (SELECT COUNT(*)
                       FROM msdb.dbo.sysjobservers sjs
                       WHERE (sjs.job_id = sjv.job_id)),
         type = fj.type
  FROM #filtered_jobs                         fj
       LEFT OUTER JOIN msdb.dbo.sysjobs_view  sjv ON (fj.job_id = sjv.job_id)
       LEFT OUTER JOIN msdb.dbo.sysoperators  so1 ON (sjv.notify_email_operator_id = so1.id)
       LEFT OUTER JOIN msdb.dbo.sysoperators  so2 ON (sjv.notify_netsend_operator_id = so2.id)
       LEFT OUTER JOIN msdb.dbo.sysoperators  so3 ON (sjv.notify_page_operator_id = so3.id)
       LEFT OUTER JOIN msdb.dbo.syscategories sc  ON (sjv.category_id = sc.category_id)
  ORDER BY sjv.job_id

  -- Clean up
  DROP TABLE #job_execution_state
  DROP TABLE #filtered_jobs
  DROP TABLE #xp_results
END
go

/**************************************************************/
/* SP_HELP_JOB                                                */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_help_job...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_help_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_help_job
go
CREATE PROCEDURE sp_help_job
  -- Individual job parameters
  @job_id                     UNIQUEIDENTIFIER = NULL,  -- If provided should NOT also provide job_name
  @job_name                   sysname          = NULL,  -- If provided should NOT also provide job_id
  @job_aspect                 VARCHAR(9)       = NULL,  -- JOB, STEPS, SCEDULES, TARGETS or ALL
  -- Job set parameters
  @job_type                   VARCHAR(12)      = NULL,  -- LOCAL or MULTI-SERVER
  @owner_login_name           sysname          = NULL,
  @subsystem                  NVARCHAR(40)     = NULL,
  @category_name              sysname          = NULL,
  @enabled                    TINYINT          = NULL,
  @execution_status           INT              = NULL,  -- 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions
  @date_comparator            CHAR(1)          = NULL,  -- >, < or =
  @date_created               DATETIME         = NULL,
  @date_last_modified         DATETIME         = NULL,
  @description                NVARCHAR(512)    = NULL   -- We do a LIKE on this so it can include wildcards
AS
BEGIN
  DECLARE @retval          INT
  DECLARE @category_id     INT
  DECLARE @job_id_as_char  VARCHAR(36)
  DECLARE @res_valid_range NVARCHAR(200)

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters (except @owner_login_name)
  SELECT @job_name         = LTRIM(RTRIM(@job_name))
  SELECT @job_aspect       = LTRIM(RTRIM(@job_aspect))
  SELECT @job_type         = LTRIM(RTRIM(@job_type))
  SELECT @subsystem        = LTRIM(RTRIM(@subsystem))
  SELECT @category_name    = LTRIM(RTRIM(@category_name))
  SELECT @description      = LTRIM(RTRIM(@description))

  -- Turn [nullable] empty string parameters into NULLs
  IF (@job_name         = N'') SELECT @job_name = NULL
  IF (@job_aspect       = '')  SELECT @job_aspect = NULL
  IF (@job_type         = '')  SELECT @job_type = NULL
  IF (@owner_login_name = N'') SELECT @owner_login_name = NULL
  IF (@subsystem        = N'') SELECT @subsystem = NULL
  IF (@category_name    = N'') SELECT @category_name = NULL
  IF (@description      = N'') SELECT @description = NULL

  IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL))
  BEGIN
    EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                                '@job_id',
                                                 @job_name OUTPUT,
                                                 @job_id   OUTPUT
    IF (@retval <> 0)
      RETURN(1) -- Failure
  END

  SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)

  -- If the user provided a job name or id but no aspect, default to ALL
  IF ((@job_name IS NOT NULL) OR (@job_id IS NOT NULL)) AND (@job_aspect IS NULL)
    SELECT @job_aspect = 'ALL'

  -- The caller must supply EITHER job name (or job id) and aspect OR one-or-more of the set
  -- parameters OR no parameters at all
  IF (((@job_name IS NOT NULL) OR (@job_id IS NOT NULL))
      AND ((@job_aspect          IS NULL)     OR
           (@job_type            IS NOT NULL) OR
           (@owner_login_name    IS NOT NULL) OR
           (@subsystem           IS NOT NULL) OR
           (@category_name       IS NOT NULL) OR
           (@enabled             IS NOT NULL) OR
           (@date_comparator     IS NOT NULL) OR
           (@date_created        IS NOT NULL) OR
           (@date_last_modified  IS NOT NULL)))
     OR
     ((@job_name IS NULL) AND (@job_id IS NULL) AND (@job_aspect IS NOT NULL))
  BEGIN
    RAISERROR(14280, -1, -1)
    RETURN(1) -- Failure
  END

  IF (@job_id IS NOT NULL)
  BEGIN
    -- Individual job...

    -- Check job aspect
    SELECT @job_aspect = UPPER(@job_aspect)
    IF (@job_aspect NOT IN ('JOB', 'STEPS', 'SCHEDULES', 'TARGETS', 'ALL'))
    BEGIN
      RAISERROR(14266, -1, -1, '@job_aspect', 'JOB, STEPS, SCHEDULES, TARGETS, ALL')
      RETURN(1) -- Failure
    END

    -- Generate results set...

    IF (@job_aspect IN ('JOB', 'ALL'))
    BEGIN
      IF (@job_aspect = 'ALL')
      BEGIN
        RAISERROR(14213, 0, 1)
        PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14213)) / 2)
      END
      EXECUTE sp_get_composite_job_info @job_id,
                                        @job_type,
                                        @owner_login_name,
                                        @subsystem,
                                        @category_id,
                                        @enabled,
                                        @execution_status,
                                        @date_comparator,
                                        @date_created,
                                        @date_last_modified,
                                        @description
    END

    IF (@job_aspect IN ('STEPS', 'ALL'))
    BEGIN
      IF (@job_aspect = 'ALL')
      BEGIN
        PRINT ''
        RAISERROR(14214, 0, 1)
        PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14214)) / 2)
      END
      EXECUTE ('EXECUTE sp_help_jobstep @job_id = ''' + @job_id_as_char + ''', @suffix = 1')
    END

    IF (@job_aspect IN ('SCHEDULES', 'ALL'))
    BEGIN
      IF (@job_aspect = 'ALL')
      BEGIN
        PRINT ''
        RAISERROR(14215, 0, 1)
        PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14215)) / 2)
      END
      EXECUTE ('EXECUTE sp_help_jobschedule @job_id = ''' + @job_id_as_char + '''')
    END

    IF (@job_aspect IN ('TARGETS', 'ALL'))
    BEGIN
      IF (@job_aspect = 'ALL')
      BEGIN
        PRINT ''
        RAISERROR(14216, 0, 1)
        PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14216)) / 2)
      END
      EXECUTE ('EXECUTE sp_help_jobserver @job_id = ''' + @job_id_as_char + ''', @show_last_run_details = 1')
    END
  END
  ELSE
  BEGIN
    -- Set of jobs...

    -- Check job type
    IF (@job_type IS NOT NULL)
    BEGIN
      SELECT @job_type = UPPER(@job_type)
      IF (@job_type NOT IN ('LOCAL', 'MULTI-SERVER'))
      BEGIN
        RAISERROR(14266, -1, -1, '@job_type', 'LOCAL, MULTI-SERVER')
        RETURN(1) -- Failure
      END
    END

    -- Check owner
    IF (@owner_login_name IS NOT NULL)
    BEGIN
      IF (SUSER_SID(@owner_login_name) IS NULL)
      BEGIN
        RAISERROR(14262, -1, -1, '@owner_login_name', @owner_login_name)
        RETURN(1) -- Failure
      END
    END

    -- Check subsystem
    IF (@subsystem IS NOT NULL)
    BEGIN
      EXECUTE @retval = sp_verify_subsystem @subsystem
      IF (@retval <> 0)
        RETURN(1) -- Failure
    END

    -- Check job category
    IF (@category_name IS NOT NULL)
    BEGIN
      SELECT @category_id = category_id
      FROM msdb.dbo.syscategories
      WHERE (category_class = 1) -- Job
        AND (name = @category_name)
      IF (@category_id IS NULL)
      BEGIN
        RAISERROR(14262, -1, -1, '@category_name', @category_name)
        RETURN(1) -- Failure
      END
    END

    -- Check enabled state
    IF (@enabled IS NOT NULL) AND (@enabled NOT IN (0, 1))
    BEGIN
      RAISERROR(14266, -1, -1, '@enabled', '0, 1')
      RETURN(1) -- Failure
    END

    -- Check current execution status
    IF (@execution_status IS NOT NULL)
    BEGIN
      IF (@execution_status NOT IN (0, 1, 2, 3, 4, 5, 7))
      BEGIN
        SELECT @res_valid_range = FORMATMESSAGE(14204)
        RAISERROR(14266, -1, -1, '@execution_status', @res_valid_range)
        RETURN(1) -- Failure
      END
    END

    -- If a date comparator is supplied, we must have either a date-created or date-last-modified
    IF ((@date_comparator IS NOT NULL) AND (@date_created IS NOT NULL) AND (@date_last_modified IS NOT NULL)) OR
       ((@date_comparator IS NULL)     AND ((@date_created IS NOT NULL) OR (@date_last_modified IS NOT NULL)))
    BEGIN
      RAISERROR(14282, -1, -1)
      RETURN(1) -- Failure
    END

    -- Check dates / comparator
    IF (@date_comparator IS NOT NULL) AND (@date_comparator NOT IN ('=', '<', '>'))
    BEGIN
      RAISERROR(14266, -1, -1, '@date_comparator', '=, >, <')
      RETURN(1) -- Failure
    END
    IF (@date_created IS NOT NULL) AND
       ((@date_created < '1 Jan 1990 12:00:00am') OR (@date_created > '31 Dec 9999 11:59:59pm'))
    BEGIN
      RAISERROR(14266, -1, -1, '@date_created', '1/1/1990 12:00am .. 12/31/9999 11:59pm')
      RETURN(1) -- Failure
    END
    IF (@date_last_modified IS NOT NULL) AND
       ((@date_last_modified < '1 Jan 1990 12:00am') OR (@date_last_modified > 'Dec 31 9999 11:59:59pm'))
    BEGIN
      RAISERROR(14266, -1, -1, '@date_last_modified', '1/1/1990 12:00am .. 12/31/9999 11:59pm')
      RETURN(1) -- Failure
    END

    -- Generate results set...
    EXECUTE sp_get_composite_job_info @job_id,
                                      @job_type,
                                      @owner_login_name,
                                      @subsystem,
                                      @category_id,
                                      @enabled,
                                      @execution_status,
                                      @date_comparator,
                                      @date_created,
                                      @date_last_modified,
                                      @description
  END

  RETURN(0) -- Success
END
go

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go

/**************************************************************/
/* SP_MANAGE_JOBS_BY_LOGIN                                    */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_manage_jobs_by_login...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_manage_jobs_by_login')
              AND (type = 'P')))
  DROP PROCEDURE sp_manage_jobs_by_login
go
CREATE PROCEDURE sp_manage_jobs_by_login
  @action                   VARCHAR(10), -- DELETE or REASSIGN
  @current_owner_login_name sysname,
  @new_owner_login_name     sysname = NULL
AS
BEGIN
  DECLARE @current_sid   VARBINARY(85)
  DECLARE @new_sid       VARBINARY(85)
  DECLARE @job_id        UNIQUEIDENTIFIER
  DECLARE @rows_affected INT
  DECLARE @is_sysadmin   INT

  SET NOCOUNT ON

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

  -- Turn [nullable] empty string parameters into NULLs
  IF (@new_owner_login_name = N'') SELECT @new_owner_login_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

  -- Check action
  IF (@action NOT IN ('DELETE', 'REASSIGN'))
  BEGIN
    RAISERROR(14266, -1, -1, '@action', 'DELETE, REASSIGN')
    RETURN(1) -- Failure
  END

  -- Check parameter combinations
  IF ((@action = 'DELETE') AND (@new_owner_login_name IS NOT NULL))
    RAISERROR(14281, 0, 1)

  IF ((@action = 'REASSIGN') AND (@new_owner_login_name IS NULL))
  BEGIN
    RAISERROR(14237, -1, -1)
    RETURN(1) -- Failure
  END

  -- Check current login
  SELECT @current_sid = SUSER_SID(@current_owner_login_name)
  IF (@current_sid IS NULL)
  BEGIN
    RAISERROR(14262, -1, -1, '@current_owner_login_name', @current_owner_login_name)
    RETURN(1) -- Failure
  END

  -- Check new login (if supplied)
  IF (@new_owner_login_name IS NOT NULL)
  BEGIN
    SELECT @new_sid = SUSER_SID(@new_owner_login_name)
    IF (@new_sid IS NULL)
    BEGIN
      RAISERROR(14262, -1, -1, '@new_owner_login_name', @new_owner_login_name)
      RETURN(1) -- Failure
    END
  END

  IF (@action = 'DELETE')
  BEGIN
    DECLARE jobs_to_delete CURSOR LOCAL
    FOR 
    SELECT job_id
    FROM msdb.dbo.sysjobs
    WHERE (owner_sid = @current_sid)

    OPEN jobs_to_delete
    FETCH NEXT FROM jobs_to_delete INTO @job_id

    SELECT @rows_affected = 0
    WHILE (@@fetch_status = 0)
    BEGIN
      EXECUTE sp_delete_job @job_id = @job_id
      SELECT @rows_affected = @rows_affected + 1
      FETCH NEXT FROM jobs_to_delete INTO @job_id
    END
    DEALLOCATE jobs_to_delete
    RAISERROR(14238, 0, 1, @rows_affected)
  END
  ELSE
  IF (@action = 'REASSIGN')
  BEGIN
    -- Check if the current owner owns any multi-server jobs.  
    -- If they do, then the new owner must be member of the sysadmin role.
    IF (EXISTS (SELECT *
                FROM msdb.dbo.sysjobs       sj,
                     msdb.dbo.sysjobservers sjs
                WHERE (sj.job_id = sjs.job_id)
                  AND (sj.owner_sid = @current_sid)
                  AND (sjs.server_id <> 0)))
    BEGIN
      SELECT @is_sysadmin = 0
      EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @new_owner_login_name, @is_sysadmin_member = @is_sysadmin OUTPUT
      IF (@is_sysadmin = 0)
      BEGIN
        RAISERROR(14543, -1, -1, @current_owner_login_name, N'sysadmin')
        RETURN(1) -- Failure
      END
    END

    UPDATE msdb.dbo.sysjobs
    SET owner_sid = @new_sid
    WHERE (owner_sid = @current_sid)
    RAISERROR(14239, 0, 1, @@rowcount, @new_owner_login_name)
  END

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_APPLY_JOB_TO_TARGETS                                    */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_apply_job_to_targets...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_apply_job_to_targets')
              AND (type = 'P')))
  DROP PROCEDURE sp_apply_job_to_targets
go
CREATE PROCEDURE sp_apply_job_to_targets
  @job_id               UNIQUEIDENTIFIER = NULL,   -- Must provide either this or job_name
  @job_name             sysname          = NULL,   -- Must provide either this or job_id
  @target_server_groups NVARCHAR(2048)   = NULL,   -- A comma-separated list of target server groups
  @target_servers       NVARCHAR(2048)   = NULL,   -- An comma-separated list of target servers
  @operation            VARCHAR(7)       = 'APPLY' -- Or 'REMOVE'
AS
BEGIN
  DECLARE @retval        INT
  DECLARE @rows_affected INT
  DECLARE @server_name   NVARCHAR(30)
  DECLARE @groups        NVARCHAR(2048)
  DECLARE @group         sysname
  DECLARE @servers       NVARCHAR(2048)
  DECLARE @server        NVARCHAR(30)
  DECLARE @pos_of_comma  INT

  SET NOCOUNT ON

  -- 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

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

  -- Turn [nullable] empty string parameters into NULLs
  IF (@target_server_groups = NULL) SELECT @target_server_groups = NULL
  IF (@target_servers       = NULL) SELECT @target_servers = NULL
  IF (@operation            = NULL) SELECT @operation = NULL

  EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure

  -- Check operation type
  IF ((@operation <> 'APPLY') AND (@operation <> 'REMOVE'))
  BEGIN
    RAISERROR(14266, -1, -1, '@operation', 'APPLY, REMOVE')
    RETURN(1) -- Failure
  END

  CREATE TABLE #temp_groups (group_name sysname NOT NULL)
  CREATE TABLE #temp_server_name (server_name NVARCHAR(30) NOT NULL)

  -- Check that we have a target server group list and/or a target server list
  IF ((@target_server_groups IS NULL) AND (@target_servers IS NULL))
  BEGIN
    RAISERROR(14283, -1, -1)
    RETURN(1) -- Failure
  END

  -- Parse the Target Server comma-separated list (if supplied)
  IF (@target_servers IS NOT NULL)
  BEGIN
    SELECT @servers = @target_servers
    SELECT @pos_of_comma = CHARINDEX(N',', @servers)
    WHILE (@pos_of_comma <> 0)
    BEGIN
      SELECT @server = SUBSTRING(@servers, 1, @pos_of_comma - 1)
      INSERT INTO #temp_server_name (server_name) VALUES (LTRIM(RTRIM(@server)))
      SELECT @servers = RIGHT(@servers, (DATALENGTH(@servers) / 2) - @pos_of_comma)
      SELECT @pos_of_comma = CHARINDEX(N',', @servers)
    END
    INSERT INTO #temp_server_name (server_name) VALUES (LTRIM(RTRIM(@servers)))
  END

  -- Parse the Target Server Groups comma-separated list
  IF (@target_server_groups IS NOT NULL)
  BEGIN
    SELECT @groups = @target_server_groups
    SELECT @pos_of_comma = CHARINDEX(N',', @groups)
    WHILE (@pos_of_comma <> 0)
    BEGIN
      SELECT @group = SUBSTRING(@groups, 1, @pos_of_comma - 1)
      INSERT INTO #temp_groups (group_name) VALUES (LTRIM(RTRIM(@group)))
      SELECT @groups = RIGHT(@groups, (DATALENGTH(@groups) / 2) - @pos_of_comma)
      SELECT @pos_of_comma = CHARINDEX(N',', @groups)
    END
    INSERT INTO #temp_groups (group_name) VALUES (LTRIM(RTRIM(@groups)))
  END

  -- Check server groups
  SET ROWCOUNT 1 -- We do this so that we catch the FIRST invalid group
  SELECT @group = NULL
  SELECT @group = group_name
  FROM #temp_groups
  WHERE group_name NOT IN (SELECT name
                           FROM msdb.dbo.systargetservergroups)
  IF (@group IS NOT NULL)
  BEGIN
    RAISERROR(14262, -1, -1, '@target_server_groups', @group)
    RETURN(1) -- Failure
  END
  SET ROWCOUNT 0

  -- Find the distinct list of servers being targeted
  INSERT INTO #temp_server_name (server_name)
  SELECT DISTINCT sts.server_name
  FROM msdb.dbo.systargetservergroups       stsg,
       msdb.dbo.systargetservergroupmembers stsgm,
       msdb.dbo.systargetservers            sts
  WHERE (stsg.name IN (SELECT group_name FROM #temp_groups))
    AND (stsg.servergroup_id = stsgm.servergroup_id)
    AND (stsgm.server_id = sts.server_id)
    AND (sts.server_name NOT IN (SELECT server_name
                                 FROM #temp_server_name))

  IF (@operation = 'APPLY')
  BEGIN
    -- Remove those servers to which the job has already been applied
    DELETE FROM #temp_server_name
    WHERE server_name IN (SELECT sts.server_name
                          FROM msdb.dbo.sysjobservers    sjs,
                               msdb.dbo.systargetservers sts
                          WHERE (sjs.job_id = @job_id)
                            AND (sjs.server_id = sts.server_id))
  END

  IF (@operation = 'REMOVE')
  BEGIN
    -- Remove those servers to which the job is not currently applied
    DELETE FROM #temp_server_name
    WHERE server_name NOT IN (SELECT sts.server_name
                              FROM msdb.dbo.sysjobservers    sjs,
                                   msdb.dbo.systargetservers sts
                              WHERE (sjs.job_id = @job_id)
                                AND (sjs.server_id = sts.server_id))
  END

  SELECT @rows_affected = COUNT(*)
  FROM #temp_server_name

  SET ROWCOUNT 1
  WHILE (EXISTS (SELECT *
                 FROM #temp_server_name))
  BEGIN
    SELECT @server_name = server_name
    FROM #temp_server_name
    IF (@operation = 'APPLY')
      EXECUTE sp_add_jobserver @job_id = @job_id, @server_name = @server_name
    ELSE
    IF (@operation = 'REMOVE')
      EXECUTE sp_delete_jobserver @job_id = @job_id, @server_name = @server_name
    DELETE FROM #temp_server_name
    WHERE (server_name = @server_name)
  END
  SET ROWCOUNT 0

  IF (@operation = 'APPLY')
    RAISERROR(14240, 0, 1, @rows_affected)
  IF (@operation = 'REMOVE')
    RAISERROR(14241, 0, 1, @rows_affected)

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_REMOVE_JOB_FROM_TARGETS                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_remove_job_from_targets...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_remove_job_from_targets')
              AND (type = 'P')))
  DROP PROCEDURE sp_remove_job_from_targets
go
CREATE PROCEDURE sp_remove_job_from_targets
  @job_id               UNIQUEIDENTIFIER = NULL,   -- Must provide either this or job_name
  @job_name             sysname          = NULL,   -- Must provide either this or job_id
  @target_server_groups NVARCHAR(1024)   = NULL,   -- A comma-separated list of target server groups
  @target_servers       NVARCHAR(1024)   = NULL    -- A comma-separated list of target servers
AS
BEGIN
  DECLARE @retval INT

  SET NOCOUNT ON

  EXECUTE @retval = sp_apply_job_to_targets @job_id,
                                            @job_name,
                                            @target_server_groups,
                                            @target_servers,
                                           'REMOVE'
  RETURN(@retval) -- 0 means success
END
go

/**************************************************************/
/* SP_GET_JOB_ALERTS                                          */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_get_job_alerts...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_get_job_alerts')
              AND (type = 'P')))
  DROP PROCEDURE sp_get_job_alerts
go
CREATE PROCEDURE sp_get_job_alerts 
  @job_id   UNIQUEIDENTIFIER = NULL,
  @job_name sysname          = NULL
AS
BEGIN
  DECLARE @retval INT

  EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure

  SELECT id,
         name, 
         enabled, 
         type = CASE ISNULL(performance_condition, N'!')
                  WHEN N'!' THEN
                    CASE event_source 
                      WHEN N'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
  WHERE (job_id = @job_id)

  RETURN(0) -- Success
END
go

/**************************************************************/
/*                                                            */
/*   S  U  P  P  O  R  T     P  R  O  C  E  D  U  R  E  S     */
/*                                                            */
/**************************************************************/

/**************************************************************/
/* SP_CONVERT_JOBID_TO_CHAR [used by SEM only]                */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_convert_jobid_to_char...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_convert_jobid_to_char')
              AND (type = 'P')))
  DROP PROCEDURE sp_convert_jobid_to_char
go
CREATE PROCEDURE sp_convert_jobid_to_char
  @job_id         UNIQUEIDENTIFIER,
  @job_id_as_char NVARCHAR(34) OUTPUT -- 34 because of the leading '0x'
AS
BEGIN
  DECLARE @job_id_as_binary BINARY(16)
  DECLARE @temp             NCHAR(8)
  DECLARE @counter          INT
  DECLARE @byte_value       INT
  DECLARE @high_word        INT
  DECLARE @low_word         INT
  DECLARE @high_high_nybble INT
  DECLARE @high_low_nybble  INT
  DECLARE @low_high_nybble  INT
  DECLARE @low_low_nybble   INT

  SET NOCOUNT ON

  SELECT @job_id_as_binary = CONVERT(BINARY(16), @job_id)
  SELECT @temp = CONVERT(NCHAR(8), @job_id_as_binary)

  SELECT @job_id_as_char = N''
  SELECT @counter = 1

  WHILE (@counter <= (DATALENGTH(@temp) / 2))
  BEGIN
    SELECT @byte_value       = CONVERT(INT, CONVERT(BINARY(2), SUBSTRING(@temp, @counter, 1)))
    SELECT @high_word        = (@byte_value & 0xff00) / 0x100
    SELECT @low_word         = (@byte_value & 0x00ff)
    SELECT @high_high_nybble = (@high_word & 0xff) / 16
    SELECT @high_low_nybble  = (@high_word & 0xff) % 16
    SELECT @low_high_nybble  = (@low_word & 0xff) / 16
    SELECT @low_low_nybble   = (@low_word & 0xff) % 16

    IF (@high_high_nybble < 10)
      SELECT @job_id_as_char = @job_id_as_char + NCHAR(ASCII('0') + @high_high_nybble)
    ELSE
      SELECT @job_id_as_char = @job_id_as_char + NCHAR(ASCII('A') + (@high_high_nybble - 10))

    IF (@high_low_nybble < 10)
      SELECT @job_id_as_char = @job_id_as_char + NCHAR(ASCII('0') + @high_low_nybble)
    ELSE
      SELECT @job_id_as_char = @job_id_as_char + NCHAR(ASCII('A') + (@high_low_nybble - 10))

    IF (@low_high_nybble < 10)
      SELECT @job_id_as_char = @job_id_as_char + NCHAR(ASCII('0') + @low_high_nybble)
    ELSE
      SELECT @job_id_as_char = @job_id_as_char + NCHAR(ASCII('A') + (@low_high_nybble - 10))

    IF (@low_low_nybble < 10)
      SELECT @job_id_as_char = @job_id_as_char + NCHAR(ASCII('0') + @low_low_nybble)
    ELSE
      SELECT @job_id_as_char = @job_id_as_char + NCHAR(ASCII('A') + (@low_low_nybble - 10))

    SELECT @counter = @counter + 1
  END

  SELECT @job_id_as_char = N'0x' + LOWER(@job_id_as_char)
END
go

/**************************************************************/
/* SP_START_JOB                                               */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_start_job...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_start_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_start_job
go
CREATE PROCEDURE sp_start_job
  @job_name    sysname          = NULL,
  @job_id      UNIQUEIDENTIFIER = NULL,
  @error_flag  INT              = 1,    -- Set to 0 to suppress the error from sp_sqlagent_notify if SQLServerAgent is not running
  @server_name NVARCHAR(30)     = NULL, -- The specific target server to start the [multi-server] job on
  @step_name   sysname          = NULL, -- The name of the job step to start execution with [for use with a local job only] 
  @output_flag INT              = 1     -- Set to 0 to suppress the success message
AS
BEGIN
  DECLARE @job_id_as_char VARCHAR(36)
  DECLARE @retval         INT
  DECLARE @step_id        INT

  SET NOCOUNT ON

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

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

  EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure

  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysjobservers
                  WHERE (job_id = @job_id)))
  BEGIN
    SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
    RAISERROR(14256, -1, -1, @job_name, @job_id_as_char)
    RETURN(1) -- Failure
  END

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysjobservers
              WHERE (job_id = @job_id)
                AND (server_id = 0)))
  BEGIN
    -- The job is local, so start (run) the job locally

    -- Check the step name (if supplied)
    IF (@step_name IS NOT NULL)
    BEGIN
      SELECT @step_id = step_id
      FROM msdb.dbo.sysjobsteps
      WHERE (step_name = @step_name)
        AND (job_id = @job_id)

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

    EXECUTE @retval = msdb.dbo.sp_sqlagent_notify @op_type     = N'J',
                                                  @job_id      = @job_id,
                                                  @schedule_id = @step_id, -- This is the start step
                                                  @action_type = N'S',
                                                  @error_flag  = @error_flag 
    IF ((@retval = 0) AND (@output_flag = 1))
      RAISERROR(14243, 0, 1, @job_name)
  END
  ELSE
  BEGIN
    -- The job is a multi-server job 

    -- Check target server name (if any)
    IF (@server_name IS NOT NULL)
    BEGIN
      IF (NOT EXISTS (SELECT *
                      FROM msdb.dbo.systargetservers
                      WHERE (server_name = @server_name)))
      BEGIN
        RAISERROR(14262, -1, -1, '@server_name', @server_name)
        RETURN(1) -- Failure
      END
    END

    -- Re-post the job if it's an auto-delete job
    IF ((SELECT delete_level
         FROM msdb.dbo.sysjobs
         WHERE (job_id = @job_id)) <> 0)
      EXECUTE @retval = msdb.dbo.sp_post_msx_operation 'INSERT', 'JOB', @job_id, @server_name

    -- Post start instruction(s)
    EXECUTE @retval = msdb.dbo.sp_post_msx_operation 'START', 'JOB', @job_id, @server_name
  END

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

/**************************************************************/
/* SP_STOP_JOB                                                */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_stop_job...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_stop_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_stop_job
go
CREATE PROCEDURE sp_stop_job
  @job_name           sysname          = NULL,
  @job_id             UNIQUEIDENTIFIER = NULL,
  @originating_server NVARCHAR(30)     = NULL, -- So that we can stop ALL jobs that came from the given server
  @server_name        NVARCHAR(30)     = NULL  -- The specific target server to stop the [multi-server] job on
AS
BEGIN
  DECLARE @job_id_as_char VARCHAR(36)
  DECLARE @retval         INT
  DECLARE @num_parameters INT

  SET NOCOUNT ON

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

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

  -- We must have EITHER a job id OR a job name OR an originating server
  SELECT @num_parameters = 0
  IF (@job_id IS NOT NULL)
    SELECT @num_parameters = @num_parameters + 1
  IF (@job_name IS NOT NULL)
    SELECT @num_parameters = @num_parameters + 1
  IF (@originating_server IS NOT NULL)
    SELECT @num_parameters = @num_parameters + 1
  IF (@num_parameters <> 1)
  BEGIN
    RAISERROR(14232, -1, -1)
    RETURN(1) -- Failure
  END

  IF (@originating_server IS NOT NULL)
  BEGIN
    -- Stop (cancel) ALL local jobs that originated from the specified server
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysjobs_view
                    WHERE (originating_server = @originating_server)))
    BEGIN
      RAISERROR(14268, -1, -1, @originating_server)
      RETURN(1) -- Failure
    END

    DECLARE @total_counter   INT
    DECLARE @success_counter INT

    DECLARE stop_jobs CURSOR LOCAL 
    FOR
    SELECT job_id
    FROM msdb.dbo.sysjobs_view
    WHERE (originating_server = @originating_server)

    SELECT @total_counter = 0, @success_counter = 0
    OPEN stop_jobs
    FETCH NEXT FROM stop_jobs INTO @job_id
    WHILE (@@fetch_status = 0)
    BEGIN
      SELECT @total_counter + @total_counter + 1
      EXECUTE @retval = msdb.dbo.sp_sqlagent_notify @op_type     = N'J',
                                                    @job_id      = @job_id,
                                                    @action_type = N'C'
      IF (@retval = 0)
        SELECT @success_counter = @success_counter + 1
      FETCH NEXT FROM stop_jobs INTO @job_id
    END
    RAISERROR(14253, 0, 1, @success_counter, @total_counter)
    DEALLOCATE stop_jobs

    RETURN(0) -- 0 means success
  END
  ELSE
  BEGIN
    -- Stop ONLY the specified job
    EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                                '@job_id',
                                                 @job_name OUTPUT,
                                                 @job_id   OUTPUT
    IF (@retval <> 0)
      RETURN(1) -- Failure

    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysjobservers
                    WHERE (job_id = @job_id)))
    BEGIN
      SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
      RAISERROR(14257, -1, -1, @job_name, @job_id_as_char)
      RETURN(1) -- Failure
    END

    IF (EXISTS (SELECT *
                FROM msdb.dbo.sysjobservers
                WHERE (job_id = @job_id)
                  AND (server_id = 0)))
    BEGIN
      -- The job is local, so stop (cancel) the job locally
      EXECUTE @retval = msdb.dbo.sp_sqlagent_notify @op_type     = N'J',
                                                    @job_id      = @job_id,
                                                    @action_type = N'C'
      IF (@retval = 0)
        RAISERROR(14254, 0, 1, @job_name)
    END
    ELSE
    BEGIN
      -- The job is a multi-server job

      -- Check target server name (if any)
      IF (@server_name IS NOT NULL)
      BEGIN
        IF (NOT EXISTS (SELECT *
                        FROM msdb.dbo.systargetservers
                        WHERE (server_name = @server_name)))
        BEGIN
          RAISERROR(14262, -1, -1, '@server_name', @server_name)
          RETURN(1) -- Failure
        END
      END

      -- Post the stop instruction(s)
      EXECUTE @retval = sp_post_msx_operation 'STOP', 'JOB', @job_id, @server_name
    END

    RETURN(@retval) -- 0 means success
  END

END
go

/**************************************************************/
/* SP_GET_CHUNKED_JOBSTEP_PARAMS                              */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_get_chunked_jobstep_params...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_get_chunked_jobstep_params')
              AND (type = 'P')))
  DROP PROCEDURE sp_get_chunked_jobstep_params
go
CREATE PROCEDURE sp_get_chunked_jobstep_params
  @job_name sysname,
  @step_id  INT = 1
AS
BEGIN
  DECLARE @job_id           UNIQUEIDENTIFIER
  DECLARE @step_id_as_char  VARCHAR(10)
  DECLARE @text_pointer     VARBINARY(16)
  DECLARE @remaining_length INT
  DECLARE @offset           INT
  DECLARE @chunk            INT
  DECLARE @retval           INT

  SET NOCOUNT ON

  -- Check that the job exists
  EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure

  -- Check that the step exists
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysjobsteps
                  WHERE (job_id = @job_id)
                    AND (step_id = @step_id)))
  BEGIN
    SELECT @step_id_as_char = CONVERT(VARCHAR(10), @step_id)
    RAISERROR(14262, -1, -1, '@step_id', @step_id_as_char)
    RETURN(1) -- Failure
  END

  -- Return the sysjobsteps.additional_parameters TEXT column as multiple readtexts of
  -- length 2048

  SELECT @text_pointer = TEXTPTR(additional_parameters),
         @remaining_length = (DATALENGTH(additional_parameters) / 2)
  FROM msdb.dbo.sysjobsteps
  WHERE (job_id = @job_id)
    AND (step_id = @step_id)

  SELECT @offset = 0, @chunk = 100

  -- Get all the chunks of @chunk size
  WHILE (@remaining_length > @chunk)
  BEGIN
    READTEXT msdb.dbo.sysjobsteps.additional_parameters @text_pointer @offset @chunk
    SELECT @offset = @offset + @chunk
    SELECT @remaining_length = @remaining_length - @chunk
  END

  -- Get the last chunk
  IF (@remaining_length > 0)
    READTEXT msdb.dbo.sysjobsteps.additional_parameters @text_pointer @offset @remaining_length

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

/**************************************************************/
/* SP_CHECK_FOR_OWNED_JOBS                                    */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_check_for_owned_jobs...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_check_for_owned_jobs')
              AND (type = 'P')))
  DROP PROCEDURE sp_check_for_owned_jobs
go
CREATE PROCEDURE sp_check_for_owned_jobs
  @login_name sysname,
  @table_name sysname
AS
BEGIN
  SET NOCOUNT ON

  -- This procedure is called by sp_droplogin to check if the login being dropped
  -- still owns jobs.  The return value (the number of jobs owned) is passed back
  -- via the supplied table name [this cumbersome approach is necessary because
  -- sp_check_for_owned_jobs is invoked via an EXEC() and because we always want
  -- sp_droplogin to work, even if msdb and/or sysjobs does not exist].

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sysjobs')
                AND (type = 'U')))
  BEGIN
    SELECT @login_name = REPLACE(@login_name, N'''', N'''''')
    EXECUTE(N'INSERT INTO ' + @table_name + N' SELECT COUNT(*) FROM msdb.dbo.sysjobs_view WHERE (owner_sid = SUSER_SID(N''' + @login_name + '''))')
  END
END
go

/**************************************************************/
/* SP_CHECK_FOR_OWNED_JOBSTEPS                                */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_check_for_owned_jobsteps...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_check_for_owned_jobsteps')
              AND (type = 'P')))
  DROP PROCEDURE sp_check_for_owned_jobsteps
go
CREATE PROCEDURE sp_check_for_owned_jobsteps
  @login_name         sysname = NULL,  -- Supply this OR the database_X parameters, but not both 
  @database_name      sysname = NULL,
  @database_user_name sysname = NULL
AS
BEGIN
  DECLARE @db_name         NVARCHAR(255)
  DECLARE @escaped_db_name NVARCHAR(255)

  SET NOCOUNT ON

  CREATE TABLE #work_table
  (
  database_name      sysname,
  database_user_name sysname
  )

  IF ((@login_name IS NOT NULL) AND (@database_name IS NULL) AND (@database_user_name IS NULL))
  BEGIN
    IF (SUSER_SID(@login_name) IS NULL)
    BEGIN
      RAISERROR(14262, -1, -1, '@login_name', @login_name)
      RETURN(1) -- Failure
    END

    DECLARE all_databases CURSOR LOCAL
    FOR
    SELECT name
    FROM master.dbo.sysdatabases

    OPEN all_databases
    FETCH NEXT FROM all_databases INTO @db_name

    -- Double up any single quotes in @login_name
    SELECT @login_name = REPLACE(@login_name, N'''', N'''''')

    WHILE (@@fetch_status = 0)
    BEGIN
      SELECT @escaped_db_name = QUOTENAME(@db_name, N'[')
      SELECT @db_name = REPLACE(@db_name, '''', '''''')
      EXECUTE(N'INSERT INTO #work_table
                SELECT N''' + @db_name + N''', name
                FROM ' + @escaped_db_name + N'.dbo.sysusers 
                WHERE (sid = SUSER_SID(N''' + @login_name + N'''))')
      FETCH NEXT FROM all_databases INTO @db_name
    END

    DEALLOCATE all_databases

    -- If the login is an NT login, check for steps run as the login directly (as is the case with transient NT logins)
    IF (@login_name LIKE '%\%')
    BEGIN
      INSERT INTO #work_table
      SELECT database_name, database_user_name
      FROM msdb.dbo.sysjobsteps
      WHERE (database_user_name = @login_name)
    END
  END
  
  IF ((@login_name IS NULL) AND (@database_name IS NOT NULL) AND (@database_user_name IS NOT NULL))
  BEGIN
    INSERT INTO #work_table
    SELECT @database_name, @database_user_name
  END  

  IF (EXISTS (SELECT *
              FROM #work_table wt,
                   msdb.dbo.sysjobsteps sjs
              WHERE (wt.database_name = sjs.database_name)
                AND (wt.database_user_name = sjs.database_user_name)))
  BEGIN
    SELECT sjv.job_id,
           sjv.name,
           sjs.step_id,
           sjs.step_name
    FROM #work_table           wt,
         msdb.dbo.sysjobsteps  sjs,
         msdb.dbo.sysjobs_view sjv
    WHERE (wt.database_name = sjs.database_name)
      AND (wt.database_user_name = sjs.database_user_name)
      AND (sjv.job_id = sjs.job_id)
    ORDER BY sjs.job_id
  END

  RETURN(0) -- 0 means success
END
go

/**************************************************************/
/* SP_SQLAGENT_REFRESH_JOB                                    */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_sqlagent_refresh_job...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_sqlagent_refresh_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_sqlagent_refresh_job
go
CREATE PROCEDURE sp_sqlagent_refresh_job
  @job_id      UNIQUEIDENTIFIER = NULL,
  @server_name NVARCHAR(30)     = N'(local)' -- This parameter allows a TSX to use this SP when updating a job
AS
BEGIN
  DECLARE @server_id INT

  SET NOCOUNT ON

  SELECT @server_id = server_id
  FROM msdb.dbo.systargetservers_view
  WHERE (server_name = ISNULL(@server_name, N'(local)'))

  SELECT @server_id = ISNULL(@server_id, 0)

  SELECT sjv.job_id,
         sjv.name,
         sjv.enabled,
         sjv.start_step_id,
         owner = SUSER_SNAME(sjv.owner_sid),
         sjv.notify_level_eventlog,
         sjv.notify_level_email,
         sjv.notify_level_netsend,
         sjv.notify_level_page,
         sjv.notify_email_operator_id,
         sjv.notify_netsend_operator_id,
         sjv.notify_page_operator_id,
         sjv.delete_level,
         has_step = (SELECT COUNT(*) 
                     FROM msdb.dbo.sysjobsteps sjst
                     WHERE (sjst.job_id = sjv.job_id)),
         sjv.version_number,
         last_run_date = ISNULL(sjs.last_run_date, 0),
         last_run_time = ISNULL(sjs.last_run_time, 0),
         sjv.originating_server,
         sjv.description
  FROM msdb.dbo.sysjobs_view  sjv,
       msdb.dbo.sysjobservers sjs
  WHERE ((@job_id IS NULL) OR (@job_id = sjv.job_id))
    AND (sjv.job_id = sjs.job_id)
    AND (sjs.server_id = @server_id)
  ORDER BY sjv.job_id

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

/**************************************************************/
/* SP_JOBHISTORY_ROW_LIMITER                                  */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_jobhistory_row_limiter...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_jobhistory_row_limiter')
              AND (type = 'P')))
  DROP PROCEDURE dbo.sp_jobhistory_row_limiter
go
CREATE PROCEDURE sp_jobhistory_row_limiter 
  @job_id UNIQUEIDENTIFIER
AS
BEGIN
  DECLARE @max_total_rows         INT -- This value comes from the registry (MaxJobHistoryTableRows)
  DECLARE @max_rows_per_job       INT -- This value comes from the registry (MaxJobHistoryRows)
  DECLARE @rows_to_delete         INT
  DECLARE @rows_to_delete_as_char VARCHAR(10)
  DECLARE @current_rows           INT
  DECLARE @current_rows_per_job   INT
  DECLARE @job_id_as_char         VARCHAR(36)

  SET NOCOUNT ON

  SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)

  -- Get max-job-history-rows from the registry
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'JobHistoryMaxRows',
                                 @max_total_rows OUTPUT,
                                N'no_output'

  -- Check if we are limiting sysjobhistory rows
  IF (ISNULL(@max_total_rows, -1) = -1)
    RETURN(0)

  -- Check that max_total_rows is more than 1
  IF (ISNULL(@max_total_rows, 0) < 2)
  BEGIN
    -- It isn't, so set the default to 1000 rows
    SELECT @max_total_rows = 1000
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'JobHistoryMaxRows',
                                   N'REG_DWORD',
                                    @max_total_rows
  END

  -- Get the per-job maximum number of rows to keep
  SELECT @max_rows_per_job = 0
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'JobHistoryMaxRowsPerJob',
                                 @max_rows_per_job OUTPUT,
                                N'no_output'

  -- Check that max_rows_per_job is <= max_total_rows
  IF ((@max_rows_per_job > @max_total_rows) OR (@max_rows_per_job < 1))
  BEGIN
    -- It isn't, so default the rows_per_job to max_total_rows
    SELECT @max_rows_per_job = @max_total_rows
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'JobHistoryMaxRowsPerJob',
                                   N'REG_DWORD',
                                    @max_rows_per_job
  END

  BEGIN TRANSACTION

  SELECT @current_rows_per_job = COUNT(*)
  FROM msdb.dbo.sysjobhistory (TABLOCKX)
  WHERE (job_id = @job_id)

  -- Delete the oldest history row(s) for the job being inserted if the new row has
  -- pushed us over the per-job row limit (MaxJobHistoryRows)
  SELECT @rows_to_delete = @current_rows_per_job - @max_rows_per_job
  SELECT @rows_to_delete_as_char = CONVERT(VARCHAR, @rows_to_delete)

  IF (@rows_to_delete > 0)
  BEGIN
    EXECUTE ('DECLARE @new_oldest_id INT
              SET NOCOUNT ON
              SET ROWCOUNT ' + @rows_to_delete_as_char +
             'SELECT @new_oldest_id = instance_id
              FROM msdb.dbo.sysjobhistory
              WHERE (job_id = ''' + @job_id_as_char + ''') ' +
             'ORDER BY instance_id
              SET ROWCOUNT 0
              DELETE FROM msdb.dbo.sysjobhistory
              WHERE (job_id = ''' + @job_id_as_char + ''')' +
             '  AND (instance_id <= @new_oldest_id)')
  END

  -- Delete the oldest history row(s) if inserting the new row has pushed us over the
  -- global MaxJobHistoryTableRows limit.
  SELECT @current_rows = COUNT(*)
  FROM msdb.dbo.sysjobhistory

  SELECT @rows_to_delete = @current_rows - @max_total_rows
  SELECT @rows_to_delete_as_char = CONVERT(VARCHAR, @rows_to_delete)

  IF (@rows_to_delete > 0)
  BEGIN
    EXECUTE ('DECLARE @new_oldest_id INT
              SET NOCOUNT ON
              SET ROWCOUNT ' + @rows_to_delete_as_char +
             'SELECT @new_oldest_id = instance_id
              FROM msdb.dbo.sysjobhistory
              ORDER BY instance_id
              SET ROWCOUNT 0
              DELETE FROM msdb.dbo.sysjobhistory
              WHERE (instance_id <= @new_oldest_id)')
  END

  IF (@@trancount > 0)
    COMMIT TRANSACTION

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_SQLAGENT_LOG_JOBHISTORY                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_sqlagent_log_jobhistory...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_sqlagent_log_jobhistory')
              AND (type = 'P')))
  DROP PROCEDURE sp_sqlagent_log_jobhistory
go
CREATE PROCEDURE sp_sqlagent_log_jobhistory
  @job_id               UNIQUEIDENTIFIER,
  @step_id              INT,
  @sql_message_id       INT = 0,
  @sql_severity         INT = 0,
  @message              NVARCHAR(1024) = NULL,
  @run_status           INT, -- SQLAGENT_EXEC_X code
  @run_date             INT,
  @run_time             INT,
  @run_duration         INT,
  @operator_id_emailed  INT = 0,
  @operator_id_netsent  INT = 0,
  @operator_id_paged    INT = 0,
  @retries_attempted    INT,
  @server               NVARCHAR(30) = N'(local)'
AS
BEGIN
  DECLARE @retval              INT
  DECLARE @job_id_as_char      VARCHAR(36)
  DECLARE @step_id_as_char     VARCHAR(10)
  DECLARE @operator_id_as_char VARCHAR(10)
  DECLARE @step_name           sysname
  DECLARE @error_severity      INT
  DECLARE @job_name            sysname

  SET NOCOUNT ON

  -- Check authority (only SQLServerAgent can add a history entry for a job)
  EXECUTE @retval = sp_verify_jobproc_caller @job_id = @job_id, @program_name = N'SQLAgent%'
  IF (@retval <> 0)
    RETURN(@retval)

  -- NOTE: We raise all errors as informational (sev 0) to prevent SQLServerAgent from caching
  --       the operation (if it fails) since if the operation will never run successfully we 
  --       don't want it to hang around in the operation cache.
  SELECT @error_severity = 0

  -- Check job_id
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysjobs_view
                  WHERE (job_id = @job_id)))
  BEGIN
    SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
    RAISERROR(14262, @error_severity, -1, 'Job', @job_id_as_char) 
    RETURN(1) -- Failure
  END

  -- Check step id
  IF (@step_id <> 0) -- 0 means 'for the whole job'
  BEGIN
    SELECT @step_name = step_name
    FROM msdb.dbo.sysjobsteps
    WHERE (job_id = @job_id)
      AND (step_id = @step_id)
    IF (@step_name IS NULL)
    BEGIN
      SELECT @step_id_as_char = CONVERT(VARCHAR, @step_id)
      RAISERROR(14262, @error_severity, -1, '@step_id', @step_id_as_char)
      RETURN(1) -- Failure
    END
  END
  ELSE
    SELECT @step_name = FORMATMESSAGE(14570)

  -- Check run_status
  IF (@run_status NOT IN (0, 1, 2, 3, 4, 5)) -- SQLAGENT_EXEC_X code
  BEGIN
    RAISERROR(14266, @error_severity, -1, '@run_status', '0, 1, 2, 3, 4, 5')
    RETURN(1) -- Failure
  END

  -- Check run_date
  EXECUTE @retval = sp_verify_job_date @run_date, '@run_date', 10
  IF (@retval <> 0)
    RETURN(1) -- Failure

  -- Check run_time
  EXECUTE @retval = sp_verify_job_time @run_time, '@run_time', 10
  IF (@retval <> 0)
    RETURN(1) -- Failure

  -- Check operator_id_emailed
  IF (@operator_id_emailed <> 0)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysoperators
                    WHERE (id = @operator_id_emailed)))
    BEGIN
      SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_emailed)
      RAISERROR(14262, @error_severity, -1, '@operator_id_emailed', @operator_id_as_char)
      RETURN(1) -- Failure
    END
  END

  -- Check operator_id_netsent
  IF (@operator_id_netsent <> 0)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysoperators
                    WHERE (id = @operator_id_netsent)))
    BEGIN
      SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_netsent)
      RAISERROR(14262, @error_severity, -1, '@operator_id_netsent', @operator_id_as_char)
      RETURN(1) -- Failure
    END
  END

  -- Check operator_id_paged
  IF (@operator_id_paged <> 0)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysoperators
                    WHERE (id = @operator_id_paged)))
    BEGIN
      SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_paged)
      RAISERROR(14262, @error_severity, -1, '@operator_id_paged', @operator_id_as_char)
      RETURN(1) -- Failure
    END
  END

  -- Insert the history row
  INSERT INTO msdb.dbo.sysjobhistory
         (job_id,
          step_id,
          step_name,
          sql_message_id,
          sql_severity,
          message,
          run_status,
          run_date,
          run_time,
          run_duration,
          operator_id_emailed,
          operator_id_netsent,
          operator_id_paged,
          retries_attempted,
          server)
  VALUES (@job_id,
          @step_id,
          @step_name,
          @sql_message_id,
          @sql_severity,
          @message,
          @run_status,
          @run_date,
          @run_time,
          @run_duration,
          @operator_id_emailed,
          @operator_id_netsent,
          @operator_id_paged,
          @retries_attempted,
          @server)

  -- If misc. replication job, then update global replication status table
  SELECT @job_name = name from msdb.dbo.sysjobs where job_id = @job_id and
    category_id IN (11, 12, 16, 17, 18)
  IF @job_name IS NOT NULL
  BEGIN
    -- Nothing can be done if this fails, so don't worry about the return code
    EXECUTE master.dbo.sp_MSupdate_replication_status
      @publisher = '',
      @publisher_db = '',
      @publication = '',
      @publication_type = -1,
      @agent_type = 5,
      @agent_name = @job_name,
      @status = @run_status
  END

  -- Delete any history rows that are over the registry-defined limits
  EXECUTE msdb.dbo.sp_jobhistory_row_limiter @job_id

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

/**************************************************************/
/* SP_SQLAGENT_CHECK_MSX_VERSION                              */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_sqlagent_check_msx_version...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_sqlagent_check_msx_version')
              AND (type = 'P')))
  DROP PROCEDURE sp_sqlagent_check_msx_version
go
CREATE PROCEDURE sp_sqlagent_check_msx_version
  @required_microsoft_version INT = NULL
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @msx_version          NVARCHAR(16)
  DECLARE @required_msx_version NVARCHAR(16)

  IF (@required_microsoft_version IS NULL)
    SELECT @required_microsoft_version = 0x07000252 -- 7.0.594

  IF (@@microsoftversion < @required_microsoft_version)
  BEGIN
    SELECT @msx_version = CONVERT(NVARCHAR(2), CONVERT(INT, CONVERT(BINARY(1), (CONVERT(BINARY(2), @@microsoftversion / 0xffff) / 256)))) + N'.' +
                          CONVERT(NVARCHAR(2), CONVERT(INT, CONVERT(BINARY(1), (CONVERT(BINARY(2), @@microsoftversion / 0xffff) % 256)))) + N'.' +
                          CONVERT(NVARCHAR(4), @@microsoftversion & 0xffff)
    SELECT @required_msx_version = CONVERT(NVARCHAR(2), CONVERT(INT, CONVERT(BINARY(1), (CONVERT(BINARY(2), @required_microsoft_version / 0xffff) / 256)))) + N'.' +
                                   CONVERT(NVARCHAR(2), CONVERT(INT, CONVERT(BINARY(1), (CONVERT(BINARY(2), @required_microsoft_version / 0xffff) % 256)))) + N'.' +
                                   CONVERT(NVARCHAR(4), @required_microsoft_version & 0xffff)
    RAISERROR(14541, -1, -1, @msx_version, @required_msx_version)
    RETURN(1) -- Failure
  END
  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_SQLAGENT_PROBE_MSX                                      */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_sqlagent_probe_msx...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_sqlagent_probe_msx')
              AND (type = 'P')))
  DROP PROCEDURE sp_sqlagent_probe_msx
go
CREATE PROCEDURE sp_sqlagent_probe_msx
  @server_name          NVARCHAR(30),  -- The name of the target server probing the MSX
  @local_time           NVARCHAR(100), -- The local time at the target server in the format YYYY/MM/DD HH:MM:SS
  @poll_interval        INT,           -- The frequency (in seconds) with which the target polls the MSX
  @time_zone_adjustment INT = NULL     -- The offset from GMT in minutes (may be NULL if unknown)
AS
BEGIN
  DECLARE @bad_enlistment        BIT
  DECLARE @blocking_instructions INT
  DECLARE @pending_instructions  INT

  SET NOCOUNT ON

  SELECT @bad_enlistment = 0, @blocking_instructions = 0, @pending_instructions = 0

  UPDATE msdb.dbo.systargetservers 
  SET last_poll_date = GETDATE(), 
      local_time_at_last_poll = CONVERT(DATETIME, @local_time, 111),
      poll_interval = @poll_interval,
      time_zone_adjustment = ISNULL(@time_zone_adjustment, time_zone_adjustment)
  WHERE (server_name = @server_name) 

  -- If the systargetservers entry is missing (and no DEFECT instruction has been posted)
  -- then the enlistment is bad
  IF (NOT EXISTS (SELECT 1 
                  FROM msdb.dbo.systargetservers 
                  WHERE (server_name = @server_name))) AND
     (NOT EXISTS (SELECT 1 
                  FROM msdb.dbo.sysdownloadlist 
                  WHERE (target_server = @server_name) 
                    AND (operation_code = 7) 
                    AND (object_type = 2)))
    SELECT @bad_enlistment = 1

  SELECT @blocking_instructions = COUNT(*) 
  FROM msdb.dbo.sysdownloadlist 
  WHERE (target_server = @server_name) 
    AND (error_message IS NOT NULL)

  SELECT @pending_instructions = COUNT(*) 
  FROM msdb.dbo.sysdownloadlist 
  WHERE (target_server = @server_name)
    AND (error_message IS NULL)
    AND (status = 0)

  SELECT @bad_enlistment, @blocking_instructions, @pending_instructions
END
go

/**************************************************************/
/* SP_SET_LOCAL_TIME                                          */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_set_local_time...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_set_local_time')
              AND (type = 'P')))
  DROP PROCEDURE sp_set_local_time
go
CREATE PROCEDURE sp_set_local_time
  @server_name           NVARCHAR(30) = NULL,
  @adjustment_in_minutes INT          = 0 -- Only needed for Win9x
AS
BEGIN
  DECLARE @ret              INT
  DECLARE @local_time       INT
  DECLARE @local_date       INT
  DECLARE @current_datetime DATETIME
  DECLARE @local_time_sz    VARCHAR(30)
  DECLARE @cmd              NVARCHAR(200)
  DECLARE @date_format      NVARCHAR(64)
  DECLARE @year_sz          NVARCHAR(16)
  DECLARE @month_sz         NVARCHAR(16)
  DECLARE @day_sz           NVARCHAR(16)

  -- Synchronize the clock with the remote server (if supplied)
  -- NOTE: NT takes timezones into account, whereas Win9x does not
  IF (@server_name IS NOT NULL)
  BEGIN
    SELECT @cmd = N'net time \\' + @server_name + N' /set /y'
    EXECUTE @ret = master.dbo.xp_cmdshell @cmd, no_output
    IF (@ret <> 0)
      RETURN(1) -- Failure
  END

  -- Since NET TIME on Win9x does not take time zones into account we need to manually adjust
  -- for this using @adjustment_in_minutes which will be the difference between the MSX GMT
  -- offset and the target server GMT offset
  IF ((PLATFORM() & 0x2) = 0x2) -- Win9x
  BEGIN
    -- Get the date format from the registry (so that we can construct our DATE command-line command)
    EXECUTE master.dbo.xp_regread N'HKEY_CURRENT_USER',
                                  N'Control Panel\International',
                                  N'sShortDate',
                                   @date_format OUTPUT,
                                  N'no_output'
    SELECT @date_format = LOWER(@date_format)

    IF (@adjustment_in_minutes <> 0)
    BEGIN
      -- Wait for SQLServer to re-cache the OS time
      WAITFOR DELAY '00:01:00'

      SELECT @current_datetime = DATEADD(mi, @adjustment_in_minutes, GETDATE())
      SELECT @local_time_sz = SUBSTRING(CONVERT(VARCHAR, @current_datetime, 8), 1, 5)
      SELECT @local_time = CONVERT(INT, LTRIM(SUBSTRING(@local_time_sz, 1, PATINDEX('%:%', @local_time_sz) - 1)  + SUBSTRING(@local_time_sz, PATINDEX('%:%', @local_time_sz) + 1, 2)))
      SELECT @local_date = CONVERT(INT, CONVERT(VARCHAR, @current_datetime, 112))

      -- Set the date
      SELECT @year_sz = CONVERT(NVARCHAR, @local_date / 10000)
      SELECT @month_sz = CONVERT(NVARCHAR, (@local_date % 10000) / 100)
      SELECT @day_sz = CONVERT(NVARCHAR, @local_date % 100)

      IF (@date_format LIKE N'y%m%d')
        SELECT @cmd = N'DATE ' + @year_sz + N'-' + @month_sz + N'-' + @day_sz
      IF (@date_format LIKE N'y%d%m')
        SELECT @cmd = N'DATE ' + @year_sz + N'-' + @day_sz + N'-' + @month_sz
      IF (@date_format LIKE N'm%d%y')
        SELECT @cmd = N'DATE ' + @month_sz + N'-' + @day_sz + N'-' + @year_sz
      IF (@date_format LIKE N'd%m%y')
        SELECT @cmd = N'DATE ' + @day_sz + N'-' + @month_sz + N'-' + @year_sz

      EXECUTE @ret = master.dbo.xp_cmdshell @cmd, no_output
      IF (@ret <> 0)
        RETURN 1 -- Failure

      -- Set the time (NOTE: We can't set the millisecond part of the time, so we may be up to .999 sec off)
      SELECT @cmd = N'TIME ' + CONVERT(NVARCHAR, @local_time / 100) + N':' + CONVERT(NVARCHAR, @local_time % 100) + ':' + CONVERT(NVARCHAR(2), DATEPART(SS, GETDATE()))
      EXECUTE @ret = master.dbo.xp_cmdshell @cmd, no_output
      IF (@ret <> 0)
        RETURN 1 -- Failure
    END

  END

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_MULTI_SERVER_JOB_SUMMARY [used by SEM only]             */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_multi_server_job_summary...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_multi_server_job_summary')
              AND (type = 'P')))
  DROP PROCEDURE sp_multi_server_job_summary
go
CREATE PROCEDURE sp_multi_server_job_summary
  @job_id   UNIQUEIDENTIFIER = NULL,
  @job_name sysname          = NULL
AS
BEGIN
  DECLARE @retval INT

  SET NOCOUNT ON

  IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL))
  BEGIN
    EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                                '@job_id',
                                                 @job_name OUTPUT,
                                                 @job_id   OUTPUT
    IF (@retval <> 0)
      RETURN(1) -- Failure
  END

  -- NOTE: We join with syscategories - not sysjobservers - since we want to include jobs
  --       which are of type multi-server but which don't currently have any servers
  SELECT 'job_id'   = sj.job_id,
         'job_name' = sj.name, 
         'enabled'  = sj.enabled,
         'category_name'  = sc.name,
         'target_servers' = (SELECT COUNT(*) 
                             FROM msdb.dbo.sysjobservers sjs
                             WHERE (sjs.job_id = sj.job_id)),
         'pending_download_instructions' = (SELECT COUNT(*) 
                                            FROM msdb.dbo.sysdownloadlist sdl 
                                            WHERE (sdl.object_id = sj.job_id)
                                              AND (status = 0)),
         'download_errors' = (SELECT COUNT(*) 
                              FROM msdb.dbo.sysdownloadlist sdl 
                              WHERE (sdl.object_id = sj.job_id)
                                AND (sdl.error_message IS NOT NULL)),
         'execution_failures' = (SELECT COUNT(*) 
                                 FROM msdb.dbo.sysjobservers sjs 
                                 WHERE (sjs.job_id = sj.job_id)
                                   AND (sjs.last_run_date <> 0)
                                   AND (sjs.last_run_outcome <> 1)) -- 1 is success
  FROM msdb.dbo.sysjobs sj,
       msdb.dbo.syscategories sc
  WHERE (sj.category_id = sc.category_id)
    AND (sc.category_class = 1) -- JOB
    AND (sc.category_type  = 2) -- Multi-Server
    AND ((@job_id IS NULL)   OR (sj.job_id = @job_id))
    AND ((@job_name IS NULL) OR (sj.name = @job_name))

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_TARGET_SERVER_SUMMARY [used by SEM only]                */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_target_server_summary...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_target_server_summary')
              AND (type = 'P')))
  DROP PROCEDURE sp_target_server_summary
go
CREATE PROCEDURE sp_target_server_summary
  @target_server NVARCHAR(30) = NULL
AS
BEGIN
  SET NOCOUNT ON

  SELECT server_id,
         server_name,
        'local_time' = DATEADD(SS, DATEDIFF(SS, last_poll_date, GETDATE()), local_time_at_last_poll),
         last_poll_date,
        'unread_instructions' = (SELECT COUNT(*)
                                 FROM msdb.dbo.sysdownloadlist sdl
                                 WHERE (sdl.target_server = sts.server_name)
                                   AND (sdl.status = 0)),
        'blocked' = (SELECT COUNT(*)
                     FROM msdb.dbo.sysdownloadlist sdl
                     WHERE (sdl.target_server = sts.server_name)
                       AND (sdl.error_message IS NOT NULL)),
         poll_interval
  FROM msdb.dbo.systargetservers sts
  WHERE ((@target_server IS NULL) OR (@target_server = sts.server_name))
END
go

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go


/**************************************************************/
/*                                                            */
/*         6  .  X     P  R  O  C  E  D  U  R  E  S           */
/*                                                            */
/* These procedures are provided for backwards compatability  */
/* with 6.x scripts and 6.x replication.  The re-implemented  */
/* procedures are as follows:                                 */
/*                                                            */
/* - sp_uniquetaskname  (SQLDMO)                              */
/* - systasks_view      (INSTDIST.SQL)                        */
/* - sp_addtask         (INSTREPL.SQL, INSTDIST.SQL, SQLDMO)  */
/* - sp_updatetask      (INSTDIST.SQL)                        */
/* - sp_droptask        (INSTREPL.SQL, INSTDIST.SQL, SQLDMO)  */
/* - sp_helptask        (INSTREPL.SQL, SQLDMO)                */
/* - sp_verifytaskid    (INSTREPL.SQL)                        */
/* - sp_reassigntask    (INSTDIST.SQL)                        */
/* - sp_helphistory     (For completeness only)               */
/* - sp_purgehistory    (For completeness only)               */
/* - systasks           (For completeness only)               */
/**************************************************************/

/**************************************************************/
/* SYSTASKS (a view to simulate the 6.x systasks table)       */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] table systasks [as a view]...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'systasks')
              AND (type = 'U')))
  DROP TABLE systasks -- Just a precaution in case the systasks table is somehow still lingering around
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'systasks')
              AND (type = 'V')))
  DROP VIEW systasks
go
CREATE VIEW systasks
AS
  SELECT 'id'                     = sti.task_id,
         'name'                   = sj.name,
         'subsystem'              = sjst.subsystem,
         'server'                 = sjst.server,
         'username'               = sjst.database_user_name,
         'ownerloginid'           = ISNULL(sl.suid, 1), -- Default to SA if owner not known
         'databasename'           = sjst.database_name,
         'enabled'                = sj.enabled,
         'freqtype'               = ISNULL(sjsch.freq_type, 2), -- On Demand
         'freqinterval'           = ISNULL(sjsch.freq_interval, 0),
         'freqsubtype'            = ISNULL(sjsch.freq_subday_type, 0),
         'freqsubinterval'        = ISNULL(sjsch.freq_subday_interval, 0),
         'freqrelativeinterval'   = ISNULL(sjsch.freq_relative_interval, 0),
         'freqrecurrencefactor'   = ISNULL(sjsch.freq_recurrence_factor, 0),
         'activestartdate'        = ISNULL(sjsch.active_start_date, 19900101),
         'activeenddate'          = ISNULL(sjsch.active_end_date, 99991231),
         'activestarttimeofday'   = ISNULL(sjsch.active_start_time, 0),
         'activeendtimeofday'     = ISNULL(sjsch.active_end_time, 235959),
         'lastrundate'            = sjs.last_run_date,
         'lastruntime'            = sjs.last_run_time,
         'nextrundate'            = ISNULL(sjsch.next_run_date, 0),
         'nextruntime'            = ISNULL(sjsch.next_run_time, 0),
         'runpriority'            = sjst.os_run_priority,
         'emailoperatorid'        = sj.notify_email_operator_id,
         'retryattempts'          = sjst.retry_attempts,
         'retrydelay'             = sjst.retry_interval,
         'datecreated'            = sj.date_created,
         'datemodified'           = sj.date_modified,
         'command'                = sjst.command,
         'lastruncompletionlevel' = sjs.last_run_outcome,
         'lastrunduration'        = sjst.last_run_duration,
         'lastrunretries'         = sjst.last_run_retries,
         'loghistcompletionlevel' = sj.notify_level_eventlog,
         'emailcompletionlevel'   = sj.notify_level_email,
         'description'            = sj.description,
         'tagadditionalinfo'      = 0,
         'tagobjectid'            = 0,
         'tagobjecttype'          = 0,
         'parameters'             = CONVERT(TEXT, sjst.additional_parameters),
         'cmdexecsuccesscode'     = sjst.cmdexec_success_code
    FROM msdb.dbo.sysjobs                         sj
         LEFT OUTER JOIN msdb.dbo.sysjobschedules sjsch ON (sj.job_id = sjsch.job_id)
         LEFT OUTER JOIN master.dbo.syslogins     sl    ON (sj.owner_sid = sl.sid),
         msdb.dbo.systaskids                      sti,
         msdb.dbo.sysjobsteps                     sjst,
         msdb.dbo.sysjobservers                   sjs
    WHERE (sj.job_id = sti.job_id)
      AND (sj.job_id = sjst.job_id)
      AND (sjst.step_id = 1)
      AND (sj.job_id = sjs.job_id)
      AND (sjs.server_id = 0)
      AND ((sjsch.name = N'6.x schedule') OR (sjsch.name IS NULL)) -- NULL handles the case of the job not having a schedule
  UNION ALL -- NOTE: We do this just to make the view non-updatable
  SELECT 0, '', '', '', '', 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, GETDATE(), GETDATE(), '', 0, 0, 0, 0, 0, '', 0, 0, 0, '', 0
  WHERE (1 = 2)
go

/**************************************************************/
/* SYSTASKS_VIEW                                              */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] view systasks_view...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'systasks_view')
              AND (type = 'V')))
  DROP VIEW systasks_view
go
CREATE VIEW systasks_view
AS
  SELECT 'id'                     = sti.task_id,
         'name'                   = sjv.name,
         'subsystem'              = sjst.subsystem,
         'server'                 = sjst.server,
         'username'               = sjst.database_user_name,
         'ownerloginid'           = ISNULL(sl.suid, 1), -- Default to SA if owner not known
         'databasename'           = sjst.database_name,
         'enabled'                = sjv.enabled,
         'freqtype'               = ISNULL(sjsch.freq_type, 2), -- On Demand
         'freqinterval'           = ISNULL(sjsch.freq_interval, 0),
         'freqsubtype'            = ISNULL(sjsch.freq_subday_type, 0),
         'freqsubinterval'        = ISNULL(sjsch.freq_subday_interval, 0),
         'freqrelativeinterval'   = ISNULL(sjsch.freq_relative_interval, 0),
         'freqrecurrencefactor'   = ISNULL(sjsch.freq_recurrence_factor, 0),
         'activestartdate'        = ISNULL(sjsch.active_start_date, 19900101),
         'activeenddate'          = ISNULL(sjsch.active_end_date, 99991231),
         'activestarttimeofday'   = ISNULL(sjsch.active_start_time, 0),
         'activeendtimeofday'     = ISNULL(sjsch.active_end_time, 235959),
         'lastrundate'            = sjs.last_run_date,
         'lastruntime'            = sjs.last_run_time,
         'nextrundate'            = ISNULL(sjsch.next_run_date, 0),
         'nextruntime'            = ISNULL(sjsch.next_run_time, 0),
         'runpriority'            = sjst.os_run_priority,
         'emailoperatorid'        = sjv.notify_email_operator_id,
         'retryattempts'          = sjst.retry_attempts,
         'retrydelay'             = sjst.retry_interval,
         'datecreated'            = sjv.date_created,
         'datemodified'           = sjv.date_modified,
         'command'                = sjst.command,
         'lastruncompletionlevel' = sjs.last_run_outcome,
         'lastrunduration'        = sjst.last_run_duration,
         'lastrunretries'         = sjst.last_run_retries,
         'loghistcompletionlevel' = sjv.notify_level_eventlog,
         'emailcompletionlevel'   = sjv.notify_level_email,
         'description'            = sjv.description,
         'tagadditionalinfo'      = 0,
         'tagobjectid'            = 0,
         'tagobjecttype'          = 0,
         'parameters'             = CONVERT(TEXT, sjst.additional_parameters),
         'cmdexecsuccesscode'     = sjst.cmdexec_success_code
    FROM msdb.dbo.sysjobs_view                    sjv
         LEFT OUTER JOIN msdb.dbo.sysjobschedules sjsch ON (sjv.job_id = sjsch.job_id)
         LEFT OUTER JOIN master.dbo.syslogins     sl    ON (sjv.owner_sid = sl.sid),
         msdb.dbo.systaskids                      sti,
         msdb.dbo.sysjobsteps                     sjst,
         msdb.dbo.sysjobservers                   sjs
    WHERE (sjv.job_id = sti.job_id)
      AND (sjv.job_id = sjst.job_id)
      AND (sjst.step_id = 1)
      AND (sjv.job_id = sjs.job_id)
      AND (sjs.server_id = 0)
      AND ((sjsch.name = N'6.x schedule') OR (sjsch.name IS NULL)) -- NULL handles the case of the job not having a schedule
  UNION ALL -- NOTE: We do this just to make the view non-updatable
  SELECT 0, '', '', '', '', 0, '', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, GETDATE(), GETDATE(), '', 0, 0, 0, 0, 0, '', 0, 0, 0, '', 0
  WHERE (1 = 2)
go

/**************************************************************/
/* SP_UNIQUETASKNAME                                          */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure sp_uniquetaskname...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_uniquetaskname')
              AND (type = 'P')))
  DROP PROCEDURE sp_uniquetaskname
go
CREATE PROCEDURE sp_uniquetaskname
  @seed NVARCHAR(92)
AS
BEGIN
  DECLARE @newest_suffix INT

  SET NOCOUNT ON

  -- We're going to add a suffix of 8 characters so make sure the seed is at most 84 characters
  SELECT @seed = LTRIM(RTRIM(@seed))
  IF (DATALENGTH(@seed) > 0)
    SELECT @seed = SUBSTRING(@seed, 1, 84)

  -- Find the newest (highest) suffix so far
  SELECT @newest_suffix = MAX(CONVERT(INT, RIGHT(name, 8)))
  FROM msdb.dbo.sysjobs -- DON'T use sysjobs_view here!
  WHERE (name LIKE N'%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

  -- Generate the task name by appending the 'newest suffix' value (plus one) to the seed
  IF (@newest_suffix IS NOT NULL)
  BEGIN
    SELECT @newest_suffix = @newest_suffix + 1
    SELECT 'TaskName' = CONVERT(NVARCHAR(92), @seed + REPLICATE(N'0', 8 - (DATALENGTH(CONVERT(NVARCHAR, @newest_suffix)) / 2)) + CONVERT(NVARCHAR, @newest_suffix))
  END
  ELSE
    SELECT 'TaskName' = CONVERT(NVARCHAR(92), @seed + N'00000001')
END
go

/**************************************************************/
/* SP_ADDTASK                                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure sp_addtask...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_addtask')
              AND (type = 'P')))
  DROP PROCEDURE sp_addtask
go
CREATE PROCEDURE sp_addtask
  @name                   sysname,               -- Was VARCHAR(100) in 6.x
  @subsystem              NVARCHAR(40)   = N'TSQL', -- Was VARCHAR(30) in 6.x
  @server                 NVARCHAR(30)   = NULL,
  @username               sysname        = NULL, -- Was VARCHAR(30) in 6.x
  @databasename           sysname        = NULL, -- Was VARCHAR(30) in 6.x
  @enabled                TINYINT        = 0,
  @freqtype               INT            = 2,    -- 2 means OnDemand
  @freqinterval           INT            = 1,
  @freqsubtype            INT            = 1,
  @freqsubinterval        INT            = 1,
  @freqrelativeinterval   INT            = 1,
  @freqrecurrencefactor   INT            = 1,
  @activestartdate        INT            = 0,
  @activeenddate          INT            = 0,
  @activestarttimeofday   INT            = 0,
  @activeendtimeofday     INT            = 0,
  @nextrundate            INT            = 0,
  @nextruntime            INT            = 0,
  @runpriority            INT            = 0,
  @emailoperatorname      sysname        = NULL, -- Was VARCHAR(50) in 6.x
  @retryattempts          INT            = 0,
  @retrydelay             INT            = 10,
  @command                NVARCHAR(3200) = NULL, -- Was VARCHAR(255) in 6.x
  @loghistcompletionlevel INT            = 2,
  @emailcompletionlevel   INT            = 0,
  @description            NVARCHAR(512)  = NULL, -- Was VARCHAR(255) in 6.x
  @tagadditionalinfo      VARCHAR(96)    = NULL, -- Obsolete in 7.0
  @tagobjectid            INT            = NULL, -- Obsolete in 7.0
  @tagobjecttype          INT            = NULL, -- Obsolete in 7.0
  @newid                  INT            = NULL OUTPUT,
  @parameters             NTEXT          = NULL, -- Was TEXT in 6.x
  @cmdexecsuccesscode     INT            = 0,
  @category_name          sysname        = NULL, -- New for 7.0
  @category_id            INT            = NULL  -- New for 7.0
AS
BEGIN
  DECLARE @retval INT
  DECLARE @job_id UNIQUEIDENTIFIER
  DECLARE @id     INT
  DECLARE @distdb sysname
  DECLARE @proc nvarchar(255) 

  SET NOCOUNT ON

  SELECT @retval = 1 -- 0 means success, 1 means failure

  -- Set 7.0 category names for 6.5 replication tasks
  IF (LOWER(@subsystem) = N'sync')
    SELECT @category_id = 15
  ELSE IF (LOWER(@subsystem) = N'logreader')
    SELECT @category_id = 13
  ELSE IF (LOWER(@subsystem) = N'distribution')
    SELECT @category_id = 10

  -- Convert old replication synchronization subsystem name to the 7.0 name
  IF (LOWER(@subsystem) = N'sync')
    SELECT @subsystem = N'Snapshot'

  -- If a category ID is provided this overrides any supplied category name
  IF (@category_id IS NOT NULL)
  BEGIN
    SELECT @category_name = name
    FROM msdb.dbo.syscategories 
    WHERE (category_id = @category_id)
    SELECT @category_name = ISNULL(@category_name, FORMATMESSAGE(14205))
  END

  -- In 6.x active start date was not restricted, but it is in 7.0; so to avoid a "noisey"
  -- failure in sp_add_jobschedule we modify the value accordingly
  IF ((@activestartdate <> 0) AND (@activestartdate < 19900101))
    SELECT @activestartdate = 19900101

  BEGIN TRANSACTION

    -- Add the job
    EXECUTE @retval = sp_add_job
      @job_name                   = @name,
      @enabled                    = @enabled,
      @start_step_id              = 1,
      @description                = @description,
      @category_name              = @category_name,
      @notify_level_eventlog      = @loghistcompletionlevel,
      @notify_level_email         = @emailcompletionlevel,
      @notify_email_operator_name = @emailoperatorname,
      @job_id                     = @job_id OUTPUT

    IF (@retval <> 0)
    BEGIN
      ROLLBACK TRANSACTION
      GOTO Quit
    END

    -- Add an entry to systaskids for the new job (created by a 6.x client)
    INSERT INTO msdb.dbo.systaskids (job_id) VALUES (@job_id)

    -- Get the assigned task id
    SELECT @id = task_id, @newid = task_id
    FROM msdb.dbo.systaskids
    WHERE (job_id = @job_id)

    -- Add the job step
    EXECUTE @retval = sp_add_jobstep
      @job_id                = @job_id,
      @step_id               = 1,
      @step_name             = N'Step 1',
      @subsystem             = @subsystem,
      @command               = @command,
      @additional_parameters = @parameters,
      @cmdexec_success_code  = @cmdexecsuccesscode,
      @server                = @server,
      @database_name         = @databasename,
      @database_user_name    = @username,
      @retry_attempts        = @retryattempts,
      @retry_interval        = @retrydelay,
      @os_run_priority       = @runpriority

    IF (@retval <> 0)
    BEGIN
      ROLLBACK TRANSACTION
      GOTO Quit
    END

    -- Add the job schedule
    IF (@activestartdate = 0)
      SELECT @activestartdate = NULL
    IF (@activeenddate = 0)
      SELECT @activeenddate = NULL
    IF (@activestarttimeofday = 0)
      SELECT @activestarttimeofday = NULL
    IF (@activeendtimeofday = 0)
      SELECT @activeendtimeofday = NULL
    IF (@freqtype <> 0x2) -- OnDemand tasks simply have no schedule in 7.0
    BEGIN
      EXECUTE @retval = sp_add_jobschedule
        @job_id                 = @job_id,
        @name                   = N'6.x schedule',
        @enabled                = 1,
        @freq_type              = @freqtype,
        @freq_interval          = @freqinterval,
        @freq_subday_type       = @freqsubtype,
        @freq_subday_interval   = @freqsubinterval,
        @freq_relative_interval = @freqrelativeinterval,
        @freq_recurrence_factor = @freqrecurrencefactor,
        @active_start_date      = @activestartdate,
        @active_end_date        = @activeenddate,
        @active_start_time      = @activestarttimeofday,
        @active_end_time        = @activeendtimeofday

      IF (@retval <> 0)
      BEGIN
        ROLLBACK TRANSACTION
        GOTO Quit
      END
    END

    -- And finally, add the job server
    EXECUTE @retval = sp_add_jobserver @job_id = @job_id, @server_name  = N'(local)'

    IF (@retval <> 0)
    BEGIN
      ROLLBACK TRANSACTION
      GOTO Quit
    END

    -- Add the replication agent for monitoring
    IF (@category_id = 13) -- Logreader
    BEGIN
      SELECT @distdb = distribution_db from MSdistpublishers where name = @server
      SELECT @proc = @distdb + '.dbo.sp_MSadd_logreader_agent'

      EXECUTE @retval = @proc
        @name = @name,
        @publisher = @server,
        @publisher_db = @databasename,
        @publication = '',  
        @local_job = 1,
        @job_existing = 1,
        @job_id = @job_id

      IF (@retval <> 0)
      BEGIN
        ROLLBACK TRANSACTION
        GOTO Quit
      END
    END
    ELSE 
    IF (@category_id = 15) -- Snapshot
    BEGIN
      DECLARE @publication sysname

      EXECUTE @retval = master.dbo.sp_MSget_publication_from_taskname
                            @taskname = @name,
                            @publisher = @server,
                            @publisherdb = @databasename,
                            @publication = @publication OUTPUT
      
      IF (@publication IS NOT NULL)
      BEGIN     
    
        SELECT @distdb = distribution_db from MSdistpublishers where name = @server
        SELECT @proc = @distdb + '.dbo.sp_MSadd_snapshot_agent'

        EXECUTE @retval = @proc
                @name = @name,
                @publisher = @server,
                @publisher_db = @databasename,
                @publication = @publication,
                @local_job = 1,
                @job_existing = 1,
                @snapshot_jobid = @job_id

        IF (@retval <> 0)
        BEGIN
          ROLLBACK TRANSACTION
          GOTO Quit
        END

        SELECT @proc = @distdb + '.dbo.sp_MSadd_publication'
        EXECUTE @retval = @proc
                @publisher = @server,
                @publisher_db = @databasename,
                @publication = @publication,
                @publication_type = 0 -- Transactional
        IF (@retval <> 0)
        BEGIN
          ROLLBACK TRANSACTION
          GOTO Quit
        END
      END
    END

  COMMIT TRANSACTION

  -- If this is an autostart LogReader or Distribution job, add the [new] '-Continuous' paramter to the command
  IF (@freqtype = 0x40) AND ((UPPER(@subsystem) = N'LOGREADER') OR (UPPER(@subsystem) = N'DISTRIBUTION'))
  BEGIN
    UPDATE msdb.dbo.sysjobsteps
    SET command = command + N' -Continuous'
    WHERE (job_id = @job_id)
      AND (step_id = 1)
  END  

  -- If this is an autostart job, start it now (for backwards compatibility with 6.x SQLExecutive behaviour)
  IF (@freqtype = 0x40)
    EXECUTE msdb.dbo.sp_start_job @job_id = @job_id, @error_flag = 0, @output_flag = 0

Quit:
  RETURN(@retval) -- 0 means success

END
go

/**************************************************************/
/* SP_UPDATETASK                                              */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure sp_updatetask...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_updatetask')
              AND (type = 'P')))
  DROP PROCEDURE sp_updatetask
go
CREATE PROCEDURE sp_updatetask
  @currentname            sysname        = NULL, -- Was VARCHAR(100) in 6.x
  @id                     INT            = NULL,
  @name                   sysname        = NULL, -- Was VARCHAR(100) in 6.x
  @subsystem              NVARCHAR(40)   = NULL, -- Was VARCHAR(30) in 6.x
  @server                 NVARCHAR(30)   = NULL, -- Was VARCHAR(30) in 6.x
  @username               sysname        = NULL, -- Was VARCHAR(30) in 6.x
  @databasename           sysname        = NULL, -- Was VARCHAR(30) in 6.x
  @enabled                TINYINT        = NULL,
  @freqtype               INT            = NULL,
  @freqinterval           INT            = NULL,
  @freqsubtype            INT            = NULL,
  @freqsubinterval        INT            = NULL,
  @freqrelativeinterval   INT            = NULL,
  @freqrecurrencefactor   INT            = NULL,
  @activestartdate        INT            = NULL,
  @activeenddate          INT            = NULL,
  @activestarttimeofday   INT            = NULL,
  @activeendtimeofday     INT            = NULL,
  @nextrundate            INT            = NULL,
  @nextruntime            INT            = NULL,
  @runpriority            INT            = NULL,
  @emailoperatorname      sysname        = NULL, -- Was VARCHAR(50) in 6.x
  @retryattempts          INT            = NULL,
  @retrydelay             INT            = NULL,
  @command                NVARCHAR(3200) = NULL,
  @loghistcompletionlevel INT            = NULL,
  @emailcompletionlevel   INT            = NULL,
  @description            VARCHAR(512)   = NULL,
  @tagadditionalinfo      VARCHAR(96)    = NULL, -- Obsolete in 7.0
  @tagobjectid            INT            = NULL, -- Obsolete in 7.0
  @tagobjecttype          INT            = NULL, -- Obsolete in 7.0
  @parameters             TEXT           = NULL,
  @cmdexecsuccesscode     INT            = NULL
AS
BEGIN
  DECLARE @retval INT
  DECLARE @job_id UNIQUEIDENTIFIER

  SET NOCOUNT ON

  SELECT @retval = 1 -- 0 means success, 1 means failure

  -- Convert old replication synchronization subsystem name to the 7.0 name
  IF (LOWER(@subsystem) = N'sync')
    SELECT @subsystem = N'Snapshot'

  IF ((@id IS NULL) AND (@currentname IS NULL)) OR
     ((@id IS NOT NULL) AND (@currentname IS NOT NULL))
  BEGIN
    RAISERROR(14246, -1, -1)
    RETURN(1) -- Failure
  END

  -- In 6.x active start date was not restricted, but it is in 7.0; so to avoid a "noisey"
  -- failure in sp_update_jobschedule we modify the value accordingly
  IF ((@activestartdate IS NOT NULL) AND (@activestartdate < 19900101))
    SELECT @activestartdate = 19900101

  -- If the name is supplied, get the job_id directly from sysjobs
  IF (@currentname IS NOT NULL)
  BEGIN
    -- Check if the name is ambiguous
    IF ((SELECT COUNT(*)
         FROM msdb.dbo.sysjobs_view
         WHERE (name = @currentname)) > 1)
    BEGIN
      RAISERROR(14292, -1, -1, @currentname, '@id', '@currentname')
      RETURN(1) -- Failure
    END

    SELECT @job_id = job_id
    FROM msdb.dbo.sysjobs_view
    WHERE (name = @currentname)

    SELECT @id = task_id
    FROM msdb.dbo.systaskids
    WHERE (job_id = @job_id)

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

  -- If the id is supplied lookup the corresponding job_id from systaskids
  IF (@id IS NOT NULL)
  BEGIN
    SELECT @job_id = job_id
    FROM msdb.dbo.systaskids
    WHERE (task_id = @id)

    -- Check that the job still exists
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysjobs_view
                    WHERE (job_id = @job_id)))
    BEGIN
      SELECT @currentname = CONVERT(NVARCHAR, @id)
      RAISERROR(14262, -1, -1, '@id', @currentname)
      RETURN(1) -- Failure
    END
  END

  -- Do the update
  BEGIN TRANSACTION

    -- Update the Job Step
    IF (@subsystem          IS NOT NULL) OR
       (@command            IS NOT NULL) OR
       (@cmdexecsuccesscode IS NOT NULL) OR
       (@server             IS NOT NULL) OR
       (@databasename       IS NOT NULL) OR
       (@username           IS NOT NULL) OR
       (@retryattempts      IS NOT NULL) OR
       (@retrydelay         IS NOT NULL) OR
       (@runpriority        IS NOT NULL)
    BEGIN
      EXECUTE @retval = sp_update_jobstep
        @job_id               = @job_id,
        @step_id              = 1,
        @subsystem            = @subsystem,
        @command              = @command,
        @cmdexec_success_code = @cmdexecsuccesscode,
        @server               = @server,
        @database_name        = @databasename,
        @database_user_name   = @username,
        @retry_attempts       = @retryattempts,
        @retry_interval       = @retrydelay,
        @os_run_priority      = @runpriority

      IF (@retval <> 0)
      BEGIN
        ROLLBACK TRANSACTION
        GOTO Quit
      END
    END

    -- Now update the job itself
    IF (@name                   IS NOT NULL) OR
       (@enabled                IS NOT NULL) OR
       (@description            IS NOT NULL) OR
       (@loghistcompletionlevel IS NOT NULL) OR
       (@emailcompletionlevel   IS NOT NULL) OR
       (@emailoperatorname      IS NOT NULL)
    BEGIN
      EXECUTE @retval = sp_update_job
        @job_id                     = @job_id,
        @new_name                   = @name,
        @enabled                    = @enabled,
        @description                = @description,
        @notify_level_eventlog      = @loghistcompletionlevel,
        @notify_level_email         = @emailcompletionlevel,
        @notify_email_operator_name = @emailoperatorname

    IF (@retval <> 0)
      BEGIN
        ROLLBACK TRANSACTION
        GOTO Quit
      END
    END

    -- Finally, update the job schedule
    IF (@freqtype             IS NOT NULL) OR
       (@freqinterval         IS NOT NULL) OR
       (@freqsubtype          IS NOT NULL) OR
       (@freqsubinterval      IS NOT NULL) OR
       (@freqrelativeinterval IS NOT NULL) OR
       (@freqrecurrencefactor IS NOT NULL) OR
       (@activestartdate      IS NOT NULL) OR
       (@activeenddate        IS NOT NULL) OR
       (@activestarttimeofday IS NOT NULL) OR
       (@activeendtimeofday   IS NOT NULL)
    BEGIN
      IF (@freqtype = 0x2)
      BEGIN
        -- OnDemand tasks simply have no schedule in 7.0, so delete the job schedule
        EXECUTE @retval = sp_delete_jobschedule
          @job_id = @job_id,
          @name   = N'6.x schedule'
      END
      ELSE
      BEGIN
        IF (NOT EXISTS (SELECT *
                        FROM msdb.dbo.sysjobschedules
                        WHERE (job_id = @job_id)
                          AND (name = N'6.x schedule')))
          EXECUTE @retval = sp_add_jobschedule
            @job_id                 = @job_id,
            @name                   = N'6.x schedule',
            @enabled                = 1,
            @freq_type              = @freqtype,
            @freq_interval          = @freqinterval,
            @freq_subday_type       = @freqsubtype,
            @freq_subday_interval   = @freqsubinterval,
            @freq_relative_interval = @freqrelativeinterval,
            @freq_recurrence_factor = @freqrecurrencefactor,
            @active_start_date      = @activestartdate,
            @active_end_date        = @activeenddate,
            @active_start_time      = @activestarttimeofday,
            @active_end_time        = @activeendtimeofday
        ELSE
          EXECUTE @retval = sp_update_jobschedule
            @job_id                 = @job_id,
            @name                   = N'6.x schedule',
            @enabled                = 1,
            @freq_type              = @freqtype,
            @freq_interval          = @freqinterval,
            @freq_subday_type       = @freqsubtype,
            @freq_subday_interval   = @freqsubinterval,
            @freq_relative_interval = @freqrelativeinterval,
            @freq_recurrence_factor = @freqrecurrencefactor,
            @active_start_date      = @activestartdate,
            @active_end_date        = @activeenddate,
            @active_start_time      = @activestarttimeofday,
            @active_end_time        = @activeendtimeofday
      END

      IF (@retval <> 0)
      BEGIN
        ROLLBACK TRANSACTION
        GOTO Quit
      END
    END

  COMMIT TRANSACTION

Quit:
  RETURN(@retval) -- 0 means success

END
go

/**************************************************************/
/* SP_DROPTASK                                                */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure sp_droptask...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_droptask')
              AND (type = 'P')))
  DROP PROCEDURE sp_droptask
go
CREATE PROCEDURE sp_droptask
  @name      sysname = NULL, -- Was VARCHAR(100) in 6.x
  @loginname sysname = NULL, -- Was VARCHAR(30) in 6.x
  @id        INT     = NULL
AS
BEGIN
  DECLARE @retval INT
  DECLARE @job_id UNIQUEIDENTIFIER
  DECLARE @category_id int

  SET NOCOUNT ON

  IF ((@name      IS NULL)     AND (@id    IS NULL)     AND (@loginname IS NULL)) OR
     ((@name      IS NOT NULL) AND ((@id   IS NOT NULL) OR  (@loginname IS NOT NULL))) OR
     ((@id        IS NOT NULL) AND ((@name IS NOT NULL) OR  (@loginname IS NOT NULL))) OR
     ((@loginname IS NOT NULL) AND ((@name IS NOT NULL) OR  (@id        IS NOT NULL)))
  BEGIN
    RAISERROR(14245, -1, -1)
    RETURN(1) -- Failure
  END

  -- If the name is supplied, get the job_id directly from sysjobs
  IF (@name IS NOT NULL)
  BEGIN
    -- Check if the name is ambiguous
    IF ((SELECT COUNT(*)
         FROM msdb.dbo.sysjobs_view
         WHERE (name = @name)) > 1)
    BEGIN
      RAISERROR(14292, -1, -1, @name, '@id', '@name')
      RETURN(1) -- Failure
    END

    SELECT @job_id = job_id, @category_id = category_id
    FROM msdb.dbo.sysjobs_view
    WHERE (name = @name)

    SELECT @id = task_id
    FROM msdb.dbo.systaskids
    WHERE (job_id = @job_id)

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

  -- If the id is supplied lookup the corresponding job_id from systaskids
  IF (@id IS NOT NULL)
  BEGIN
    SELECT @job_id = job_id
    FROM msdb.dbo.systaskids
    WHERE (task_id = @id)

    -- Check that the job still exists
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysjobs_view
                    WHERE (job_id = @job_id)))
    BEGIN
      SELECT @name = CONVERT(NVARCHAR, @id)
      RAISERROR(14262, -1, -1, '@id', @name)
      RETURN(1) -- Failure
    END

    -- Get the name of this job
    SELECT @name = name, @category_id = category_id
    FROM msdb.dbo.sysjobs_view
    WHERE (job_id = @job_id)
  END

  -- Delete the specific job
  IF (@name IS NOT NULL)
  BEGIN
    BEGIN TRANSACTION

    DELETE FROM msdb.dbo.systaskids
    WHERE (job_id = @job_id)
    EXECUTE @retval = sp_delete_job @job_id = @job_id
    IF (@retval <> 0)
	BEGIN
      ROLLBACK TRANSACTION
	  GOTO Quit
	END

	-- If a Logreader or Snapshot task, delete corresponding replication agent information 
	IF @category_id = 13 or @category_id = 15
	BEGIN
   	  EXECUTE @retval = sp_MSdrop_6x_replication_agent @job_id, @category_id
	  IF (@retval <> 0)
	  BEGIN
	  	ROLLBACK TRANSACTION
	  	GOTO Quit
	  END
	END


    COMMIT TRANSACTION
  END

  -- Delete all jobs belonging to the specified login
  IF (@loginname IS NOT NULL)
  BEGIN
    BEGIN TRANSACTION

    DELETE FROM msdb.dbo.systaskids
    WHERE job_id IN (SELECT job_id
                     FROM msdb.dbo.sysjobs_view
                     WHERE (owner_sid = SUSER_SID(@loginname)))
    EXECUTE @retval = sp_manage_jobs_by_login @action = 'DELETE',
                                              @current_owner_login_name = @loginname
    IF (@retval <> 0)
    BEGIN
      ROLLBACK TRANSACTION
      GOTO Quit
    END		

    COMMIT TRANSACTION
  END

Quit:
  RETURN(@retval) -- 0 means success

END
go


/**************************************************************/
/* SP_HELPTASK                                                */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure procedure sp_helptask...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_helptask')
              AND (type = 'P')))
  DROP PROCEDURE sp_helptask
go
CREATE PROCEDURE sp_helptask
  @taskname     sysname      = NULL, -- Was VARCHAR(100) in 6.x
  @taskid       INT          = NULL,
  @loginname    sysname      = NULL, -- Was VARCHAR(20) in 6.x
  @operatorname sysname      = NULL, -- Was VARCHAR(50) in 6.x
  @subsystem    NVARCHAR(40) = NULL, -- Was VARCHAR(30) in 6.x
  @mode         VARCHAR(10)  = 'QUICK'  -- Or 'FULL'
AS
BEGIN
  DECLARE @operator_id INT
  DECLARE @owner_sid   VARBINARY(85)

  SET NOCOUNT ON

  -- Convert old replication synchronization subsystem name to the 7.0 name
  IF (LOWER(@subsystem) = N'sync')
    SELECT @subsystem = N'Snapshot'

  -- Get the login id for the login name (if supplied)
  IF (@loginname IS NOT NULL)
  BEGIN
    IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0) AND (SUSER_SNAME() <> @loginname)
    BEGIN
      RAISERROR(14247, -1, -1)
      RETURN(1) -- Failure
    END

    SELECT @owner_sid = SUSER_ID(@loginname)
    IF (@owner_sid IS NULL)
    BEGIN
      RAISERROR(14234, -1, -1, '@loginname', 'sp_helplogins')
      RETURN(1) -- Failure
    END
  END

  -- Get the operator id for the operator name (if supplied)
  IF (@operatorname IS NOT NULL)
  BEGIN
    SELECT @operator_id = id
    FROM msdb.dbo.sysoperators
    WHERE (name = @operatorname)

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

  -- Check the mode
  SELECT @mode = UPPER(@mode)
  IF (@mode NOT IN ('FULL', 'QUICK'))
  BEGIN
    RAISERROR(14266, -1, -1, '@mode', 'FULL, QUICK')
    RETURN(1) -- Failure
  END

  -- Return task details...
  -- NOTE: SQLOLE and Starfighter rely on the 'FULL' mode format - do not change it!
  IF (@mode = 'FULL')
  BEGIN
    SELECT name                   = sjv.name,
           id                     = sti.task_id,
           subsystem              = sjst.subsystem,
           server                 = sjst.server,
           username               = sjst.database_user_name,
           ownerloginname         = SUSER_SNAME(sjv.owner_sid),
           databasename           = sjst.database_name,
           enabled                = sjv.enabled,
           freqtype               = ISNULL(sjsch.freq_type, 2), -- On Demand
           freqinterval           = ISNULL(sjsch.freq_interval, 0),
           freqsubtype            = ISNULL(sjsch.freq_subday_type, 0),
           freqsubinterval        = ISNULL(sjsch.freq_subday_interval, 0),
           freqrelativeinterval   = ISNULL(sjsch.freq_relative_interval, 0),
           freqrecurrencefactor   = ISNULL(sjsch.freq_recurrence_factor, 0),
           activestartdate        = ISNULL(sjsch.active_start_date, 19900101),
           activeenddate          = ISNULL(sjsch.active_end_date, 99991231),
           activestarttimeofday   = ISNULL(sjsch.active_start_time, 0),
           activeendtimeofday     = ISNULL(sjsch.active_end_time, 235959),
           lastrundate            = sjs.last_run_date,
           lastruntime            = sjs.last_run_time,
           nextrundate            = ISNULL(sjsch.next_run_date, 0),
           nextruntime            = ISNULL(sjsch.next_run_time, 0),
           runpriority            = sjst.os_run_priority,
           emailoperatorname      = so.name,
           retryattempts          = sjst.retry_attempts,
           retrydelay             = sjst.retry_interval,
           datecreated            = sjv.date_created,
           datemodified           = sjv.date_modified,
           command                = sjst.command,
           lastruncompletionlevel = sjs.last_run_outcome,
           lastrunduration        = sjst.last_run_duration,
           lastrunretries         = sjst.last_run_retries,
           loghistcompletionlevel = sjv.notify_level_eventlog,
           emailcompletionlevel   = sjv.notify_level_email,
           description            = sjv.description,
           tagadditionalinfo      = 0,
           tagobjectid            = 0,
           tagobjecttype          = 0,
           cmdexecsuccesscode     = sjst.cmdexec_success_code
    FROM msdb.dbo.sysjobs_view                    sjv
         LEFT OUTER JOIN msdb.dbo.sysoperators    so    ON (sjv.notify_email_operator_id = so.id)
         LEFT OUTER JOIN msdb.dbo.systaskids      sti   ON (sjv.job_id = sti.job_id)
         LEFT OUTER JOIN msdb.dbo.sysjobschedules sjsch ON (sjv.job_id = sjsch.job_id),
         msdb.dbo.sysjobsteps                     sjst,
         msdb.dbo.sysjobservers                   sjs
    WHERE (sjv.job_id = sjst.job_id)
      AND (sjst.step_id = 1)
      AND (sjv.job_id = sjs.job_id)
      AND (sjs.server_id = 0)
      AND ((sjsch.name = N'6.x schedule') OR (sjsch.name IS NULL)) -- NULL handles the case of the job not having a schedule
      AND ((@owner_sid      IS NULL) OR (sjv.owner_sid                = @owner_sid))
      AND ((@subsystem      IS NULL) OR (sjst.subsystem               = @subsystem))
      AND ((@operator_id    IS NULL) OR (sjv.notify_email_operator_id = @operator_id))
      AND ((@taskname       IS NULL) OR (sjv.name                     = @taskname))
      AND ((@taskid         IS NULL) OR (sti.task_id                  = @taskid))
    ORDER BY sj.name
  END
  ELSE
  BEGIN
    SELECT name      = SUBSTRING(sjv.name, 1, 20),
           id        = sti.task_id,
           subsystem = SUBSTRING(sjst.subsystem, 1, 15),
           server    = SUBSTRING(sjst.server, 1, 20),
           username  = SUBSTRING(sjst.database_user_name, 1, 20),
           dbname    = SUBSTRING(sjst.database_name, 1, 20),
           enabled   = sjv.enabled
    FROM msdb.dbo.sysjobs_view               sjv
         LEFT OUTER JOIN msdb.dbo.systaskids sti ON (sjv.job_id = sti.job_id),
         msdb.dbo.sysjobsteps                sjst
    WHERE (sjv.job_id = sjst.job_id)
      AND (sjst.step_id = 1)
      AND ((@owner_sid      IS NULL) OR (sjv.owner_sid                = @owner_sid))
      AND ((@subsystem      IS NULL) OR (sjst.subsystem               = @subsystem))
      AND ((@operator_id    IS NULL) OR (sjv.notify_email_operator_id = @operator_id))
      AND ((@taskname       IS NULL) OR (sjv.name                     = @taskname))
      AND ((@taskid         IS NULL) OR (sti.task_id                  = @taskid))
    ORDER BY sjv.name
  END
END
go

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go

/**************************************************************/
/* SP_VERIFYTASKID                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure procedure sp_verifytaskid...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_verifytaskid')
              AND (type = 'P')))
  DROP PROCEDURE sp_verifytaskid
go
CREATE PROCEDURE sp_verifytaskid
  @taskid    INT,
  @subsystem NVARCHAR(40) = N'%'
AS
BEGIN
  SET NOCOUNT ON

  -- Convert old replication synchronization subsystem name to the 7.0 name
  IF (LOWER(@subsystem) = N'sync')
    SELECT @subsystem = N'Snapshot'

  -- Check if the task [job] exists
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysjobs_view sjv,
                   msdb.dbo.sysjobsteps  sjst,
                   msdb.dbo.systaskids   sti
              WHERE (sjv.job_id = sjst.job_id)
                AND (sjv.job_id = sti.job_id)
                AND (sti.task_id = @taskid)
                AND (LOWER(sjst.subsystem) LIKE LOWER(@subsystem))))
    RETURN(0) -- Success
  ELSE
    RETURN(1) -- Failure
END
go

/**************************************************************/
/* SP_REASSIGNTASK                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure sp_reassigntask...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_reassigntask')
              AND (type = 'P')))
  DROP PROCEDURE sp_reassigntask
go
CREATE PROCEDURE sp_reassigntask
  @taskname     sysname = NULL, -- Was VARCHAR(100) in 6.x
  @newloginname sysname,        -- Was VARCHAR(30) in 6.x
  @oldloginname sysname = NULL  -- Was VARCHAR(30) in 6.x
AS
BEGIN
  DECLARE @retval INT

  SET NOCOUNT ON

  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(14244, -1, -1)
    RETURN(1) -- Failure
  END

  -- Check that we have either task or old login name (or both, though it's redundant)
  IF ((@taskname IS NULL) AND (@oldloginname IS NULL))
  BEGIN
    RAISERROR(14249, -1, -1)
    RETURN(1) -- Failure
  END

  -- Case [1]: Reassign a specific task [job]...
  IF (@taskname IS NOT NULL)
  BEGIN
    -- Check new login id
    IF (SUSER_ID(@newloginname) IS NULL)
    BEGIN
      RAISERROR(14262, -1, -1, '@newloginname', @newloginname)
      RETURN(1) -- Failure
    END

    -- NOTE: Normally we'd invoke sp_update_job by supplying the job_id, but since this is
    --       a legacy procedure we cannot change the parameter list to have the job_id
    --       supplied to us.  Hence we use name and we run the risk of a [handled] error
    --       if the task [job] name is not unique.
    EXECUTE @retval = sp_update_job @job_name = @taskname, @owner_login_name = @newloginname
    RETURN(@retval) -- 0 means success
  END

  -- Case [2]: Reassign all jobs belonging to the specified old login name...
  IF (@oldloginname IS NOT NULL)
  BEGIN
    EXECUTE @retval = sp_manage_jobs_by_login @action = 'REASSIGN',
                                              @current_owner_login_name = @oldloginname,
                                              @new_owner_login_name = @newloginname
    RETURN(@retval) -- 0 means success
  END
END
go

/**************************************************************/
/* SP_HELPHISTORY (Replication doesn't need this - It is here */
/* for completeness only)                                     */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure sp_helphistory...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_helphistory')
              AND (type = 'P')))
  DROP PROCEDURE sp_helphistory
go
CREATE PROCEDURE sp_helphistory
  @taskname            sysname     = NULL,   -- Was VARCHAR(100) in 6.x
  @taskid              INT         = NULL,
  @eventid             INT         = NULL,
  @messageid           INT         = NULL,
  @severity            INT         = NULL,
  @source              VARCHAR(30) = NULL,   -- Obsolete in 7.0
  @category            VARCHAR(30) = NULL,   -- Obsolete in 7.0
  @startdate           INT         = NULL,   -- YYYYMMDD format
  @enddate             INT         = NULL,   -- YYYYMMDD format
  @starttime           INT         = NULL,   -- HHMMSS format
  @endtime             INT         = NULL,   -- HHMMSS format
  @minimumtimesskipped INT         = NULL,
  @minimumrunduration  INT         = NULL,   -- HHMMSS format
  @runstatusmask       INT         = NULL,   -- SQLOLE_COMPLETION_STATUS
  @minimumretries      INT         = NULL,
  @oldestfirst         INT         = NULL,
  @mode                VARCHAR(10) = 'QUICK' -- Or FULL
AS
BEGIN
  DECLARE @job_id   UNIQUEIDENTIFIER
  DECLARE @order_by INT

  SET NOCOUNT ON

  -- If the name is supplied, get the job_id directly from sysjobs
  IF (@taskname IS NOT NULL)
  BEGIN
    SELECT @job_id = job_id
    FROM msdb.dbo.sysjobs_view
    WHERE (name = @taskname)

    -- Check if the name is ambiguous
    IF (@@rowcount > 1)
    BEGIN
      RAISERROR(14292, -1, -1, @taskname, '@taskid', '@taskname')
      RETURN(1) -- Failure
    END

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

  -- If the id is supplied lookup the corresponding job_id from systaskids
  IF (@taskid IS NOT NULL)
  BEGIN
    SELECT @job_id = job_id
    FROM msdb.dbo.systaskids
    WHERE (task_id = @taskid)

    -- Check that the job still exists
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysjobs_view
                    WHERE (job_id = @job_id)))
    BEGIN
      SELECT @taskname = CONVERT(NVARCHAR, @taskid)
      RAISERROR(14262, -1, -1, '@taskid', @taskname)
      RETURN(1) -- Failure
    END
  END

  SELECT @mode = UPPER(@mode)
  IF (@mode = 'QUICK')
    SELECT @mode = 'SUMMARY'

  CREATE TABLE #task_history_full
  (
  instance_id       INT              NOT NULL,
  job_id            UNIQUEIDENTIFIER NOT NULL,
  job_name          sysname          NOT NULL,
  step_id           INT              NOT NULL,
  step_name         sysname          NOT NULL,
  sql_message_id    INT              NOT NULL,
  sql_severity      INT              NOT NULL,
  message           NVARCHAR(1024)   NULL,
  run_status        INT              NOT NULL,
  run_date          INT              NOT NULL,
  run_time          INT              NOT NULL,
  run_duration      INT              NOT NULL,
  operator_emailed  sysname          NULL,
  operator_netsent  sysname          NULL,
  operator_paged    sysname          NULL,
  retries_attempted INT              NOT NULL,
  server            NVARCHAR(30)     NOT NULL
  )

  CREATE TABLE #task_history_quick
  (
  job_id            UNIQUEIDENTIFIER NOT NULL,
  job_name          sysname          NOT NULL,
  run_status        INT              NOT NULL,
  run_date          INT              NOT NULL,
  run_time          INT              NOT NULL,
  run_duration      INT              NOT NULL,
  operator_emailed  sysname          NULL,
  operator_netsent  sysname          NULL,
  operator_paged    sysname          NULL,
  retries_attempted INT              NOT NULL,
  server            NVARCHAR(30)     NOT NULL
  )

  IF (@mode = 'FULL')
  BEGIN
    INSERT INTO #task_history_full
    EXECUTE msdb.dbo.sp_help_jobhistory  @job_id               = @job_id,
                                         @sql_message_id       = @messageid,
                                         @sql_severity         = @severity,
                                         @start_run_date       = @startdate,
                                         @end_run_date         = @enddate,
                                         @start_run_time       = @starttime,
                                         @end_run_time         = @endtime,
                                         @minimum_run_duration = @minimumrunduration,
                                         @run_status           = @runstatusmask,
                                         @minimum_retries      = @minimumretries,
                                         @oldest_first         = @oldestfirst,
                                         @mode                 = @mode
  END
  ELSE
  BEGIN
    INSERT INTO #task_history_quick
    EXECUTE msdb.dbo.sp_help_jobhistory  @job_id               = @job_id,
                                         @sql_message_id       = @messageid,
                                         @sql_severity         = @severity,
                                         @start_run_date       = @startdate,
                                         @end_run_date         = @enddate,
                                         @start_run_time       = @starttime,
                                         @end_run_time         = @endtime,
                                         @minimum_run_duration = @minimumrunduration,
                                         @run_status           = @runstatusmask,
                                         @minimum_retries      = @minimumretries,
                                         @oldest_first         = @oldestfirst,
                                         @mode                 = @mode
  END

  IF (@mode = 'FULL')
  BEGIN
    SELECT id                = sti.task_id,
           eventid           = 0,
           messageid         = sql_message_id,
           severity          = sql_severity,
           taskname          = job_name,
           source            = '',
           category          = '',
           runstatus         = run_status,
           rundate           = run_date,
           runtime           = run_time,
           runduration       = run_duration,
           reviewstatus      = 0,
           emailoperatorname = operator_emailed,
           retries           = retries_attempted,
           comments          = message,
           timesskipped      = 0
    FROM #task_history_full thf,
         systaskids         sti
    WHERE (thf.job_id = sti.job_id)
  END
  ELSE
  BEGIN
    SELECT taskname          = job_name,
           source            = '',
           runstatus         = run_status,
           rundate           = run_date,
           runtime           = run_time,
           runduration       = run_duration,
           emailoperatorname = operator_emailed,
           retries           = retries_attempted
    FROM #task_history_quick
  END
END
go

/**************************************************************/
/* SP_PURGEHISTORY (Replication doesn't need this - It is     */
/* here for completeness only)                                */
/**************************************************************/

PRINT ''
PRINT 'Creating [legacy] procedure sp_purgehistory...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_purgehistory')
              AND (type = 'P')))
  DROP PROCEDURE sp_purgehistory
go
CREATE PROCEDURE sp_purgehistory
  @taskname sysname = NULL, -- Was VARCHAR(100) in 6.x
  @taskid   INT     = NULL
AS
BEGIN
  DECLARE @job_id UNIQUEIDENTIFIER

  SET NOCOUNT ON

  -- If the name is supplied, get the job_id directly from sysjobs
  IF (@taskname IS NOT NULL)
  BEGIN
    SELECT @job_id = job_id
    FROM msdb.dbo.sysjobs_view
    WHERE (name = @taskname)

    -- Check if the name is ambiguous
    IF (@@rowcount > 1)
    BEGIN
      RAISERROR(14292, -1, -1, @taskname, '@taskid', '@taskname')
      RETURN(1) -- Failure
    END

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

  -- If the id is supplied lookup the corresponding job_id from systaskids
  IF (@taskid IS NOT NULL)
  BEGIN
    SELECT @job_id = job_id
    FROM msdb.dbo.systaskids
    WHERE (task_id = @taskid)

    -- Check that the job still exists
    IF (NOT EXISTS (SELECT *
                    FROM msdb.dbo.sysjobs_view
                    WHERE (job_id = @job_id)))
    BEGIN
      SELECT @taskname = CONVERT(NVARCHAR, @taskid)
      RAISERROR(14262, -1, -1, '@taskid', @taskname)
      RETURN(1) -- Failure
    END
  END

  EXECUTE sp_purge_jobhistory @job_id = @job_id
END
go


/**************************************************************/
/*                                                            */
/*         E  R  R  O  R    M  E  S  S  A  G  E  S            */
/*                                                            */
/*  These are now created by MESSAGES.SQL.                    */
/*                                                            */
/*  NOTE: 14255 and 14265 are called by dynamic SQL generated */
/*        by SQLServerAgent.                                  */
/**************************************************************/


/**************************************************************/
/*                                                            */
/*                   T  R  I  G  G  E  R  S                   */
/*                                                            */
/**************************************************************/

/**************************************************************/
/* TRIG_TARGETSERVER_INSERT                                   */
/**************************************************************/

PRINT ''
PRINT 'Creating trigger trig_targetserver_insert...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'trig_targetserver_insert')
              AND (type = 'TR')))
  DROP TRIGGER dbo.trig_targetserver_insert
go
CREATE TRIGGER trig_targetserver_insert
ON msdb.dbo.systargetservers
FOR INSERT, DELETE
AS
BEGIN
  SET NOCOUNT ON

  -- Disallow the insert if the server is called 'ALL'
  -- NOTE: We have to do this check here in the trigger since there is no sp_add_targetserver
  --       (target servers insert a row for themselves when they 'enlist' in an MSX)
  IF (EXISTS (SELECT *
              FROM inserted
              WHERE (server_name = N'ALL')))
  BEGIN
    DELETE FROM msdb.dbo.systargetservers
    WHERE (server_name = N'ALL')
    RAISERROR(14271, -1, -1, 'ALL')
    RETURN
  END

  -- Set (or delete) the registy flag (so that SETUP can detect if we're an MSX)
  IF ((SELECT COUNT(*)
       FROM msdb.dbo.systargetservers) = 0)
  BEGIN
    DECLARE @val INT

    EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                  N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                  N'MSXServer',
                                  @val OUTPUT, 
                                  N'no_output'
    IF (@val IS NOT NULL)
      EXECUTE master.dbo.xp_regdeletevalue N'HKEY_LOCAL_MACHINE',
                                           N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                           N'MSXServer'
  END
  ELSE
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'MSXServer',
                                   N'REG_DWORD',
                                   1
END
go

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go



/**************************************************************/
/**                                                          **/
/**          A L E R T S  A N D  O P E R A T O R S           **/
/**                                                          **/
/**************************************************************/

/**************************************************************/
/*                                                            */
/*        C  O  R  E     P  R  O  C  E  D  U  R  E  S         */
/*                                                            */
/**************************************************************/

DUMP TRANSACTION msdb WITH NO_LOG
go

/**************************************************************/
/* SP_VERIFY_PERFORMANCE_CONDITION                            */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_verify_performance_condition...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_verify_performance_condition')
              AND (type = 'P')))
  DROP PROCEDURE sp_verify_performance_condition
go
CREATE PROCEDURE sp_verify_performance_condition
  @performance_condition NVARCHAR(512)
AS
BEGIN
  DECLARE @delimiter_count INT
  DECLARE @temp_str        NVARCHAR(512)
  DECLARE @object_name     sysname
  DECLARE @counter_name    sysname
  DECLARE @instance_name   sysname
  DECLARE @pos             INT
  
  SET NOCOUNT ON

  -- The performance condition must have the format 'object|counter|instance|comparator|value'
  -- NOTE: 'instance' may be empty.
  IF (PATINDEX(N'%_|%_|%|[><=]|[0-9]%', @performance_condition) = 0)
  BEGIN
    RAISERROR(14507, 16, 1)
    RETURN(1) -- Failure
  END

  -- Parse the performance_condition
  SELECT @delimiter_count = 0
  SELECT @temp_str = @performance_condition
  SELECT @pos = CHARINDEX(N'|', @temp_str)
  WHILE (@pos <> 0)
  BEGIN
    SELECT @delimiter_count = @delimiter_count + 1
    IF (@delimiter_count = 1) SELECT @object_name = SUBSTRING(@temp_str, 1, @pos - 1)
    IF (@delimiter_count = 2) SELECT @counter_name = SUBSTRING(@temp_str, 1, @pos - 1)
    IF (@delimiter_count = 3) SELECT @instance_name = SUBSTRING(@temp_str, 1, @pos - 1)
    SELECT @temp_str = SUBSTRING(@temp_str, @pos + 1, (DATALENGTH(@temp_str) / 2) - @pos)
    SELECT @pos = CHARINDEX(N'|', @temp_str)
  END
  IF (@delimiter_count <> 4)
  BEGIN
    RAISERROR(14507, 16, 1)
    RETURN(1) -- Failure
  END

  -- Check the object_name
  IF (NOT EXISTS (SELECT *
                  FROM master.dbo.sysperfinfo
                  WHERE (object_name = @object_name)))
  BEGIN
    RAISERROR(14262, 16, 1, 'object_name', @object_name)
    RETURN(1) -- Failure
  END

  -- Check the counter_name
  IF (NOT EXISTS (SELECT *
                  FROM master.dbo.sysperfinfo
                  WHERE (object_name = @object_name)
                    AND (counter_name = @counter_name)))
  BEGIN
    RAISERROR(14262, 16, 1, 'counter_name', @counter_name)
    RETURN(1) -- Failure
  END

  -- Check the instance_name
  IF (@instance_name IS NOT NULL)
  BEGIN
    IF (NOT EXISTS (SELECT *
                    FROM master.dbo.sysperfinfo
                    WHERE (object_name = @object_name)
                      AND (counter_name = @counter_name)
                      AND (instance_name = @instance_name)))
    BEGIN
      RAISERROR(14262, 16, 1, 'instance_name', @instance_name)
      RETURN(1) -- Failure
    END
  END

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_VERIFY_ALERT                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_verify_alert...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_verify_alert')
              AND (type = 'P')))
  DROP PROCEDURE sp_verify_alert
go
CREATE PROCEDURE sp_verify_alert
  @name                          sysname,
  @message_id                    INT,
  @severity                      INT,
  @enabled                       TINYINT,
  @delay_between_responses       INT,
  @notification_message          NVARCHAR(512),
  @include_event_description_in  TINYINT,
  @database_name                 sysname,
  @event_description_keyword     NVARCHAR(100),
  @job_id                        UNIQUEIDENTIFIER OUTPUT,
  @job_name                      sysname          OUTPUT,
  @occurrence_count              INT,
  @raise_snmp_trap               TINYINT,
  @performance_condition         NVARCHAR(512),
  @category_name                 sysname,
  @category_id                   INT              OUTPUT,
  @count_reset_date              INT,
  @count_reset_time              INT
AS
BEGIN
  DECLARE @retval               INT
  DECLARE @non_alertable_errors VARCHAR(512)
  DECLARE @message_id_as_string VARCHAR(10)
  DECLARE @res_valid_range      NVARCHAR(100)

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @name                      = LTRIM(RTRIM(@name))
  SELECT @notification_message      = LTRIM(RTRIM(@notification_message))
  SELECT @database_name             = LTRIM(RTRIM(@database_name))
  SELECT @event_description_keyword = LTRIM(RTRIM(@event_description_keyword))
  SELECT @job_name                  = LTRIM(RTRIM(@job_name))
  SELECT @performance_condition     = LTRIM(RTRIM(@performance_condition))
  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 the NewName is unique
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysalerts
              WHERE (name = @name)))
  BEGIN
    RAISERROR(14261, 16, 1, '@name', @name)
    RETURN(1) -- Failure
  END

  -- Check if the user has supplied MessageID OR Severity OR Performance-Condition
  IF ((@performance_condition IS NULL)     AND ((@message_id = 0) AND (@severity = 0)) OR ((@message_id <> 0) AND (@severity <> 0))) OR
     ((@performance_condition IS NOT NULL) AND ((@message_id <> 0) OR (@severity <> 0)))
  BEGIN
    RAISERROR(14500, 16, 1)
    RETURN(1) -- Failure
  END

  -- Check the Severity
  IF ((@severity < 0) OR (@severity > 25))
  BEGIN
    RAISERROR(14266, 16, 1, '@severity', '0..25')
    RETURN(1) -- Failure
  END

  -- Check the MessageID
  IF (@message_id <> 0) AND 
     (NOT EXISTS (SELECT error
                  FROM master.dbo.sysmessages
                  WHERE (error = @message_id)))
  BEGIN
    SELECT @message_id_as_string = CONVERT(VARCHAR, @message_id)
    RAISERROR(14262, 16, 1, '@message_id', @message_id_as_string)
    RETURN(1) -- Failure
  END

  -- Check if it is legal to set an alert on this MessageID
  CREATE TABLE #TempRetVal (RetVal INT)
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                N'NonAlertableErrors',
                                 @non_alertable_errors OUTPUT,
                                N'no_output'
  IF (ISNULL(@non_alertable_errors, N'NULL') <> N'NULL')
  BEGIN
    DECLARE @message_id_as_char VARCHAR(10)

    SELECT @message_id_as_char = CONVERT(VARCHAR(10), @message_id)
    INSERT INTO #TempRetVal
    EXECUTE ('IF (' + @message_id_as_char + ' IN (' + @non_alertable_errors + ')) SELECT 1')
  END

  IF (EXISTS (SELECT *
              FROM #TempRetVal))
  BEGIN
    RAISERROR(14506, 16, 1, @message_id)
    RETURN(1) -- Failure
  END

  DROP TABLE #TempRetVal

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

  -- DelayBetweenResponses must be > 0
  IF (@delay_between_responses < 0)
  BEGIN
    SELECT @res_valid_range = FORMATMESSAGE(14206)
    RAISERROR(14266, 16, 1, '@delay_between_responses', @res_valid_range)
    RETURN(1) -- Failure
  END

  -- NOTE: We don't check the notification message

  -- Check IncludeEventDescriptionIn
  IF ((@include_event_description_in < 0) OR (@include_event_description_in > 7))
  BEGIN
    SELECT @res_valid_range = FORMATMESSAGE(14208)
    RAISERROR(14266, 16, 1, '@include_event_description_in', @res_valid_range)
    RETURN(1) -- Failure
  END

  -- Check the database name
  IF (@database_name IS NOT NULL) AND (DB_ID(@database_name) IS NULL)
  BEGIN
    RAISERROR(15010, 16, 1, @database_name)
    RETURN(1) -- Failure
  END

  -- NOTE: We don't check the event description keyword

  -- Check JobName/ID
  IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL))
  BEGIN
    -- We use '' as a special value which means 'no job' (we cannot use NULL since this forces
    -- sp_update_alert to use the existing value)
    IF (@job_name = N'')
      SELECT @job_id = 0x00
    ELSE
    BEGIN
      EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                                  '@job_id',
                                                   @job_name OUTPUT,
                                                   @job_id   OUTPUT
      IF (@retval <> 0)
        RETURN(1) -- Failure
      -- Check that the job is a local job
      IF (NOT EXISTS (SELECT *
                      FROM msdb.dbo.sysjobservers
                      WHERE (job_id = @job_id)
                        AND (server_id = 0)))
      BEGIN
        RAISERROR(14234, -1, -1, '@job_id', 'sp_help_job @job_type = ''LOCAL''')
        RETURN(1) -- Failure
      END
    END
  END

  -- OccurrenceCount must be > 0
  IF (@occurrence_count < 0)
  BEGIN
    RAISERROR(14266, 16, 1, '@occurrence_count', '0..n')
    RETURN(1) -- Failure
  END

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

  -- Check the performance condition (including invalid parameter combinations)
  IF (@performance_condition IS NOT NULL)
  BEGIN
    IF (@database_name IS NOT NULL)
    BEGIN
      RAISERROR(14505, 16, 1, 'database_name')
      RETURN(1) -- Failure
    END

    IF (@event_description_keyword IS NOT NULL)
    BEGIN
      RAISERROR(14505, 16, 1, 'event_description_keyword')
      RETURN(1) -- Failure
    END

    -- Verify the performance condition
    EXECUTE @retval = msdb.dbo.sp_verify_performance_condition @performance_condition
    IF (@retval <> 0)
      RETURN(1) -- Failure
  END

  -- Check category name
  IF (@category_name = N'[DEFAULT]')
    SELECT @category_id = 98
  ELSE
  BEGIN
    SELECT @category_id = category_id
    FROM msdb.dbo.syscategories
    WHERE (category_class = 2) -- Alerts
      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

  -- Check count reset date
  IF (@count_reset_date <> 0)
  BEGIN
    EXECUTE @retval = msdb.dbo.sp_verify_job_date @count_reset_date, '@count_reset_date'
    IF (@retval <> 0)
      RETURN(1) -- Failure
  END

  -- Check count reset time
  IF (@count_reset_time <> 0)
  BEGIN
    EXECUTE @retval = msdb.dbo.sp_verify_job_time @count_reset_time, '@count_reset_time'
    IF (@retval <> 0)
      RETURN(1) -- Failure
  END

  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_ADD_ALERT                                               */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_add_alert...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_add_alert')
              AND (type = 'P')))
  DROP PROCEDURE sp_add_alert
go
CREATE PROCEDURE sp_add_alert
  @name                         sysname,
  @message_id                   INT              = 0,
  @severity                     INT              = 0,
  @enabled                      TINYINT          = 1,
  @delay_between_responses      INT              = 0,
  @notification_message         NVARCHAR(512)    = NULL,
  @include_event_description_in TINYINT          = 5,    -- 0 = None, 1 = Email, 2 = Pager, 4 = NetSend, 7 = All
  @database_name                sysname          = NULL,
  @event_description_keyword    NVARCHAR(100)    = NULL,
  @job_id                       UNIQUEIDENTIFIER = NULL, -- If provided must NOT also provide job_name
  @job_name                     sysname          = NULL, -- If provided must NOT also provide job_id
  @raise_snmp_trap              TINYINT          = 0,
  @performance_condition        NVARCHAR(512)    = NULL, -- New for 7.0
  @category_name                sysname          = NULL  -- New for 7.0
AS
BEGIN
  DECLARE @event_source           NVARCHAR(100)
  DECLARE @event_category_id      INT
  DECLARE @event_id               INT
  DECLARE @last_occurrence_date   INT
  DECLARE @last_occurrence_time   INT
  DECLARE @last_notification_date INT
  DECLARE @last_notification_time INT
  DECLARE @occurrence_count       INT
  DECLARE @count_reset_date       INT
  DECLARE @count_reset_time       INT
  DECLARE @has_notification       INT
  DECLARE @return_code            INT
  DECLARE @duplicate_name         sysname
  DECLARE @category_id            INT
  DECLARE @alert_id               INT

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @name                      = LTRIM(RTRIM(@name))
  SELECT @notification_message      = LTRIM(RTRIM(@notification_message))
  SELECT @database_name             = LTRIM(RTRIM(@database_name))
  SELECT @event_description_keyword = LTRIM(RTRIM(@event_description_keyword))
  SELECT @job_name                  = LTRIM(RTRIM(@job_name))
  SELECT @performance_condition     = LTRIM(RTRIM(@performance_condition))
  SELECT @category_name             = LTRIM(RTRIM(@category_name))

  -- Turn [nullable] empty string parameters into NULLs
  IF (@notification_message      = N'') SELECT @notification_message = NULL
  IF (@database_name             = N'') SELECT @database_name = NULL
  IF (@event_description_keyword = N'') SELECT @event_description_keyword = NULL
  IF (@job_name                  = N'') SELECT @job_name = NULL
  IF (@performance_condition     = N'') SELECT @performance_condition = NULL
  IF (@category_name             = N'') SELECT @category_name = NULL

  SELECT @message_id = ISNULL(@message_id, 0)
  SELECT @severity = ISNULL(@severity, 0)

  -- 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

  -- Hard-code the new Alert defaults
  SELECT @event_source = N'MSSQLServer'
  SELECT @event_category_id = NULL
  SELECT @event_id = NULL
  SELECT @last_occurrence_date = 0
  SELECT @last_occurrence_time = 0
  SELECT @last_notification_date = 0
  SELECT @last_notification_time = 0
  SELECT @occurrence_count = 0
  SELECT @count_reset_date = 0
  SELECT @count_reset_time = 0
  SELECT @has_notification = 0

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

  -- Map a job_id of 0 to the real value we use to mean 'no job'
  IF (@job_id = CONVERT(UNIQUEIDENTIFIER, 0x00)) AND (@job_name IS NULL)
    SELECT @job_name = N''

  -- Verify the Alert
  IF (@job_id = CONVERT(UNIQUEIDENTIFIER, 0x00))
    SELECT @job_id = NULL
  EXECUTE @return_code = sp_verify_alert @name,
                                         @message_id,
                                         @severity,
                                         @enabled,
                                         @delay_between_responses,
                                         @notification_message,
                                         @include_event_description_in,
                                         @database_name,
                                         @event_description_keyword,
                                         @job_id OUTPUT,
                                         @job_name OUTPUT,
                                         @occurrence_count,
                                         @raise_snmp_trap,
                                         @performance_condition,
                                         @category_name,
                                         @category_id OUTPUT,
                                         @count_reset_date,
                                         @count_reset_time
  IF (@return_code <> 0)
    RETURN(1) -- Failure

  -- Check if this Alert already exists
  SELECT @duplicate_name = FORMATMESSAGE(14205)
  SELECT @duplicate_name = name
  FROM msdb.dbo.sysalerts
  WHERE (ISNULL(performance_condition, N'apples') = ISNULL(@performance_condition, N'oranges'))
     OR ((performance_condition IS NULL) AND
         (message_id = @message_id) AND
         (severity = @severity) AND
         (ISNULL(database_name, N'') = ISNULL(@database_name, N'')) AND
         (ISNULL(event_description_keyword, N'') = ISNULL(@event_description_keyword, N'')))
  IF (@duplicate_name <> FORMATMESSAGE(14205))
  BEGIN
    RAISERROR(14501, 16, 1, @duplicate_name)
    RETURN(1) -- Failure
  END

  -- Finally, do the actual INSERT
  INSERT INTO msdb.dbo.sysalerts
         (name,
          event_source,
          event_category_id,
          event_id,
          message_id,
          severity,
          enabled,
          delay_between_responses,
          last_occurrence_date,
          last_occurrence_time,
          last_response_date,
          last_response_time,
          notification_message,
          include_event_description,
          database_name,
          event_description_keyword,
          occurrence_count,
          count_reset_date,
          count_reset_time,
          job_id,
          has_notification,
          flags,
          performance_condition,
          category_id)
  VALUES (@name,
          @event_source,
          @event_category_id,
          @event_id,
          @message_id,
          @severity,
          @enabled,
          @delay_between_responses,
          @last_occurrence_date,
          @last_occurrence_time,
          @last_notification_date,
          @last_notification_time,
          @notification_message,
          @include_event_description_in,
          @database_name,
          @event_description_keyword,
          @occurrence_count,
          @count_reset_date,
          @count_reset_time,
          ISNULL(@job_id, CONVERT(UNIQUEIDENTIFIER, 0x00)),
          @has_notification,
          @raise_snmp_trap,
          @performance_condition,
          @category_id)

  -- Notify SQLServerAgent of the change
  SELECT @alert_id = id
  FROM msdb.dbo.sysalerts
  WHERE (name = @name)
  EXECUTE msdb.dbo.sp_sqlagent_notify @op_type     = N'A',
                                      @alert_id    = @alert_id,
                                      @action_type = N'I'
  RETURN(0) -- Success
END
go

/**************************************************************/
/* SP_UPDATE_ALERT                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_update_alert...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_update_alert')
              AND (type = 'P')))
  DROP PROCEDURE sp_update_alert
go
CREATE PROCEDURE sp_update_alert
  @name                         sysname,
  @new_name                     sysname          = NULL,
  @enabled                      TINYINT          = NULL,
  @message_id                   INT              = NULL,
  @severity                     INT              = NULL,
  @delay_between_responses      INT              = NULL,
  @notification_message         NVARCHAR(512)    = NULL,
  @include_event_description_in TINYINT          = NULL, -- 0 = None, 1 = Email, 2 = Pager. 4 = NetSend, 7 = All
  @database_name                sysname          = NULL,
  @event_description_keyword    NVARCHAR(100)    = NULL,
  @job_id                       UNIQUEIDENTIFIER = NULL, -- If provided must NOT also provide job_name
  @job_name                     sysname          = NULL, -- If provided must NOT also provide job_id
  @occurrence_count             INT              = NULL, -- Can only be set to 0
  @count_reset_date             INT              = NULL, 
  @count_reset_time             INT              = NULL, 
  @last_occurrence_date         INT              = NULL, -- Can only be set to 0
  @last_occurrence_time         INT              = NULL, -- Can only be set to 0
  @last_response_date           INT              = NULL, -- Can only be set to 0
  @last_response_time           INT              = NULL, -- Can only be set to 0
  @raise_snmp_trap              TINYINT          = NULL,
  @performance_condition        NVARCHAR(512)    = NULL, -- New for 7.0
  @category_name                sysname          = NULL  -- New for 7.0
AS
BEGIN
  DECLARE @x_enabled                   TINYINT
  DECLARE @x_message_id                INT
  DECLARE @x_severity                  INT
  DECLARE @x_delay_between_responses   INT
  DECLARE @x_notification_message      NVARCHAR(512)
  DECLARE @x_include_event_description TINYINT
  DECLARE @x_database_name             sysname
  DECLARE @x_event_description_keyword NVARCHAR(100)
  DECLARE @x_occurrence_count          INT
  DECLARE @x_count_reset_date          INT
  DECLARE @x_count_reset_time          INT
  DECLARE @x_last_occurrence_date      INT
  DECLARE @x_last_occurrence_time      INT
  DECLARE @x_last_response_date        INT
  DECLARE @x_last_response_time        INT
  DECLARE @x_flags                     INT
  DECLARE @x_performance_condition     NVARCHAR(512)
  DECLARE @x_job_id                    UNIQUEIDENTIFIER
  DECLARE @x_category_id               INT

  DECLARE @include_event_desc_code     TINYINT
  DECLARE @return_code                 INT
  DECLARE @duplicate_name              sysname
  DECLARE @category_id                 INT
  DECLARE @alert_id                    INT
  DECLARE @cached_attribute_modified   INT

  SET NOCOUNT ON

  -- Remove any leading/trailing spaces from parameters
  SELECT @new_name                  = LTRIM(RTRIM(@new_name))
  SELECT @job_name                  = LTRIM(RTRIM(@job_name))
  SELECT @notification_message      = LTRIM(RTRIM(@notification_message))
  SELECT @database_name             = LTRIM(RTRIM(@database_name))
  SELECT @event_description_keyword = LTRIM(RTRIM(@event_description_keyword))
  SELECT @performance_condition     = LTRIM(RTRIM(@performance_condition))
  SELECT @category_name             = LTRIM(RTRIM(@category_name))

  -- Are we modifying an attribute which SQLServerAgent caches?
  IF ((@new_name                     IS NOT NULL) OR
      (@enabled                      IS NOT NULL) OR
      (@message_id                   IS NOT NULL) OR
      (@severity                     IS NOT NULL) OR
      (@delay_between_responses      IS NOT NULL) OR
      (@notification_message         IS NOT NULL) OR
      (@include_event_description_in IS NOT NULL) OR
      (@database_name                IS NOT NULL) OR
      (@event_description_keyword    IS NOT NULL) OR
      (@job_id                       IS NOT NULL) OR
      (@job_name                     IS NOT NULL) OR
      (@last_response_date           IS NOT NULL) OR
      (@last_response_time           IS NOT NULL) OR
      (@raise_snmp_trap              IS NOT NULL) OR
      (@performance_condition        IS NOT NULL))
    SELECT @cached_attribute_modified = 1
  ELSE
    SELECT @cached_attribute_modified = 0

  -- Map a job_id of 0 to the real value we use to mean 'no job'
  IF (@job_id = CONVERT(UNIQUEIDENTIFIER, 0x00)) AND (@job_name IS NULL)
    SELECT @job_name = N''

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

  -- Certain values (if supplied) may only be updated to 0  
  IF (@occurrence_count <> 0)
  BEGIN
    RAISERROR(14266, -1, -1, '@occurrence_count', '0')
    RETURN(1) -- Failure
  END
  IF (@last_occurrence_date <> 0)
  BEGIN
    RAISERROR(14266, -1, -1, '@last_occurrence_date', '0')
    RETURN(1) -- Failure
  END
  IF (@last_occurrence_time <> 0)
  BEGIN
    RAISERROR(14266, -1, -1, '@last_occurrence_time', '0')
    RETURN(1) -- Failure
  END
  IF (@last_response_date <> 0)
  BEGIN
    RAISERROR(14266, -1, -1, '@last_response_date', '0')
    RETURN(1) -- Failure
  END
  IF (@last_response_time <> 0)
  BEGIN
    RAISERROR(14266, -1, -1, '@last_response_time', '0')
    RETURN(1) -- Failure
  END

  -- Get existing (@x_) values
  SELECT @alert_id                    = id,
         @x_enabled                   = enabled,
         @x_message_id                = message_id,
         @x_severity                  = severity,
         @x_delay_between_responses   = delay_between_responses,
         @x_notification_message      = notification_message,
         @x_include_event_description = include_event_description,
         @x_database_name             = database_name,
         @x_event_description_keyword = event_description_keyword,
         @x_occurrence_count          = occurrence_count,
         @x_count_reset_date          = count_reset_date,
         @x_count_reset_time          = count_reset_time,
         @x_job_id                    = job_id,
         @x_last_occurrence_date      = last_occurrence_date,
         @x_last_occurrence_time      = last_occurrence_time,
         @x_last_response_date        = last_response_date,
         @x_last_response_time        = last_response_time,
         @x_flags                     = flags,
         @x_performance_condition     = performance_condition,
         @x_category_id               = category_id
  FROM msdb.dbo.sysalerts
  WHERE (name = @name)

  SELECT @x_job_id = sjv.job_id
  FROM msdb.dbo.sysalerts    sa,
       msdb.dbo.sysjobs_view sjv
  WHERE (sa.job_id = sjv.job_id)
    AND (sa.name = @name)

  -- Fill out the values for all non-supplied parameters from the existsing values
  IF (@enabled                      IS NULL) SELECT @enabled                      = @x_enabled
  IF (@message_id                   IS NULL) SELECT @message_id                   = @x_message_id
  IF (@severity                     IS NULL) SELECT @severity                     = @x_severity
  IF (@delay_between_responses      IS NULL) SELECT @delay_between_responses      = @x_delay_between_responses
  IF (@notification_message         IS NULL) SELECT @notification_message         = @x_notification_message
  IF (@include_event_description_in IS NULL) SELECT @include_event_description_in = @x_include_event_description
  IF (@database_name                IS NULL) SELECT @database_name                = @x_database_name
  IF (@event_description_keyword    IS NULL) SELECT @event_description_keyword    = @x_event_description_keyword
  IF (@job_id IS NULL) AND (@job_name IS NULL) SELECT @job_id                     = @x_job_id
  IF (@occurrence_count             IS NULL) SELECT @occurrence_count             = @x_occurrence_count
  IF (@count_reset_date             IS NULL) SELECT @count_reset_date             = @x_count_reset_date
  IF (@count_reset_time             IS NULL) SELECT @count_reset_time             = @x_count_reset_time
  IF (@last_occurrence_date         IS NULL) SELECT @last_occurrence_date         = @x_last_occurrence_date
  IF (@last_occurrence_time         IS NULL) SELECT @last_occurrence_time         = @x_last_occurrence_time
  IF (@last_response_date           IS NULL) SELECT @last_response_date           = @x_last_response_date
  IF (@last_response_time           IS NULL) SELECT @last_response_time           = @x_last_response_time
  IF (@raise_snmp_trap              IS NULL) SELECT @raise_snmp_trap              = @x_flags & 0x1
  IF (@performance_condition        IS NULL) SELECT @performance_condition        = @x_performance_condition
  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 = 98)
  END

  -- Turn [nullable] empty string parameters into NULLs
  IF (@new_name                  = N'') SELECT @new_name                  = NULL
  IF (@notification_message      = N'') SELECT @notification_message      = NULL
  IF (@database_name             = N'') SELECT @database_name             = NULL
  IF (@event_description_keyword = N'') SELECT @event_description_keyword = NULL
  IF (@performance_condition     = N'') SELECT @performance_condition     = NULL

  -- Check if this alert would match an already existing alert
  SELECT @duplicate_name = FORMATMESSAGE(14205)
  SELECT @duplicate_name = name
  FROM msdb.dbo.sysalerts
  WHERE (ISNULL(performance_condition, N'') = ISNULL(@performance_condition, N''))
    AND (message_id = @message_id)
    AND (severity = @severity)
    AND (ISNULL(database_name, N'') = ISNULL(@database_name, N''))
    AND (ISNULL(event_description_keyword, N'') = ISNULL(@event_description_keyword, N''))
    AND (name <> @name)
  IF (@duplicate_name <> FORMATMESSAGE(14205))
  BEGIN
    RAISERROR(14501, 16, 1, @duplicate_name)
    RETURN(1) -- Failure
  END

  -- Verify the Alert
  IF (@job_id = CONVERT(UNIQUEIDENTIFIER, 0x00))
    SELECT @job_id = NULL
  EXECUTE @return_code = sp_verify_alert @new_name,
                                         @message_id,
                                         @severity,
                                         @enabled,
                                         @delay_between_responses,
                                         @notification_message,
                                         @include_event_description_in,
                                         @database_name,
                                         @event_description_keyword,
                                         @job_id OUTPUT,
                                         @job_name OUTPUT,
                                         @occurrence_count,
                                         @raise_snmp_trap,
                                         @performance_condition,
                                         @category_name,
                                         @category_id OUTPUT,
                                         @count_reset_date,
                                         @count_reset_time
  IF (@return_code <> 0)
    RETURN(1) -- Failure

  -- If the user didn't supply a NewName, use the old one.
  -- NOTE: This must be done AFTER sp_verify_alert.
  IF (@new_name IS NULL)
    SELECT @new_name = @name

  -- Turn the 1st 'flags' bit on or off accordingly
  IF (@raise_snmp_trap = 0)
    SELECT @x_flags = @x_flags & 0xFFFE
  ELSE
    SELECT @x_flags = @x_flags | 0x0001

  -- Finally, do the actual UPDATE
  UPDATE msdb.dbo.sysalerts
  SET name                        = @new_name,
      message_id                  = @message_id,
      severity                    = @severity,
      enabled                     = @enabled,
      delay_between_responses     = @delay_between_responses,
      notification_message        = @notification_message,
      include_event_description   = @include_event_description_in,
      database_name               = @database_name,
      event_description_keyword   = @event_description_keyword,
      job_id                      = ISNULL(@job_id, CONVERT(UNIQUEIDENTIFIER, 0x00)),
      occurrence_count            = @occurrence_count,
      count_reset_date            = @count_reset_date,
      count_reset_time            = @count_reset_time,
      last_occurrence_date        = @last_occurrence_date,
      last_occurrence_time        = @last_occurrence_time,
      last_response_date          = @last_response_date,
      last_response_time          = @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_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_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_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'AlertFailSafeOperator',
                                   N'REG_SZ',
                                    @new_name
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'AlertFailSafeEmailAddress',
                                   N'REG_SZ',
                                    @email_address
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'AlertFailSafePagerAddress',
                                   N'REG_SZ',
                                     @pager_address
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'AlertFailSafeNetSendAddress',
                                   N'REG_SZ',
                                    @netsend_address

    -- Update AlertNotificationMethod values
    EXECUTE master.dbo.xp_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_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_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_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(14261, -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))

  -- 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 the Notification is valid
  EXECUTE @retval = msdb.dbo.sp_verify_notification @alert_name,
                                                    @operator_name,
                                                    @notification_method,
                                                    @alert_id     OUTPUT,
                                                    @operator_id  OUTPUT
  IF (@retval <> 0)
    RETURN(1) -- Failure

  -- Check if this notification already exists
  -- NOTE: The unique index would catch this, but testing for the problem here lets us
  --       control the message.
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysnotifications
              WHERE (alert_id = @alert_id)
                AND (operator_id = @operator_id)))
  BEGIN
    SELECT @notification = @alert_name + N' / ' + @operator_name + N' / ' + CONVERT(NVARCHAR, @notification_method)
    RAISERROR(14261, 16, 1, @res_notification, @notification)
    RETURN(1) -- Failure
  END

  SELECT @old_has_notification = has_notification 
  FROM msdb.dbo.sysalerts 
  WHERE (id = @alert_id)

  -- Do the INSERT
  INSERT INTO msdb.dbo.sysnotifications
         (alert_id,
          operator_id,
          notification_method)
  VALUES (@alert_id,
          @operator_id,
          @notification_method)

  SELECT @retval = @@error

  SELECT @new_has_notification = has_notification 
  FROM msdb.dbo.sysalerts 
  WHERE (id = @alert_id)

  -- Notify SQLServerAgent of the change - if any - to has_notifications
  IF (@old_has_notification <> @new_has_notification)
    EXECUTE msdb.dbo.sp_sqlagent_notify @op_type     = N'A',
                                        @alert_id    = @alert_id,
                                        @action_type = N'U'

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

/**************************************************************/
/* SP_UPDATE_NOTIFICATION                                     */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_update_notification...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_update_notification')
              AND (type = 'P')))
  DROP PROCEDURE sp_update_notification
go
CREATE PROCEDURE sp_update_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))

  -- 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 the Notification is valid
  EXECUTE sp_verify_notification @alert_name,
                                 @operator_name,
                                 @notification_method,
                                 @alert_id     OUTPUT,
                                 @operator_id  OUTPUT

  -- Check if this notification exists
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysnotifications
                  WHERE (alert_id = @alert_id)
                    AND (operator_id = @operator_id)))
  BEGIN
    SELECT @notification = @alert_name + N' / ' + @operator_name
    RAISERROR(14262, 16, 1, @res_notification, @notification)
    RETURN(1) -- Failure
  END

  SELECT @old_has_notification = has_notification 
  FROM msdb.dbo.sysalerts 
  WHERE (id = @alert_id)

  -- Do the UPDATE
  UPDATE msdb.dbo.sysnotifications
  SET notification_method = @notification_method
  WHERE (alert_id = @alert_id)
    AND (operator_id = @operator_id)

  SELECT @retval = @@error

  SELECT @new_has_notification = has_notification 
  FROM msdb.dbo.sysalerts 
  WHERE (id = @alert_id)

  -- Notify SQLServerAgent of the change - if any - to has_notifications
  IF (@old_has_notification <> @new_has_notification)
    EXECUTE msdb.dbo.sp_sqlagent_notify @op_type       = N'A',
                                          @alert_id    = @alert_id,
                                          @action_type = N'U'

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

/**************************************************************/
/* SP_DELETE_NOTIFICATION                                     */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_delete_notification...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_notification')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_notification
go
CREATE PROCEDURE sp_delete_notification
  @alert_name    sysname,
  @operator_name sysname
AS
BEGIN
  DECLARE @alert_id             INT
  DECLARE @operator_id          INT
  DECLARE @ignored              TINYINT
  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))

  -- 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

  -- Get the alert and operator ID's
  EXECUTE sp_verify_notification @alert_name,
                                 @operator_name,
                                 7,           -- A dummy (but valid) value
                                 @alert_id    OUTPUT,
                                 @operator_id OUTPUT

  -- Check if this notification exists
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysnotifications
                  WHERE (alert_id = @alert_id)
                    AND (operator_id = @operator_id)))
  BEGIN
    SELECT @notification = @alert_name + N' / ' + @operator_name
    RAISERROR(14262, 16, 1, @res_notification, @notification)
    RETURN(1) -- Failure
  END

  SELECT @old_has_notification = has_notification 
  FROM msdb.dbo.sysalerts 
  WHERE (id = @alert_id)

  -- Do the Delete
  DELETE FROM msdb.dbo.sysnotifications
  WHERE (alert_id = @alert_id)
    AND (operator_id = @operator_id)

  SELECT @retval = @@error

  SELECT @new_has_notification = has_notification 
  FROM msdb.dbo.sysalerts 
  WHERE (id = @alert_id)

  -- Notify SQLServerAgent of the change - if any - to has_notifications
  IF (@old_has_notification <> @new_has_notification)
    EXECUTE msdb.dbo.sp_sqlagent_notify @op_type       = N'A',
                                          @alert_id    = @alert_id,
                                          @action_type = N'U'

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

/**************************************************************/
/* SP_HELP_NOTIFICATION                                       */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_help_notification...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_help_notification')
              AND (type = 'P')))
  DROP PROCEDURE sp_help_notification
go
CREATE PROCEDURE sp_help_notification
  @object_type          CHAR(9),   -- Either 'ALERTS'    (enumerates Alerts for given Operator)
                                   --     or 'OPERATORS' (enumerates Operators for given Alert)
  @name                 sysname,   -- Either an Operator Name (if @object_type is 'ALERTS')
                                   --     or an Alert Name    (if @object_type is 'OPERATORS')
  @enum_type            CHAR(10),  -- Either 'ALL'    (enumerate all objects [eg. all alerts irrespective of whether 'Fred' receives a notification for them])
                                   --     or 'ACTUAL' (enumerate only the associated objects [eg. only the alerts which 'Fred' receives a notification for])
                                   --     or 'TARGET' (enumerate only the objects matching @target_name [eg. a single row showing how 'Fred' is notfied for alert 'Test'])
  @notification_method  TINYINT,   -- Either 1 (Email)   - Modifies the result set to only show use_email column
                                   --     or 2 (Pager)   - Modifies the result set to only show use_pager column
                                   --     or 4 (NetSend) - Modifies the result set to only show use_netsend column
                                   --     or 7 (All)     - Modifies the result set to show all the use_xxx columns
  @target_name   sysname = NULL    -- Either an Alert Name    (if @object_type is 'ALERTS')
                                   --     or an Operator Name (if @object_type is 'OPERATORS')
                                   -- NOTE: This parameter is only required if @enum_type is 'TARGET')
AS
BEGIN
  DECLARE @id              INT    -- We use this to store the decode of @name
  DECLARE @target_id       INT    -- We use this to store the decode of @target_name
  DECLARE @select_clause   NVARCHAR(1024)
  DECLARE @from_clause     NVARCHAR(512)
  DECLARE @where_clause    NVARCHAR(512)
  DECLARE @res_valid_range NVARCHAR(100)

  SET NOCOUNT ON

  SELECT @res_valid_range = FORMATMESSAGE(14208)

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

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

  -- Check ObjectType
  IF (@object_type NOT IN ('ALERTS', 'OPERATORS'))
  BEGIN
    RAISERROR(14266, 16, 1, '@object_type', 'ALERTS, OPERATORS')
    RETURN(1) -- Failure
  END

  -- Check AlertName
  IF (@object_type = 'OPERATORS') AND
     (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysalerts
                  WHERE (name = @name)))
  BEGIN
    RAISERROR(14262, 16, 1, '@name', @name)
    RETURN(1) -- Failure
  END

  -- Check OperatorName
  IF (@object_type = 'ALERTS') AND
     (NOT EXISTS (SELECT *
                  FROM msdb.dbo.sysoperators
                  WHERE (name = @name)))
  BEGIN
    RAISERROR(14262, 16, 1, '@name', @name)
    RETURN(1) -- Failure
  END

  -- Check EnumType
  IF (@enum_type NOT IN ('ALL', 'ACTUAL', 'TARGET'))
  BEGIN
    RAISERROR(14266, 16, 1, '@enum_type', 'ALL, ACTUAL, TARGET')
    RETURN(1) -- Failure
  END

  -- Check Notification Method
  IF ((@notification_method < 1) OR (@notification_method > 7))
  BEGIN
    RAISERROR(14266, 16, 1, '@notification_method', @res_valid_range)
    RETURN(1) -- Failure
  END

  -- If EnumType is 'TARGET', check if we have a @TargetName parameter
  IF (@enum_type = 'TARGET') AND (@target_name IS NULL)
  BEGIN
    RAISERROR(14502, 16, 1)
    RETURN(1) -- Failure
  END

  -- If EnumType isn't 'TARGET', we shouldn't have an @target_name parameter
  IF (@enum_type <> 'TARGET') AND (@target_name IS NOT NULL)
  BEGIN
    RAISERROR(14503, 16, 1)
    RETURN(1) -- Failure
  END

  -- Translate the Name into an ID
  IF (@object_type = 'ALERTS')
  BEGIN
    SELECT @id = id
    FROM msdb.dbo.sysoperators
    WHERE (name = @name)
  END
  IF (@object_type = 'OPERATORS')
  BEGIN
    SELECT @id = id
    FROM msdb.dbo.sysalerts
    WHERE (name = @name)
  END

  -- Translate the TargetName into a TargetID
  IF (@target_name IS NOT NULL)
  BEGIN
    IF (@object_type = 'OPERATORS')
    BEGIN
      SELECT @target_id = id
      FROM msdb.dbo.sysoperators
      WHERE (name = @target_name )
    END
    IF (@object_type = 'ALERTS')
    BEGIN
      SELECT @target_id = id
      FROM msdb.dbo.sysalerts
      WHERE (name = @target_name)
    END
    IF (@target_id IS NULL) -- IE. the Target Name is invalid
    BEGIN
      RAISERROR(14262, 16, 1, @object_type, @target_name)
      RETURN(1) -- Failure
    END
  END

  -- Ok, the parameters look good so generate the SQL then EXECUTE() it...

  -- Generate the 'stub' SELECT clause and the FROM clause
  IF (@object_type = 'OPERATORS') -- So we want a list of Operators for the supplied AlertID
  BEGIN
    SELECT @select_clause = N'SELECT operator_id = o.id, operator_name = o.name, '
    IF (@enum_type = 'ALL')
      SELECT @from_clause = N'FROM msdb.dbo.sysoperators o LEFT OUTER JOIN msdb.dbo.sysnotifications sn ON (o.id = sn.operator_id) '
    ELSE
      SELECT @from_clause = N'FROM msdb.dbo.sysoperators o, msdb.dbo.sysnotifications sn '
  END
  IF (@object_type = 'ALERTS') -- So we want a list of Alerts for the supplied OperatorID
  BEGIN
    SELECT @select_clause = N'SELECT alert_id = a.id, alert_name = a.name, '
    IF (@enum_type = 'ALL')
      SELECT @from_clause = N'FROM msdb.dbo.sysalerts a LEFT OUTER JOIN msdb.dbo.sysnotifications sn ON (a.id = sn.alert_id) '
    ELSE
      SELECT @from_clause = N'FROM msdb.dbo.sysalerts a, msdb.dbo.sysnotifications sn '
  END

  -- Add the required use_xxx columns to the SELECT clause
  IF (@notification_method & 1 = 1)
    SELECT @select_clause = @select_clause + N'use_email = ISNULL((sn.notification_method & 1) / POWER(2, 0), 0), '
  IF (@notification_method & 2 = 2)
    SELECT @select_clause = @select_clause + N'use_pager = ISNULL((sn.notification_method & 2) / POWER(2, 1), 0), '
  IF (@notification_method & 4 = 4)
    SELECT @select_clause = @select_clause + N'use_netsend = ISNULL((sn.notification_method & 4) / POWER(2, 2), 0), '

  -- Remove the trailing comma
  SELECT @select_clause = SUBSTRING(@select_clause, 1, (DATALENGTH(@select_clause) / 2) - 2) + N' '

  -- Generate the WHERE clause
  IF (@object_type = 'OPERATORS')
  BEGIN
    IF (@enum_type = 'ALL')
      SELECT @from_clause = @from_clause + N' AND (sn.alert_id = ' + CONVERT(VARCHAR(10), @id) + N')'

    IF (@enum_type = 'ACTUAL')
      SELECT @where_clause = N'WHERE (o.id = sn.operator_id) AND (sn.alert_id = ' + CONVERT(VARCHAR(10), @id) + N') AND (sn.notification_method & ' + CONVERT(VARCHAR, @notification_method) + N' <> 0)'

    IF (@enum_type = 'TARGET')
      SELECT @where_clause = N'WHERE (o.id = sn.operator_id) AND (sn.operator_id = ' + CONVERT(VARCHAR(10), @target_id) + N') AND (sn.alert_id = ' + CONVERT(VARCHAR(10), @id) + N')'
  END
  IF (@object_type = 'ALERTS')
  BEGIN
    IF (@enum_type = 'ALL')
      SELECT @from_clause = @from_clause + N' AND (sn.operator_id = ' + CONVERT(VARCHAR(10), @id) + N')'

    IF (@enum_type = 'ACTUAL')
      SELECT @where_clause = N'WHERE (a.id = sn.alert_id) AND (sn.operator_id = ' + CONVERT(VARCHAR(10), @id) + N') AND (sn.notification_method & ' + CONVERT(VARCHAR, @notification_method) + N' <> 0)'

    IF (@enum_type = 'TARGET')
      SELECT @where_clause = N'WHERE (a.id = sn.alert_id) AND (sn.alert_id = ' + CONVERT(VARCHAR(10), @target_id) + N') AND (sn.operator_id = ' + CONVERT(VARCHAR(10), @id) + N')'
  END

  -- Add the has_email and has_pager columns to the SELECT clause
  IF (@object_type = 'OPERATORS')
  BEGIN
    SELECT @select_clause = @select_clause + N', has_email = PATINDEX(N''%[^ ]%'', ISNULL(o.email_address, N''''))'
    SELECT @select_clause = @select_clause + N', has_pager = PATINDEX(N''%[^ ]%'', ISNULL(o.pager_address, N''''))'
    SELECT @select_clause = @select_clause + N', has_netsend = PATINDEX(N''%[^ ]%'', ISNULL(o.netsend_address, N''''))'
  END
  IF (@object_type = 'ALERTS')
  BEGIN
    -- NOTE: We return counts so that the UI can detect 'unchecking' the last notification
    SELECT @select_clause = @select_clause + N', has_email = (SELECT COUNT(*) FROM sysnotifications WHERE (alert_id = a.id) AND ((notification_method & 1) = 1)) '
    SELECT @select_clause = @select_clause + N', has_pager = (SELECT COUNT(*) FROM sysnotifications WHERE (alert_id = a.id) AND ((notification_method & 2) = 2)) '
    SELECT @select_clause = @select_clause + N', has_netsend = (SELECT COUNT(*) FROM sysnotifications WHERE (alert_id = a.id) AND ((notification_method & 4) = 4)) '
  END

  EXECUTE (@select_clause + @from_clause + @where_clause)

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

DUMP TRANSACTION msdb WITH NO_LOG
go

/**************************************************************/
/*                                                            */
/*                    T  R  I G  G  E  R  S                   */
/*                                                            */
/**************************************************************/

/**************************************************************/
/* DROP THE OLD 6.x TRIGGERS                                  */
/* [multiple triggers of the same type are allowed in 7.0]    */
/**************************************************************/

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'NewOrChangedNotification')
              AND (type = 'TR')))
  DROP TRIGGER NewOrChangedNotification

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'RemovedNotification')
              AND (type = 'TR')))
  DROP TRIGGER RemovedNotification
go

/**************************************************************/
/* TRIG_NOTIFICATION_INS_OR_UPD                               */
/**************************************************************/

PRINT ''
PRINT 'Creating trigger trig_notification_ins_or_upd...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'trig_notification_ins_or_upd')
              AND (type = 'TR')))
  DROP TRIGGER trig_notification_ins_or_upd
go
CREATE TRIGGER trig_notification_ins_or_upd
ON msdb.dbo.sysnotifications
FOR INSERT,
    UPDATE
AS
BEGIN
  SET NOCOUNT ON

  -- First, throw out 'non-notification' rows
  DELETE FROM msdb.dbo.sysnotifications
  WHERE (notification_method = 0)

  -- Reset the has_notification flag for the affected alerts
  UPDATE msdb.dbo.sysalerts
  SET has_notification = 0
  FROM inserted           i,
       msdb.dbo.sysalerts sa
  WHERE (i.alert_id = sa.id)

  -- Update sysalerts.has_notification (for email)
  UPDATE msdb.dbo.sysalerts
  SET has_notification = has_notification | 1
  FROM msdb.dbo.sysalerts        sa,
       msdb.dbo.sysnotifications sn,
       inserted                  i
  WHERE (sa.id = sn.alert_id) 
    AND (sa.id = i.alert_id)
    AND ((sn.notification_method & 1) = 1)

  -- Update sysalerts.has_notification (for pager)
  UPDATE msdb.dbo.sysalerts
  SET has_notification = has_notification | 2
  FROM msdb.dbo.sysalerts        sa,
       msdb.dbo.sysnotifications sn,
       inserted                  i
  WHERE (sa.id = sn.alert_id) 
    AND (sa.id = i.alert_id)
    AND ((sn.notification_method & 2) = 2)

  -- Update sysalerts.has_notification (for netsend)
  UPDATE msdb.dbo.sysalerts
  SET has_notification = has_notification | 4
  FROM msdb.dbo.sysalerts        sa,
       msdb.dbo.sysnotifications sn,
       inserted                  i
  WHERE (sa.id = sn.alert_id) 
    AND (sa.id = i.alert_id)
    AND ((sn.notification_method & 4) = 4)
END
go

/**************************************************************/
/* TRIG_NOTIFICATION_DELETE                                   */
/**************************************************************/

PRINT ''
PRINT 'Creating trigger trig_notification_delete...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'trig_notification_delete')
              AND (type = 'TR')))
  DROP TRIGGER trig_notification_delete
go
CREATE TRIGGER trig_notification_delete
ON msdb.dbo.sysnotifications
FOR DELETE
AS
BEGIN
  SET NOCOUNT ON

  -- Reset the has_notification flag for the affected alerts
  UPDATE msdb.dbo.sysalerts
  SET has_notification = 0
  FROM deleted            d,
       msdb.dbo.sysalerts sa
  WHERE (d.alert_id = sa.id)

  -- Update sysalerts.has_notification (for email)
  UPDATE msdb.dbo.sysalerts
  SET has_notification = has_notification | 1
  FROM msdb.dbo.sysalerts        sa,
       msdb.dbo.sysnotifications sn,
       deleted                   d
  WHERE (sa.id = sn.alert_id) 
    AND (sa.id = d.alert_id)
    AND ((sn.notification_method & 1) = 1)

  -- Update sysalerts.has_notification (for pager)
  UPDATE msdb.dbo.sysalerts
  SET has_notification = has_notification | 2
  FROM msdb.dbo.sysalerts        sa,
       msdb.dbo.sysnotifications sn,
       deleted                   d
  WHERE (sa.id = sn.alert_id) 
    AND (sa.id = d.alert_id)
    AND ((sn.notification_method & 2) = 2)

  -- Update sysalerts.has_notification (for netsend)
  UPDATE msdb.dbo.sysalerts
  SET has_notification = has_notification | 4
  FROM msdb.dbo.sysalerts        sa,
       msdb.dbo.sysnotifications sn,
       deleted                   d
  WHERE (sa.id = sn.alert_id) 
    AND (sa.id = d.alert_id)
    AND ((sn.notification_method & 4) = 4)
END
go

DUMP TRANSACTION msdb WITH NO_LOG
go

/**************************************************************/
/*                                                            */
/*          D  E  F  A  U  L  T    A  L  E  R  T  S           */
/*                                                            */
/**************************************************************/

PRINT ''
PRINT 'Installing default alerts...'
go

EXECUTE master.dbo.sp_altermessage 9002, 'WITH_LOG', TRUE
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Demo: Full msdb log')
                   OR ((severity = 0) AND
                       (message_id = 9002) AND
                       (database_name = N'msdb') AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Full msdb log',
                                @message_id = 9002,
                                @severity = 0,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = N'msdb',
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Demo: Full tempdb')
                   OR ((severity = 0) AND
                       (message_id = 9002) AND
                       (database_name = N'tempdb') AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Full tempdb',
                                @message_id = 9002,
                                @severity = 0,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = N'tempdb',
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Demo: Sev. 19 Errors')
                   OR ((severity = 19) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Sev. 19 Errors',
                                @message_id = 0,
                                @severity = 19,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = NULL,
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Demo: Sev. 20 Errors')
                   OR ((severity = 20) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Sev. 20 Errors',
                                @message_id = 0,
                                @severity = 20,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = NULL,
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Demo: Sev. 21 Errors')
                   OR ((severity = 21) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Sev. 21 Errors',
                                @message_id = 0,
                                @severity = 21,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = NULL,
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Demo: Sev. 22 Errors')
                   OR ((severity = 22) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Sev. 22 Errors',
                                @message_id = 0,
                                @severity = 22,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = NULL,
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE name = N'Demo: Sev. 23 Errors'
                   OR ((severity = 23) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Sev. 23 Errors',
                                @message_id = 0,
                                @severity = 23,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = NULL,
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Demo: Sev. 24 Errors')
                   OR ((severity = 24) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Sev. 24 Errors',
                                @message_id = 0,
                                @severity = 24,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = NULL,
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysalerts
                WHERE (name = N'Demo: Sev. 25 Errors')
                   OR ((severity = 25) AND
                       (message_id = 0) AND
                       (database_name IS NULL) AND
                       (event_description_keyword IS NULL) AND
                       (performance_condition IS NULL))))
  EXECUTE msdb.dbo.sp_add_alert @name = N'Demo: Sev. 25 Errors',
                                @message_id = 0,
                                @severity = 25,
                                @enabled = 1,
                                @delay_between_responses = 10,
                                @database_name = NULL,
                                @notification_message = NULL,
                                @job_name = NULL,
                                @event_description_keyword = NULL,
                                @include_event_description_in = 5 -- Email and NetSend
go


/**************************************************************/
/**                                                          **/
/**       B A C K U P   H I S T O R Y   S U P P O R T        **/
/**                                                          **/
/**************************************************************/

/**************************************************************/
/* T A B L E S                                                */
/**************************************************************/

/**************************************************************/
/* BACKUPMEDIASET                                             */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'backupmediaset')))
BEGIN
  PRINT ''
  PRINT 'Creating table backupmediaset...'

  CREATE TABLE backupmediaset
  (
  media_set_id       INT IDENTITY     NOT NULL PRIMARY KEY,
  media_uuid         UNIQUEIDENTIFIER NULL,  -- Null if this media set only one media family
  media_family_count TINYINT          NULL,  -- Number of media families in the media set
  name               NVARCHAR(128)    NULL,
  description        NVARCHAR(255)    NULL,
  software_name      NVARCHAR(128)    NULL,
  software_vendor_id INT              NULL,
  MTF_major_version  TINYINT          NULL
  )

  CREATE INDEX backupmediasetuuid ON backupmediaset (media_uuid)
END
go

/**************************************************************/
/* BACKUPMEDIAFAMILY                                          */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'backupmediafamily')))
BEGIN
  PRINT ''
  PRINT 'Creating table backupmediafamily...'

  CREATE TABLE backupmediafamily
  (
  media_set_id           INT              NOT NULL REFERENCES backupmediaset(media_set_id),
  family_sequence_number TINYINT          NOT NULL, -- Raid sequence number
  media_family_id        UNIQUEIDENTIFIER NULL,     -- This will be a uuid in MTF 2.0, allow space
  media_count            INT              NULL,     -- Number of media in the family
  logical_device_name    NVARCHAR(128)    NULL,     -- Name from sysdevices, if any
  physical_device_name   NVARCHAR(260)    NULL,     -- To facilitate restores from online media (disk)
  device_type            TINYINT          NULL,  -- Disk, tape, pipe, ...
  physical_block_size    INT              NULL
  PRIMARY KEY (media_set_id, family_sequence_number)
  )

  CREATE INDEX backupmediafamilyuuid ON backupmediafamily (media_family_id)
END
go

/**************************************************************/
/* BACKUPSET - One row per backup operation.                  */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'backupset')))
BEGIN
  PRINT ''
  PRINT 'Creating table backupset...'

  CREATE TABLE backupset
  (
  backup_set_id          INT IDENTITY     NOT NULL PRIMARY KEY,
  backup_set_uuid        UNIQUEIDENTIFIER NOT NULL,
  media_set_id           INT              NOT NULL REFERENCES backupmediaset(media_set_id),
  first_family_number    TINYINT          NULL,  -- family number & media number of the media
  first_media_number     SMALLINT         NULL,  -- containing the start of this backup (first SSET)
  last_family_number     TINYINT          NULL,  -- family number & media number of the media
  last_media_number      SMALLINT         NULL,  -- containing the end of this backup (ESET after MBC)
  catalog_family_number  TINYINT          NULL,  -- family number & media number of the media
  catalog_media_number   SMALLINT         NULL,  -- containing the start of the 'directory' data stream

  position               INT              NULL,  -- For FILE=
  expiration_date        DATETIME         NULL,

  -- From SSET...
  software_vendor_id     INT              NULL,  -- Might want table for sw vendors
  name                   NVARCHAR(128)    NULL,
  description            NVARCHAR(255)    NULL,
  user_name              NVARCHAR(128)    NULL,
  software_major_version TINYINT          NULL,	
  software_minor_version TINYINT          NULL,	
  software_build_version SMALLINT         NULL,
  time_zone              SMALLINT         NULL,		
  mtf_minor_version      TINYINT          NULL,

  -- From CONFIG_INFO...
  first_lsn              NUMERIC(25,0)    NULL,
  last_lsn               NUMERIC(25,0)    NULL,
  checkpoint_lsn         NUMERIC(25,0)    NULL,
  database_backup_lsn    NUMERIC(25,0)    NULL,
  database_creation_date DATETIME         NULL,
  backup_start_date      DATETIME         NULL,
  backup_finish_date     DATETIME         NULL,
  type                   CHAR(1)          NULL,
  sort_order             SMALLINT         NULL,
  code_page              SMALLINT         NULL,
  compatibility_level    TINYINT          NULL,
  database_version       INT              NULL,
  backup_size            NUMERIC(20,0)    NULL,
  database_name          NVARCHAR(128)    NULL,
  server_name            NVARCHAR(128)    NULL,
  machine_name           NVARCHAR(128)    NULL
  )

  CREATE INDEX backupsetuuid ON backupset (backup_set_uuid)
END
go

/**************************************************************/
/* BACKUPFILE - One row per file backed up (data file, log    */
/*              file)                                         */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'backupfile')))
BEGIN
  PRINT ''
  PRINT 'Creating table backupfile...'

  CREATE TABLE backupfile
  (
  backup_set_id          INT           NOT NULL REFERENCES backupset(backup_set_id),
  first_family_number    TINYINT       NULL,     -- Family number & media number of he first media
  first_media_number     SMALLINT      NULL,     -- containing this file
  filegroup_name         NVARCHAR(128) NULL,
  page_size              INT           NULL,
  file_number            NUMERIC(10,0) NOT NULL,
  backed_up_page_count   NUMERIC(10,0) NULL,
  file_type              CHAR(1)       NULL,     -- database or log
  source_file_block_size NUMERIC(10,0) NULL,
  file_size              NUMERIC(20,0) NULL,
  logical_name           NVARCHAR(128) NULL,
  physical_drive         VARCHAR(260)  NULL,     -- Drive or partition name
  physical_name          VARCHAR(260)  NULL      -- Remainder of physical (OS) filename
  PRIMARY KEY (backup_set_id, file_number)
  )
END
go

/**************************************************************/
/* RESTOREHISTORY - One row per restore operation.            */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'restorehistory')))
BEGIN
  PRINT ''
  PRINT 'Creating table restorehistory...'

  CREATE TABLE restorehistory
  (
  restore_history_id        INT           NOT NULL IDENTITY PRIMARY KEY,
  restore_date              DATETIME      NULL,
  destination_database_name NVARCHAR(128) NULL,
  user_name                 NVARCHAR(128) NULL,
  backup_set_id             INT           NOT NULL REFERENCES backupset(backup_set_id), -- The backup set restored
  restore_type              CHAR(1)       NULL,      -- Database, file, filegroup, log, verifyonly, ...

  -- Various options...
  replace                   BIT           NULL,      -- Replace(1), Noreplace(0)
  recovery                  BIT           NULL,      -- Recovery(1), Norecovery(0)
  restart                   BIT           NULL,      -- Restart(1), Norestart(0)
  stop_at                   DATETIME      NULL,
  device_count              TINYINT       NULL       -- Can be less than number of media families
  )

  CREATE INDEX restorehistorybackupset ON restorehistory (backup_set_id)
END
go

/**************************************************************/
/* RESTOREFILE - One row per file restored.                   */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'restorefile')))
BEGIN
  PRINT ''
  PRINT 'Creating table restorefile...'

  CREATE TABLE restorefile
  (
  restore_history_id     INT           NOT NULL REFERENCES restorehistory(restore_history_id),
  file_number            NUMERIC(10,0) NULL,      -- Note: requires database to make unique
  destination_phys_drive VARCHAR(260)  NULL,
  destination_phys_name  VARCHAR(260)  NULL
  )
END
go

/**************************************************************/
/* RESTOREFILEGROUP - One row per filegroup restored.         */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'restorefilegroup')))
BEGIN
  PRINT ''
  PRINT 'Creating table restorefilegroup...'

  CREATE TABLE restorefilegroup
  (
  restore_history_id INT           NOT NULL REFERENCES restorehistory(restore_history_id),
  filegroup_name     NVARCHAR(128) NULL
  )
END
go


/**************************************************************/
/**                                                          **/
/**           O B J E C T    P E R M I S S I O N S           **/
/**                                                          **/
/**************************************************************/

PRINT ''
PRINT 'Setting object permissions...'
go

-- Permissions a non-SA needs to create/update/delete a job
GRANT EXECUTE ON sp_get_sqlagent_properties  TO PUBLIC
GRANT EXECUTE ON sp_help_category            TO PUBLIC
GRANT EXECUTE ON sp_enum_sqlagent_subsystems TO PUBLIC
GRANT EXECUTE ON sp_add_jobserver            TO PUBLIC
GRANT EXECUTE ON sp_delete_jobserver         TO PUBLIC
GRANT SELECT  ON syscategories               TO PUBLIC

GRANT EXECUTE ON sp_purge_jobhistory TO PUBLIC
GRANT EXECUTE ON sp_help_jobhistory  TO PUBLIC

GRANT EXECUTE ON sp_add_jobstep    TO PUBLIC
GRANT EXECUTE ON sp_update_jobstep TO PUBLIC
GRANT EXECUTE ON sp_delete_jobstep TO PUBLIC
GRANT EXECUTE ON sp_help_jobstep   TO PUBLIC

GRANT EXECUTE ON sp_add_jobschedule    TO PUBLIC
GRANT EXECUTE ON sp_update_jobschedule TO PUBLIC
GRANT EXECUTE ON sp_delete_jobschedule TO PUBLIC
GRANT EXECUTE ON sp_help_jobschedule   TO PUBLIC

GRANT EXECUTE ON sp_add_job    TO PUBLIC
GRANT EXECUTE ON sp_update_job TO PUBLIC
GRANT EXECUTE ON sp_delete_job TO PUBLIC
GRANT EXECUTE ON sp_help_job   TO PUBLIC
GRANT EXECUTE ON sp_start_job  TO PUBLIC
GRANT EXECUTE ON sp_stop_job   TO PUBLIC

GRANT EXECUTE ON sp_help_jobserver TO PUBLIC

GRANT EXECUTE ON sp_check_for_owned_jobs     TO PUBLIC
GRANT EXECUTE ON sp_check_for_owned_jobsteps TO PUBLIC
GRANT EXECUTE ON sp_get_jobstep_db_username  TO PUBLIC
GRANT EXECUTE ON sp_post_msx_operation       TO PUBLIC
GRANT EXECUTE ON sp_get_job_alerts           TO PUBLIC

GRANT EXECUTE ON sp_uniquetaskname TO PUBLIC
GRANT EXECUTE ON sp_addtask        TO PUBLIC
GRANT EXECUTE ON sp_updatetask     TO PUBLIC
GRANT EXECUTE ON sp_droptask       TO PUBLIC
GRANT EXECUTE ON sp_helptask       TO PUBLIC
GRANT EXECUTE ON sp_verifytaskid   TO PUBLIC
GRANT EXECUTE ON sp_reassigntask   TO PUBLIC
GRANT EXECUTE ON sp_helphistory    TO PUBLIC
GRANT EXECUTE ON sp_purgehistory   TO PUBLIC

GRANT SELECT ON sysjobs_view  TO PUBLIC
GRANT SELECT ON systasks_view TO PUBLIC
REVOKE ALL ON systargetservers                 FROM PUBLIC
REVOKE ALL ON systargetservers_view            FROM PUBLIC
REVOKE ALL ON systargetservergroups            FROM PUBLIC
REVOKE ALL ON systargetservergroupmembers      FROM PUBLIC
REVOKE INSERT, UPDATE, DELETE ON syscategories FROM PUBLIC
REVOKE ALL ON sysalerts                        FROM PUBLIC
REVOKE ALL ON sysoperators                     FROM PUBLIC
REVOKE ALL ON sysnotifications                 FROM PUBLIC

GRANT SELECT ON backupfile        TO PUBLIC
GRANT SELECT ON backupmediafamily TO PUBLIC
GRANT SELECT ON backupmediaset    TO PUBLIC
GRANT SELECT ON backupset         TO PUBLIC
GRANT SELECT ON restorehistory    TO PUBLIC
GRANT SELECT ON restorefile       TO PUBLIC
GRANT SELECT ON restorefilegroup  TO PUBLIC
go

-- Create the TargetServers role (for use by target servers when downloading jobs / uploading status)
IF (EXISTS (SELECT * 
            FROM msdb.dbo.sysusers
            WHERE (name = N'TargetServersRole')
              AND (issqlrole = 1)))
BEGIN
  -- If there are no members in the role, then drop and re-create it
  IF ((SELECT COUNT(*) 
       FROM msdb.dbo.sysusers   su,
            msdb.dbo.sysmembers sm
       WHERE (su.uid = sm.groupuid)
         AND (su.name = N'TargetServersRole')
         AND (su.issqlrole = 1)) = 0)
  BEGIN
    EXECUTE msdb.dbo.sp_droprole @rolename = N'TargetServersRole'
    EXECUTE msdb.dbo.sp_addrole @rolename = N'TargetServersRole'
  END
END
ELSE
  EXECUTE msdb.dbo.sp_addrole @rolename = N'TargetServersRole'

GRANT SELECT, UPDATE, DELETE ON sysdownloadlist               TO TargetServersRole
GRANT SELECT, UPDATE         ON sysjobservers                 TO TargetServersRole
GRANT SELECT, UPDATE         ON systargetservers              TO TargetServersRole
GRANT EXECUTE                ON sp_downloaded_row_limiter     TO TargetServersRole
GRANT SELECT                 ON sysjobs                       TO TargetServersRole
GRANT EXECUTE                ON sp_help_jobstep               TO TargetServersRole
GRANT EXECUTE                ON sp_help_jobschedule           TO TargetServersRole
GRANT EXECUTE                ON sp_sqlagent_refresh_job       TO TargetServersRole
GRANT EXECUTE                ON sp_sqlagent_probe_msx         TO TargetServersRole
GRANT EXECUTE                ON sp_sqlagent_check_msx_version TO TargetServersRole
go

USE msdb
go

/**************************************************************/
/**************************************************************/
/* BEGIN DTS                                                  */
/**************************************************************/
/**************************************************************/

/**************************************************************/
/* DTS TABLES                                                 */
/* These are never dropped since we dropped MSDB itself if    */
/* this was an upgrade from pre-beta3, and we preserve beta3  */
/* packages.  However, we need to add the owner_sid column    */
/* if it's not there already, defaulting to sa ownership.     */
/**************************************************************/

/**************************************************************/
/* SYSDTSCATEGORIES                                           */
/**************************************************************/
IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdtscategories')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdtscategories...'
  CREATE TABLE sysdtscategories
  (
    name                   sysname             NOT NULL,
    description            NVARCHAR(1024)      NULL,
    id                     UNIQUEIDENTIFIER    NOT NULL,
    parentid               UNIQUEIDENTIFIER    NOT NULL,         --// IID_NULL if a predefined root category
    CONSTRAINT pk_dtscategories PRIMARY KEY (id),
    CONSTRAINT uq_dtscategories_name_parent UNIQUE (name, parentid)
  )

  /**************************************************************/
  /* PREDEFINED DTS CATEGORIES                                  */
  /**************************************************************/
  PRINT ''
  PRINT 'Adding predefined dts categories...'
  --// MUST BE IN SYNC with DTSPkg.h!
  --// These must be INSERTed explicitly as the IID_NULL parent does not exist.
  INSERT sysdtscategories VALUES (N'Local', 'DTS Packages stored on local SQL Server', 'B8C30000-A282-11D1-B7D9-00C04FB6EFD5', '00000000-0000-0000-0000-000000000000')
  INSERT sysdtscategories VALUES (N'Repository', 'DTS Packages stored on Repository', 'B8C30001-A282-11D1-B7D9-00C04FB6EFD5', '00000000-0000-0000-0000-000000000000')

  --// Default location for DTSPackage.SaveToSQLServer
  INSERT sysdtscategories VALUES (N'LocalDefault', 'Default local subcategory for DTS Packages', 'B8C30002-A282-11D1-B7D9-00C04FB6EFD5', 'B8C30000-A282-11D1-B7D9-00C04FB6EFD5')
END
GO

/**************************************************************/
/* SYSDTSPACKAGES                                             */
/**************************************************************/
IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdtspackages')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdtspackages...'
  CREATE TABLE sysdtspackages
  (
    name                   sysname             NOT NULL,                   --// May have multiple ids
    id                     UNIQUEIDENTIFIER    NOT NULL,                   --// May have multiple versionids
    versionid              UNIQUEIDENTIFIER    NOT NULL UNIQUE,
    description            NVARCHAR(1024)      NULL,
    categoryid             UNIQUEIDENTIFIER    NOT NULL REFERENCES sysdtscategories (id),
    createdate             DATETIME,
    owner                  sysname,
    packagedata            IMAGE,
	owner_sid              VARBINARY(85)       NOT NULL DEFAULT SUSER_SID(N'sa')
    CONSTRAINT pk_dtspackages PRIMARY KEY (id, versionid)
  )
END ELSE BEGIN
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.syscolumns
                  WHERE name = N'owner_sid' AND id = OBJECT_ID(N'sysdtspackages')))
  BEGIN
    PRINT ''
    PRINT 'Altering table sysdtspackages...'
    ALTER TABLE sysdtspackages ADD owner_sid VARBINARY(85) NOT NULL DEFAULT SUSER_SID(N'sa')
  END
END
GO

/**************************************************************/
/* SP_MAKE_DTSPACKAGENAME                                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_make_dtspackagename...'
go
IF OBJECT_ID(N'sp_make_dtspackagename') IS NOT NULL
  DROP PROCEDURE sp_make_dtspackagename
go
CREATE PROCEDURE sp_make_dtspackagename
  @categoryid UNIQUEIDENTIFIER,
  @name sysname OUTPUT,
  @flags int = 0
AS
  SET NOCOUNT ON

  --// If NULL catid, default to the LocalDefault category.
  IF (@categoryid IS NULL)
    SELECT @categoryid = 'B8C30002-A282-11d1-B7D9-00C04FB6EFD5'

  --// Validate category.  We'll generate a unique name within category.
  DECLARE @stringfromclsid NVARCHAR(200)
  IF NOT EXISTS (SELECT * FROM sysdtscategories WHERE id = @categoryid)
  BEGIN
    SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @categoryid)
    RAISERROR(14262, 16, 1, '@categoryid', @stringfromclsid)
    RETURN(1) -- Failure
  END

  --// Autogenerate the next name in our format.
  DECLARE @max sysname, @i INT, @spidchar NVARCHAR(20)

  --// Any logic we use may have collisions so let's get the max and wrap if we have to.
  --// @@spid is necessary for guaranteed uniqueness but makes it ugly so for now, don't use it.
  --// Note:  use only 9 characters as it makes the pattern match easier without overflowing.
  SELECT @i = 0, @spidchar = '_'               -- + LTRIM(STR(@@spid)) + '_'
  SELECT @max = MAX(name)
    FROM sysdtspackages
    WHERE name like 'DTS_' + @spidchar + '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  IF @max IS NOT NULL
    SELECT @i = CONVERT(INT, SUBSTRING(@max, (DATALENGTH(N'DTS_' + @spidchar) / 2) + 1, 9))

  --// Wrap if needed.  Find a gap in the names.
  IF @i < 999999999
  BEGIN
    SELECT @i = @i + 1
  END ELSE BEGIN
    SELECT @i = 1
    DECLARE @existingname sysname
    DECLARE hC CURSOR LOCAL FOR SELECT name FROM sysdtspackages WHERE categoryid = @categoryid ORDER BY name FOR READ ONLY
    OPEN hC
    FETCH NEXT FROM hC INTO @existingname
    WHILE @@FETCH_STATUS = 0 AND @i < 999999999
    BEGIN
      SELECT @name = 'DTS_' + @spidchar + REPLICATE('0', 9 - DATALENGTH(LTRIM(STR(@i)))) + LTRIM(STR(@i))
      IF @existingname > @name
        BREAK
      SELECT @i = @i + 1
      FETCH NEXT FROM hC INTO @existingname
    END
    CLOSE hC
    DEALLOCATE hC
  END

  --// Set the name.
  SELECT @name = 'DTS_' + @spidchar + REPLICATE('0', 9 - DATALENGTH(LTRIM(STR(@i)))) + LTRIM(STR(@i))
  IF (@flags & 1) <> 0
    SELECT @name
GO
GRANT EXECUTE ON sp_make_dtspackagename TO PUBLIC
GO

/**************************************************************/
/* SP_ADD_DTSPACKAGE                                          */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_dtspackage...'
GO
IF OBJECT_ID(N'sp_add_dtspackage') IS NOT NULL
  DROP PROCEDURE sp_add_dtspackage
GO
CREATE PROCEDURE sp_add_dtspackage
  @name sysname,
  @id UNIQUEIDENTIFIER,
  @versionid UNIQUEIDENTIFIER,
  @description NVARCHAR(255),
  @categoryid UNIQUEIDENTIFIER,
  @owner sysname,
  @packagedata IMAGE
AS
  SET NOCOUNT ON

  --// If NULL catid, default to the LocalDefault category.
  IF (@categoryid IS NULL)
    SELECT @categoryid = 'B8C30002-A282-11d1-B7D9-00C04FB6EFD5'

  --// Autogenerate name if it came in NULL.  If it didn't, the below will validate uniqueness.
  IF DATALENGTH(@name) = 0
    SELECT @name = NULL
  IF @name IS NULL
  BEGIN
    --// First see if they specified a new version based on id instead of name.
    if @id IS NOT NULL
    BEGIN
      SELECT @name = name
        FROM sysdtspackages WHERE @id = id
      IF @name IS NOT NULL
        GOTO AddPackage          -- OK, add with the existing name
    END

    --// Name not available, autogenerate one.
    exec sp_make_dtspackagename @categoryid, @name OUTPUT
    GOTO AddPackage
  END

  --// Verify name unique within category.  Allow a new versionid of the same name though.
  IF EXISTS (SELECT * FROM sysdtspackages WHERE name = @name AND categoryid = @categoryid AND id <> @id)
  BEGIN
    RAISERROR (14590, -1, -1, @name)
    RETURN(1) -- Failure
  END

  --// Verify that the same id is not getting a different name.
  IF EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND name <> @name)
  BEGIN
    DECLARE @stringfromclsid NVARCHAR(200)
    SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @id)
    RAISERROR (14597, -1, -1, @stringfromclsid)
    RETURN(1) -- Failure
  END

  --// Verify all versions of a package go in the same category.
  IF EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND categoryid <> @categoryid)
  BEGIN
    RAISERROR (14596, -1, -1, @name)
    RETURN(1) -- Failure
  END

  --// The real information is in the IMAGE; the rest is "documentary".
  --// Therefore, there is no need to verify anything.
  --// The REFERENCE in sysdtspackages will validate @categoryid.
AddPackage:

  --// We will use the original owner_sid for all new versions - all must have the same owner.
  --// New packages will get the current login's SID as owner_sid.
  DECLARE @owner_sid VARBINARY(85)
  SELECT @owner_sid = MIN(owner_sid) FROM sysdtspackages WHERE id = @id
  IF @@rowcount = 0 OR @owner_sid IS NULL
  BEGIN
    SELECT @owner_sid = SUSER_SID()
  END ELSE BEGIN
    --// Only the owner of DTS Package ''%s'' or a member of the sysadmin role may create new versions of it.
    IF (@owner_sid <> SUSER_SID() AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1))
    BEGIN
      RAISERROR (14586, -1, -1, @name)
      RETURN(1) -- Failure
    END
  END

  --// Everything checks out, add the package or its new version.
  INSERT sysdtspackages (
    name,
    id,
    versionid,
    description,
    categoryid,
    createdate,
    owner,
    packagedata,
    owner_sid
  ) VALUES (
    @name,
    @id,
    @versionid,
    @description,
    @categoryid,
    GETDATE(),
    @owner,
    @packagedata,
    @owner_sid
  )
  RETURN 0    -- SUCCESS
GO
GRANT EXECUTE ON sp_add_dtspackage TO PUBLIC
GO

/**************************************************************/
/* SP_DROP_DTSPACKAGE                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_drop_dtspackage...'
go
IF OBJECT_ID(N'sp_drop_dtspackage') IS NOT NULL
  DROP PROCEDURE sp_drop_dtspackage
go
CREATE PROCEDURE sp_drop_dtspackage
  @name sysname,
  @id UNIQUEIDENTIFIER,
  @versionid UNIQUEIDENTIFIER
AS
  SET NOCOUNT ON

  --// Does the specified package (uniquely) exist?  Referencing by name only may not be unique.
  --// We do a bit of a hack here as SQL can't handle a DISTINCT clause with UNIQUEIDENTIFIER.
  --// @id will get the first id returned; if only name specified, see if there are more.
  DECLARE @findid UNIQUEIDENTIFIER
  SELECT @findid = id FROM sysdtspackages 
    WHERE (@name IS NOT NULL OR @id IS NOT NULL OR @versionid IS NOT NULL)
      AND (@name IS NULL OR @name = name)
      AND (@id IS NULL OR @id = id)
      AND (@versionid IS NULL or @versionid = versionid)
  IF @@rowcount = 0
  BEGIN
    DECLARE @pkgnotfound NVARCHAR(200)
    DECLARE @dts_package_res NVARCHAR(100)
    SELECT @pkgnotfound = FORMATMESSAGE(14599) + ' = ''' + ISNULL(@name, FORMATMESSAGE(14589)) + '''; ' + FORMATMESSAGE(14588) + ' {'
    SELECT @pkgnotfound = @pkgnotfound + CASE WHEN @id IS NULL THEN FORMATMESSAGE(14589) ELSE CONVERT(NVARCHAR(50), @id) END + '}.{'
    SELECT @pkgnotfound = @pkgnotfound + CASE WHEN @versionid IS NULL THEN FORMATMESSAGE(14589) ELSE CONVERT(NVARCHAR(50), @versionid) END + '}'
    SELECT @dts_package_res = FORMATMESSAGE(14594)
    RAISERROR(14262, 16, 1, @dts_package_res, @pkgnotfound)
    RETURN(1) -- Failure
  END ELSE IF @name IS NOT NULL AND @id IS NULL AND @versionid IS NULL AND
      EXISTS (SELECT * FROM sysdtspackages WHERE name = @name AND id <> @findid)
  BEGIN
    RAISERROR(14595, -1, -1, @name)
    RETURN(1) -- Failure
  END
  SELECT @id = @findid

  --// Only the owner of DTS Package ''%s'' or a member of the sysadmin role may drop it or any of its versions.
  --// sp_add_dtspackage ensures that all versions have the same owner_sid.
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND owner_sid = SUSER_SID()))
    BEGIN
      SELECT @name = name FROM sysdtspackages WHERE id = @id
      RAISERROR (14587, -1, -1, @name)
      RETURN(1) -- Failure
    END
  END

  --// If @versionid is NULL, drop all versions of name, else only the @versionid version.
  DELETE sysdtspackages
  WHERE id = @id 
    AND (@versionid IS NULL OR @versionid = versionid)
  RETURN 0    -- SUCCESS
go
GRANT EXECUTE ON sp_drop_dtspackage TO PUBLIC
go

/**************************************************************/
/* SP_REASSIGN_DTSPACKAGEOWNER                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_reassign_dtspackageowner...'
go
IF OBJECT_ID(N'sp_reassign_dtspackageowner') IS NOT NULL
  DROP PROCEDURE sp_reassign_dtspackageowner
go
CREATE PROCEDURE sp_reassign_dtspackageowner
  @name sysname,
  @id UNIQUEIDENTIFIER,
  @newloginname sysname
AS
  SET NOCOUNT ON

  --// First, is this a valid login?
  IF SUSER_SID(@newloginname) IS NULL
  BEGIN
    RAISERROR(14262, -1, -1, '@newloginname', @newloginname)
    RETURN(1) -- Failure
  END

  --// Does the specified package (uniquely) exist?  Referencing by name only may not be unique.
  --// We do a bit of a hack here as SQL can't handle a DISTINCT clause with UNIQUEIDENTIFIER.
  --// @id will get the first id returned; if only name specified, see if there are more.
  DECLARE @findid UNIQUEIDENTIFIER
  SELECT @findid = id FROM sysdtspackages 
    WHERE (@name IS NOT NULL OR @id IS NOT NULL)
      AND (@name IS NULL OR @name = name)
      AND (@id IS NULL OR @id = id)
  IF @@rowcount = 0
  BEGIN
    DECLARE @pkgnotfound NVARCHAR(200)
    DECLARE @dts_package_res NVARCHAR(100)
    SELECT @pkgnotfound = FORMATMESSAGE(14599) + ' = ''' + ISNULL(@name, FORMATMESSAGE(14589)) + '''; ' + FORMATMESSAGE(14588) + ' {'
    SELECT @pkgnotfound = @pkgnotfound + CASE WHEN @id IS NULL THEN FORMATMESSAGE(14589) ELSE CONVERT(NVARCHAR(50), @id) END + '}.{'
    SELECT @pkgnotfound = @pkgnotfound + FORMATMESSAGE(14589) + '}'
    SELECT @dts_package_res = FORMATMESSAGE(14594)
    RAISERROR(14262, 16, 1, @dts_package_res, @pkgnotfound)
    RETURN(1) -- Failure
  END ELSE IF @name IS NOT NULL AND @id IS NULL AND 
      EXISTS (SELECT * FROM sysdtspackages WHERE name = @name AND id <> @findid)
  BEGIN
    RAISERROR(14595, -1, -1, @name)
    RETURN(1) -- Failure
  END
  SELECT @id = @findid

  --// Only the owner of DTS Package ''%s'' or a member of the sysadmin role may reassign its ownership.
  --// sp_add_dtspackage ensures that all versions have the same owner_sid.
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    IF (NOT EXISTS (SELECT * FROM sysdtspackages WHERE id = @id AND owner_sid = SUSER_SID()))
    BEGIN
      SELECT @name = name FROM sysdtspackages WHERE id = @id
      RAISERROR (14585, -1, -1, @name)
      RETURN(1) -- Failure
    END
  END

  --// Everything checks out, so reassign the owner.
  --// Note that @newloginname may be a sql server login rather than a network user,
  --// which is not quite the same as when a package is created.
  UPDATE sysdtspackages
    SET owner_sid = SUSER_SID(@newloginname),
	    owner = @newloginname
	WHERE id = @id

  RETURN 0    -- SUCCESS
GO
GRANT EXECUTE ON sp_reassign_dtspackageowner TO PUBLIC
GO

/**************************************************************/
/* SP_GET_DTSPACKAGE                                          */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_get_dtspackage...'
go
IF OBJECT_ID(N'sp_get_dtspackage') IS NOT NULL
  DROP PROCEDURE sp_get_dtspackage
go
CREATE PROCEDURE sp_get_dtspackage
  @name sysname,
  @id UNIQUEIDENTIFIER,
  @versionid UNIQUEIDENTIFIER
AS
  SET NOCOUNT ON

  --// Does the specified package (uniquely) exist?  Dropping by name only may not be unique.
  --// We do a bit of a hack here as SQL can't handle a DISTINCT clause with UNIQUEIDENTIFIER.
  --// @id will get the first id returned; if only name specified, see if there are more.
  DECLARE @findid UNIQUEIDENTIFIER
  SELECT @findid = id FROM sysdtspackages 
    WHERE (@name IS NOT NULL OR @id IS NOT NULL OR @versionid IS NOT NULL)
      AND (@name IS NULL OR @name = name)
      AND (@id IS NULL OR @id = id)
      AND (@versionid IS NULL or @versionid = versionid)
  IF @@rowcount = 0
  BEGIN
    DECLARE @pkgnotfound NVARCHAR(200)
    DECLARE @dts_package_res NVARCHAR(100)
    SELECT @pkgnotfound = FORMATMESSAGE(14599) + ' = ''' + ISNULL(@name, FORMATMESSAGE(14589)) + '''; ' + FORMATMESSAGE(14588) + ' {'
    SELECT @pkgnotfound = @pkgnotfound + CASE WHEN @id IS NULL THEN FORMATMESSAGE(14589) ELSE CONVERT(NVARCHAR(50), @id) END + '}.{'
    SELECT @pkgnotfound = @pkgnotfound + CASE WHEN @versionid IS NULL THEN FORMATMESSAGE(14589) ELSE CONVERT(NVARCHAR(50), @versionid) END + '}'
    SELECT @dts_package_res = FORMATMESSAGE(14594)
    RAISERROR(14262, 16, 1, @dts_package_res, @pkgnotfound)
    RETURN(1) -- Failure
  END ELSE IF @name IS NOT NULL AND @id IS NULL AND @versionid IS NULL AND
      EXISTS (SELECT * FROM sysdtspackages WHERE name = @name AND id <> @findid)
  BEGIN
    RAISERROR(14595, -1, -1, @name)
    RETURN(1) -- Failure
  END
  SELECT @id = @findid

  --// If @versionid is NULL, select all versions of name, else only the @versionid version.
  --// This must return the IMAGE as the rightmost column.
  SELECT
    name,
    id,
    versionid,
    description,
    createdate,
    owner,
    pkgsize = datalength(packagedata),
    packagedata,
    isowner = CASE WHEN (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1 OR owner_sid = SUSER_SID()) THEN 1 ELSE 0 END
  FROM sysdtspackages
  WHERE id = @id
    AND (@versionid IS NULL OR @versionid = versionid)
  ORDER BY name, createdate DESC

  RETURN 0    -- SUCCESS
go
GRANT EXECUTE ON sp_get_dtspackage TO PUBLIC
go

/**************************************************************/
/* SP_REASSIGN_DTSPACKAGECATEGORY                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_reassign_dtspackagecategory...'
go
IF OBJECT_ID(N'sp_reassign_dtspackagecategory') IS NOT NULL
  DROP PROCEDURE sp_reassign_dtspackagecategory
go
CREATE PROCEDURE sp_reassign_dtspackagecategory
  @packageid UNIQUEIDENTIFIER,
  @categoryid UNIQUEIDENTIFIER
AS
  SET NOCOUNT ON

  --// Does the package exist?
  DECLARE @stringfromclsid NVARCHAR(200)
  IF NOT EXISTS (SELECT * from sysdtspackages WHERE id = @packageid)
  BEGIN
    SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @packageid)
    RAISERROR(14262, 16, 1, '@packageid', @stringfromclsid)
    RETURN(1) -- Failure
  END

  --// Does the category exist?
  IF NOT EXISTS (SELECT * FROM sysdtscategories WHERE id = @categoryid)
  BEGIN
    SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @categoryid)
    RAISERROR(14262, 16, 1, '@categoryid', @stringfromclsid)
    RETURN(1) -- Failure
  END

  UPDATE sysdtspackages SET categoryid = @categoryid WHERE id = @packageid
go

/**************************************************************/
/* SP_ENUM_DTSPACKAGES                                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_enum_dtspackages...'
go
IF OBJECT_ID(N'sp_enum_dtspackages') IS NOT NULL
  DROP PROCEDURE sp_enum_dtspackages
go
CREATE PROCEDURE sp_enum_dtspackages
  @name_like sysname = '%',
  @description_like NVARCHAR(255) = '%',
  @categoryid UNIQUEIDENTIFIER = NULL,
  @flags INT = 0,          --// Bitmask:  0x01 == return image data
                           --//           0x02 == recursive (packagenames and categorynames only)
                           --//           0x04 == all versions (default == only most-recent-versions)
                           --//           0x08 == all prior versions versions (not most-recent; requires @id)
  @id UNIQUEIDENTIFIER = NULL    --// If non-NULL, enum versions of this package.
AS
  IF (@flags & 0x02) <> 0
    GOTO DO_RECURSE

  --// Just return the non-IMAGE stuff - sp_get_dtspackage will return the
  --// actual dtspackage info. 
  DECLARE @latestversiondate datetime
  SELECT @latestversiondate = NULL
  IF (@flags & 0x08 = 0x08)
  BEGIN
    SELECT @latestversiondate = MAX(t.createdate) FROM sysdtspackages t WHERE t.id = @id
    IF @latestversiondate IS NULL
    BEGIN
      DECLARE @pkgnotfound NVARCHAR(200)
      DECLARE @dts_package_res NVARCHAR(100)
      SELECT @pkgnotfound = FORMATMESSAGE(14599) + ' = ' + FORMATMESSAGE(14589) + '; ' + FORMATMESSAGE(14588) + ' {'
      SELECT @pkgnotfound = @pkgnotfound + CASE WHEN @id IS NULL THEN FORMATMESSAGE(14589) ELSE CONVERT(NVARCHAR(50), @id) END + '}.{'
      SELECT @pkgnotfound = @pkgnotfound + FORMATMESSAGE(14589) + '}'
      SELECT @dts_package_res = FORMATMESSAGE(14594)
      RAISERROR(14262, 16, 1, @dts_package_res, @pkgnotfound)
      RETURN(1) -- Failure
    END 
  END
  SELECT
    p.name,
    p.id,
    p.versionid,
    p.description,
    p.createdate,
    p.owner,
    size = datalength(p.packagedata),
    packagedata = CASE (@flags & 0x01) WHEN 0 THEN NULL ELSE p.packagedata END,
    isowner = CASE WHEN (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1 OR owner_sid = SUSER_SID()) THEN 1 ELSE 0 END
  FROM sysdtspackages p
  WHERE (@name_like IS NULL OR p.name LIKE @name_like)
    AND (@description_like IS NULL OR p.description LIKE @description_like)
    AND (@categoryid IS NULL OR p.categoryid = @categoryid)
    AND (@id is NULL OR p.id = @id)
    -- These filter by version
    AND ( (@flags & 0x08 = 0x08 AND p.createdate < @latestversiondate)
          OR ( (@flags & 0x04 = 0x04)
		       OR (@flags & 0x08 = 0 AND p.createdate = (SELECT MAX(t.createdate) FROM sysdtspackages t WHERE t.id = p.id))
             )
        )
  ORDER BY id, createdate DESC
  RETURN 0    -- SUCCESS

  DO_RECURSE:
  DECLARE @packagesfound INT
  SELECT @packagesfound = 0

  --// Starting parent category.  If null, start at root.
  if (@categoryid IS NULL)
    SELECT @categoryid = '00000000-0000-0000-0000-000000000000'

  IF EXISTS (SELECT *
      FROM sysdtspackages p INNER JOIN sysdtscategories c ON p.categoryid = c.id
      WHERE p.categoryid = @categoryid
      AND (@name_like IS NULL OR p.name LIKE @name_like)
      AND (@description_like IS NULL OR p.description LIKE @description_like)
    )
    SELECT @packagesfound = 1

  IF (@packagesfound <> 0)
  BEGIN
    --// Identify the category and list its Packages.
    SELECT 'Level' = @@nestlevel, 'PackageName' = p.name, 'CategoryName' = c.name
        FROM sysdtspackages p INNER JOIN sysdtscategories c ON p.categoryid = c.id
        WHERE p.categoryid = @categoryid
        AND (@name_like IS NULL OR p.name LIKE @name_like)
        AND (@description_like IS NULL OR p.description LIKE @description_like)
  END

  --// List its subcategories' packages
  DECLARE @childid UNIQUEIDENTIFIER
  DECLARE hC CURSOR LOCAL FOR SELECT id FROM sysdtscategories c WHERE parentid = @categoryid ORDER BY c.name FOR READ ONLY
  OPEN hC
  FETCH NEXT FROM hC INTO @childid
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXECUTE sp_enum_dtspackages @name_like, @description_like, @childid, @flags
    FETCH NEXT FROM hC INTO @childid
  END
  CLOSE hC
  DEALLOCATE hC
  RETURN 0
go

GRANT EXECUTE ON sp_enum_dtspackages TO PUBLIC
go

/**************************************************************/
/* SP_ADD_DTSCATEGORY                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_dtscategory...'
go
IF OBJECT_ID(N'sp_add_dtscategory') IS NOT NULL
  DROP PROCEDURE sp_add_dtscategory
go
CREATE PROCEDURE sp_add_dtscategory
  @name sysname,
  @description NVARCHAR(1024),
  @id UNIQUEIDENTIFIER,
  @parentid UNIQUEIDENTIFIER
AS
  SET NOCOUNT ON

  --// If parentid is NULL, use 'Local'
  IF @parentid IS NULL
    SELECT @parentid = 'B8C30000-A282-11d1-B7D9-00C04FB6EFD5'

  --// First do some simple validation of "non-assert" cases.  UI should validate others and the table
  --// definitions will act as an "assert", but we check here (with a nice message) for user-error stuff
  --// it would be hard for UI to validate.
  IF NOT EXISTS (SELECT * FROM sysdtscategories WHERE id = @parentid)
  BEGIN
    DECLARE @stringfromclsid NVARCHAR(200)
    SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @parentid)
    RAISERROR(14262, 16, 1, '@parentid', @stringfromclsid)
    RETURN(1) -- Failure
  END

  IF EXISTS (SELECT * FROM sysdtscategories WHERE name = @name AND parentid = @parentid)
  BEGIN
    RAISERROR(14591, 16, -1, @name)
    RETURN(1) -- Failure
  END

  --// id uniqueness is ensured by the primary key.
  INSERT sysdtscategories (
    name,
    description,
    id,
    parentid
  ) VALUES (
    @name,
    @description,
    @id,
    @parentid
  )
  RETURN 0    -- SUCCESS
go

/**************************************************************/
/* SP_DROP_DTSCATEGORY                                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_drop_dtscategory...'
go
IF OBJECT_ID(N'sp_drop_dtscategory') IS NOT NULL
  DROP PROCEDURE sp_drop_dtscategory
go
CREATE PROCEDURE sp_drop_dtscategory
  @name_like sysname,
  @id UNIQUEIDENTIFIER = NULL,
  @flags INT = 0           --// Bitmask:  0x01 == recursive (drop all subcategories and packages)
AS
  SET NOCOUNT ON

  --// Temp table in case recursion is needed.
  CREATE TABLE #recurse(id UNIQUEIDENTIFIER, passcount INT DEFAULT(0))

  IF (@name_like IS NOT NULL)
  BEGIN
    INSERT #recurse (id) SELECT id FROM sysdtscategories WHERE name LIKE @name_like
    IF @@rowcount = 0
    BEGIN
      RAISERROR(14262, 16, 1, '@name_like', @name_like)
      RETURN(1) -- Failure
    END
    IF @@rowcount > 1
    BEGIN
      RAISERROR(14592, 16, -1, @name_like)
      RETURN(1) -- Failure
    END
    SELECT @name_like = name, @id = id FROM sysdtscategories WHERE name LIKE @name_like
  END ELSE BEGIN
    --// Verify the id.  @name_like will be NULL if we're here so no need to initialize.
    SELECT @name_like = name FROM sysdtscategories WHERE id = @id
    IF @name_like IS NULL
    BEGIN
      DECLARE @stringfromclsid NVARCHAR(200)
      SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @id)
      RAISERROR(14262, 16, 1, '@id', @stringfromclsid)
      RETURN(1) -- Failure
    END
    INSERT #recurse (id) VALUES (@id)
  END

  --// We now have a unique category.

  --// Cannot drop the predefined categories (or the root, which already failed above as IID_NULL
  --// is not an id in sysdtscategories).  These will be at top level.
  IF @id IN (
    'B8C30000-A282-11d1-B7D9-00C04FB6EFD5'
    , 'B8C30001-A282-11d1-B7D9-00C04FB6EFD5'
    , 'B8C30002-A282-11d1-B7D9-00C04FB6EFD5'
  ) BEGIN
      RAISERROR(14598, 16, 1)
      RETURN(1) -- Failure
  END

  --// Check for subcategories or packages.
  IF EXISTS (SELECT * FROM sysdtspackages WHERE categoryid = @id)
             OR EXISTS (SELECT * FROM sysdtscategories WHERE parentid = @id)
  BEGIN
    --// It does.  Make sure recursion was requested.
    IF (@flags & 0x01 = 0)
    BEGIN
      RAISERROR(14593, 16, -1, @name_like)
      RETURN(1) -- Failure
    END

    --// Fill up #recurse.
    UPDATE #recurse SET passcount = 0
    WHILE (1 = 1)
    BEGIN
      UPDATE #recurse SET passcount = passcount + 1
      INSERT #recurse (id, passcount)
        SELECT c.id, 0 FROM sysdtscategories c INNER JOIN #recurse r ON c.parentid = r.id
        WHERE passcount = 1
      IF @@rowcount = 0
        BREAK
    END
  END

  DELETE sysdtspackages FROM sysdtspackages INNER JOIN #recurse ON sysdtspackages.categoryid = #recurse.id
  DELETE sysdtscategories FROM sysdtscategories INNER JOIN #recurse ON sysdtscategories.id = #recurse.id
  RETURN(0) -- SUCCESS
go

/**************************************************************/
/* SP_MODIFY_DTSCATEGORY                                      */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_modify_dtscategory...'
go
IF OBJECT_ID(N'sp_modify_dtscategory') IS NOT NULL
  DROP PROCEDURE sp_modify_dtscategory
go
CREATE PROCEDURE sp_modify_dtscategory
  @id UNIQUEIDENTIFIER,
  @name sysname,
  @description NVARCHAR(1024),
  @parentid UNIQUEIDENTIFIER
AS
  SET NOCOUNT ON

  --// Validate.
  DECLARE @stringfromclsid NVARCHAR(200)
  IF NOT EXISTS (SELECT * FROM sysdtscategories WHERE id = @id)
  BEGIN
    SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @id)
    RAISERROR(14262, 16, 1, '@id', @stringfromclsid)
    RETURN(1) -- Failure
  END

  IF NOT EXISTS (SELECT * FROM sysdtscategories WHERE id = @parentid)
  BEGIN
    SELECT @stringfromclsid = CONVERT(NVARCHAR(50), @parentid)
    RAISERROR(14262, 16, 1, '@parentid', @stringfromclsid)
    RETURN(1) -- Failure
  END

  --// Check the name uniqueness within parent, but make sure the id is different (we may just be renaming
  --// without reassigning parentage).
  IF EXISTS (SELECT * FROM sysdtscategories WHERE name = @name AND parentid = @parentid and id <> @id)
  BEGIN
    RAISERROR(14591, 16, -1, @name)
    RETURN(1) -- Failure
  END

  UPDATE sysdtscategories SET name = @name, description = @description, parentid = @parentid
    WHERE id = @id
  RETURN(0) -- SUCCESS
go

/**************************************************************/
/* SP_ENUM_DTSCATEGORIES                                      */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_enum_dtscategories...'
go
IF OBJECT_ID(N'sp_enum_dtscategories') IS NOT NULL
  DROP PROCEDURE sp_enum_dtscategories
go
CREATE PROCEDURE sp_enum_dtscategories
  @parentid UNIQUEIDENTIFIER = NULL,
  @flags INT = 0           --// Bitmask:  0x01 == recursive (enum all subcategories; names only)
AS
  IF (@flags & 0x01) <> 0
    GOTO DO_RECURSE

  --// Go to the root if no parentid specified
  IF @parentid IS NULL
    SELECT @parentid = '00000000-0000-0000-0000-000000000000'

  --// 'No results' is valid here.
  SELECT name, description, id FROM sysdtscategories WHERE parentid = @parentid
    ORDER BY name
  RETURN 0

  DO_RECURSE:

  --// Identify the category.
  IF @@nestlevel <> 0
    SELECT 'Level' = @@nestlevel, name FROM sysdtscategories WHERE id = @parentid

  --// List its subcategories
  DECLARE @childid UNIQUEIDENTIFIER
  DECLARE hC CURSOR LOCAL FOR SELECT id FROM sysdtscategories c WHERE parentid = @parentid ORDER BY c.name FOR READ ONLY
  OPEN hC
  FETCH NEXT FROM hC INTO @childid
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXECUTE sp_enum_dtscategories @childid, @flags
    FETCH NEXT FROM hC INTO @childid
  END
  CLOSE hC
  DEALLOCATE hC
  RETURN 0
go

/**************************************************************/
/*                                                            */
/*  D  B    M  A  I  N  T  E  N  A  N  C  E    P  L  A  N  S  */
/*                                                            */
/**************************************************************/

/**************************************************************/
/* SYSDBMAINTPLANS                                            */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdbmaintplans')
                  AND (type = 'U')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdbmaintplans...'

  CREATE TABLE sysdbmaintplans
  (
  plan_id                    UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
  plan_name                  sysname          NOT NULL,
  date_created               DATETIME         NOT NULL DEFAULT (GETDATE()),
  owner                      sysname          NOT NULL DEFAULT (ISNULL(NT_CLIENT(), SUSER_SNAME())),
  max_history_rows           INT              NOT NULL DEFAULT (0),
  remote_history_server      sysname          NOT NULL DEFAULT (''),
  max_remote_history_rows    INT              NOT NULL DEFAULT (0),
  user_defined_1             INT              NULL,
  user_defined_2             NVARCHAR(100)    NULL,
  user_defined_3             DATETIME         NULL,
  user_defined_4             UNIQUEIDENTIFIER NULL
  )
END
go

-- Add row for "plan 0"
IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysdbmaintplans
                WHERE (plan_id = CONVERT(UNIQUEIDENTIFIER, 0x00))))
  INSERT INTO sysdbmaintplans(plan_id, plan_name) VALUES (0x00, N'All ad-hoc plans')
go

/**************************************************************/
/* SYSDBMAINTPLAN_JOBS                                        */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdbmaintplan_jobs')
                  AND (type = 'U')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdbmaintplan_jobs...'

  CREATE TABLE sysdbmaintplan_jobs
  (
  plan_id UNIQUEIDENTIFIER NOT NULL UNIQUE CLUSTERED (plan_id, job_id)
                                    FOREIGN KEY REFERENCES msdb.dbo.sysdbmaintplans (plan_id),
  job_id  UNIQUEIDENTIFIER NOT NULL
  )
END
go

/**************************************************************/
/* SYSDBMAINTPLAN_DATABASES                                   */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdbmaintplan_databases')
                  AND (type = 'U')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdbmaintplan_databases...'

  CREATE TABLE sysdbmaintplan_databases
  (
  plan_id       UNIQUEIDENTIFIER NOT NULL UNIQUE CLUSTERED (plan_id, database_name) 
                                          FOREIGN KEY REFERENCES msdb.dbo.sysdbmaintplans (plan_id),
  database_name sysname          NOT NULL
  )
END
go

/**************************************************************/
/* SYSDBMAINTPLAN_HISTORY                                     */
/**************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysdbmaintplan_history')
                  AND (type = 'U')))
BEGIN
  PRINT ''
  PRINT 'Creating table sysdbmaintplan_history...'

  CREATE TABLE sysdbmaintplan_history
  (
  sequence_id    INT               NOT NULL IDENTITY UNIQUE NONCLUSTERED,
  plan_id        UNIQUEIDENTIFIER  NOT NULL DEFAULT('00000000-0000-0000-0000-000000000000'),
  plan_name      sysname           NOT NULL DEFAULT('All ad-hoc plans'),
  database_name  sysname           NULL,
  server_name    sysname           NOT NULL DEFAULT (@@servername),
  activity       NVARCHAR(128)     NULL,
  succeeded      BIT               NOT NULL DEFAULT (1),
  end_time       DATETIME          NOT NULL DEFAULT (GETDATE()),
  duration       INT               NULL     DEFAULT (0),
  start_time     AS                DATEADD (ss, -duration, end_time),
  error_number   INT               NOT NULL DEFAULT (0),
  message        NVARCHAR(512)     NULL
  )

  CREATE CLUSTERED INDEX clust ON sysdbmaintplan_history(plan_id)
END
go

/**************************************************************/
/*                                                            */
/* B A C K U P  H I S T O R Y                                 */
/*                                                            */
/**************************************************************/

/**************************************************************/
/* SP_DELETE_BACKUPHISTORY                                    */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_delete_backuphistory...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_backuphistory')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_backuphistory
go
CREATE   PROCEDURE sp_delete_backuphistory
  @oldest_date datetime
AS
BEGIN
  declare @bsid int
  declare @msid int
  declare @rows int
  declare @errorflag int
  declare @str nvarchar(64)

  set nocount on
  set @errorflag = 0
  declare oldbackups insensitive cursor for
    select backup_set_id from backupset where backup_finish_date < @oldest_date
    for read only
  open oldbackups
  fetch next from oldbackups into @bsid
  while(@@fetch_status = 0)
  begin
    begin transaction
    set rowcount 1
    set @rows = (select count(*) from restorehistory where backup_set_id = @bsid)
    set rowcount 0
    if (@rows > 0)
    begin
      delete from restorefile where restore_history_id in (select restore_history_id from restorehistory where backup_set_id = @bsid)
      if (@@error <> 0)
      begin
         rollback transaction 
         set @errorflag = 1
         break
      end
      delete from restorefilegroup where restore_history_id in (select restore_history_id from restorehistory where backup_set_id = @bsid)
      if (@@error <> 0)
      begin
         rollback transaction 
         set @errorflag = 1
         break
      end
      delete from restorehistory where backup_set_id = @bsid
      if (@@error <> 0)
      begin
         rollback transaction 
         set @errorflag = 1
         break
      end
    end 
    delete from backupfile where backup_set_id = @bsid
    if (@@error <> 0)
    begin
       rollback transaction 
       set @errorflag = 1
       break
    end
    set @msid = (select media_set_id from backupset where backup_set_id = @bsid)
    delete from backupset where backup_set_id = @bsid
    if (@@error <> 0)
    begin
       rollback transaction 
       set @errorflag = 1
       break
    end
    set rowcount 1
    set @rows = (select count(*) from backupset where media_set_id = @msid)
    set rowcount 0
    if (@rows = 0)
    begin
      delete from backupmediafamily where media_set_id = @msid
      if (@@error <> 0)
      begin
         rollback transaction 
         set @errorflag = 1
         break
      end
      delete from backupmediaset where media_set_id = @msid
      if (@@error <> 0)
      begin
         rollback transaction 
         set @errorflag = 1
         break
      end
    end
    commit transaction
    fetch next from oldbackups into @bsid
  end
  deallocate oldbackups
  set nocount off

  if (@errorflag <> 0)
  begin
    set @str = (select convert( nvarchar(64), @bsid))
    raiserror( 4325, -1, -1, @str )
    return(1)
  end

  set @str = (select convert( nvarchar(64), @oldest_date))
  raiserror( 4324, -1, -1, @str  )
  return(0)

END
go

/**************************************************************/
/* Turn 'System Object' marking OFF                           */
/**************************************************************/
PRINT ''
EXECUTE master.dbo.sp_MS_upd_sysobj_category 2
go

EXECUTE master.dbo.sp_configure N'allow updates', 0
go
RECONFIGURE WITH OVERRIDE
go

PRINT ''
PRINT '----------------------------------'
PRINT 'Execution of INSTMSDB.SQL complete'
PRINT '----------------------------------'
go

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go
