/**********************************************************************/
/* MSDB6TO7.SQL                                                       */
/*                                                                    */
/* Upgrades 6.x task data to 7.0 job data and drops all obsolete 6.x  */
/* objects related to Tasks, Operators and Alerts.                    */
/* This script should be run after 7.0 INSTMSDB.SQL and once it has   */
/* completed successfully it cannot be run again.                     */
/*                                                                    */
/*
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/
/**********************************************************************/

PRINT '----------------------------------'
PRINT 'Starting execution of MSDB6TO7.SQL'
PRINT '----------------------------------'
go

/**************************************************************/
/*                                                            */
/*      U  P  G  R  A  D  E     F  R  O  M     6  .  X        */
/*                                                            */
/**************************************************************/

USE msdb
go

DUMP TRANSACTION msdb WITH NO_LOG
go
CHECKPOINT
go

-- Check that INSTMSDB.SQL has run
IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysjobs')))
  RAISERROR('INSTMSDB.SQL has not been run.  The script cannot complete successfully.', 20, 127) WITH LOG
go

-- Check that XPSTAR.SQL has run
IF (NOT EXISTS (SELECT *
                FROM master.dbo.sysobjects
                WHERE (name = N'xp_sqlagent_notify')))
  RAISERROR('XPSTAR.SQL has not been run.  The script cannot complete successfully.', 20, 127) WITH LOG
go

-- Check that we have not already been run (successfully)
DECLARE @already_upgraded INT
SET NOCOUNT ON
SELECT @already_upgraded = 0
EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                              'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                              'UpgradedFrom6X',
                               @already_upgraded OUTPUT,
                              'no_output'
IF (@already_upgraded > 0)
  RAISERROR('MSDB6TO7.SQL has already been successfully run so cannot be run again.', 20, 127) WITH LOG
go

-- Allow updates to system catalogs so that we can fully manipulate our system objects
EXECUTE master.dbo.sp_configure N'allow updates', 1
go
RECONFIGURE WITH OVERRIDE
go

/**************************************************************/
/* Turn 'System Object' marking ON                            */
/**************************************************************/
PRINT ''
EXECUTE master.dbo.sp_MS_upd_sysobj_category 1
go

/**************************************************************/
/* Delete the old 6.5 backup/restore tables.                  */
/**************************************************************/

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysbackupdetail')
              AND (type = 'U')))
  DROP TABLE msdb.dbo.sysbackupdetail

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysbackuphistory')
              AND (type = 'U')))
  DROP TABLE msdb.dbo.sysbackuphistory

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysrestoredetail')
              AND (type = 'U')))
  DROP TABLE msdb.dbo.sysrestoredetail

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysrestorehistory')
              AND (type = 'U')))
  DROP TABLE msdb.dbo.sysrestorehistory

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysvolumelabel')
              AND (type = 'U')))
  DROP TABLE msdb.dbo.sysvolumelabel

IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysservermessages')
              AND (type = 'U')))
  DROP TABLE msdb.dbo.sysservermessages
go

/************************************************************************/
/* Report number of rows in the tables (to aid problem determination).  */
/************************************************************************/

DECLARE @num_alerts    INT
DECLARE @num_operators INT
DECLARE @num_tasks     INT
SELECT @num_alerts    = COUNT(*) FROM msdb.dbo.sysalerts
SELECT @num_operators = COUNT(*) FROM msdb.dbo.sysoperators
SELECT @num_tasks     = COUNT(*) FROM msdb.dbo.systasks
PRINT ''
PRINT 'Row counts before upgrade:'
RAISERROR('Number of Alerts   : %ld', 0, 100, @num_alerts)
RAISERROR('Number of Operators: %ld', 0, 101, @num_operators)
RAISERROR('Number of Tasks    : %ld', 0, 102, @num_tasks)
go

/************************************************************************/
/* Sysalerts upgrade - PART I - Bring 6.x Alert data into 7.0 table.    */
/************************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.syscolumns
                WHERE (name = N'category_id')
                  AND (id = OBJECT_ID(N'sysalerts'))))
BEGIN
  PRINT ''
  PRINT 'Upgrading sysalerts definition...'

  -- The 'flags' column was added for 6.5 (ie. it wan't part of 6.0)
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.syscolumns
                  WHERE (id = OBJECT_ID(N'sysalerts'))
                    AND (name = N'flags')))
  BEGIN
    ALTER TABLE msdb.dbo.sysalerts ADD flags INT NULL
  END

  CREATE TABLE sysalerts_temp
  (
  id                        INT IDENTITY     NOT NULL,
  name                      sysname          NOT NULL, -- Was length 60 in 6.x
  event_source              NVARCHAR(100)    NOT NULL,
  event_category_id         INT              NULL,
  event_id                  INT              NULL,
  message_id                INT              NOT NULL, -- Was NULL in 6.x
  severity                  INT              NOT NULL, -- Was NULL in 6.x
  enabled                   TINYINT          NOT NULL,
  delay_between_responses   INT              NOT NULL,
  last_occurrence_date      INT              NOT NULL, -- Was NULL in 6.x
  last_occurrence_time      INT              NOT NULL, -- Was NULL in 6.x
  last_response_date        INT              NOT NULL, -- Was NULL in 6.x
  last_response_time        INT              NOT NULL, -- Was NULL in 6.x
  notification_message      NVARCHAR(512)    NULL,
  include_event_description TINYINT          NOT NULL,
  database_name             sysname          NULL,
  event_description_keyword NVARCHAR(100)    NULL,
  occurrence_count          INT              NOT NULL,
  count_reset_date          INT              NOT NULL, -- Was NULL in 6.x
  count_reset_time          INT              NOT NULL, -- Was NULL in 6.x
  job_id                    UNIQUEIDENTIFIER NOT NULL, -- Was NULL in 6.x
  has_notification          INT              NOT NULL, -- New for 7.0
  flags                     INT              NOT NULL, -- Was NULL in 6.x
  performance_condition     NVARCHAR(512)    NULL,
  category_id               INT              NOT NULL  -- New for 7.0
  )

  EXECUTE sp_dboption N'msdb', N'select into', TRUE

  BEGIN TRANSACTION

  SET IDENTITY_INSERT msdb.dbo.sysalerts_temp ON

  INSERT INTO msdb.dbo.sysalerts_temp
        (id,
         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)
  SELECT id,
         name,
         event_source,
         NULL,  -- event_category_id (was VARCHAR(30) in 6.x)
         NULL,  -- event_id          (was VARCHAR(20) in 6.x)
         ISNULL(message_id, 0),
         ISNULL(severity, 0),
         enabled,
         delay_between_notifications, -- Renamed to delay_between_responses in 7.0
         ISNULL(last_occurrence_date, 0),
         ISNULL(last_occurrence_time, 0),
         ISNULL(last_response_date, 0),
         ISNULL(last_response_time, 0),
         notification_message,
         include_event_description,
         database_name,
         event_description_keyword,
         occurrence_count,
         ISNULL(count_reset_date, 0),
         ISNULL(count_reset_time, 0),
         job_id = CONVERT(UNIQUEIDENTIFIER, CONVERT(BINARY(16), ISNULL(task_id, 0))),
         (1 * CASE has_email_notification
                WHEN 0 THEN 0
                ELSE 1
              END) + 
         (2 * CASE has_pager_notification
                WHEN 0 THEN 0
                ELSE 1
              END),
         ISNULL(flags, 0),
         NULL, -- performance_condition (new for 7.0)
         98    -- category_id (new for 7.0) NOTE: 98 is '[Uncategorized]' for alerts
  FROM msdb.dbo.sysalerts

  SET IDENTITY_INSERT msdb.dbo.sysalerts_temp OFF

  DROP TABLE msdb.dbo.sysalerts

  IF ((SELECT status & 0x80000000 from msdb.dbo.sysobjects WHERE name = 'sysalerts_temp') <> 0)
    UPDATE msdb.dbo.sysobjects SET status = status ^ 0x80000000 WHERE name = 'sysalerts_temp'
  EXECUTE sp_rename 'msdb.dbo.sysalerts_temp', sysalerts
  UPDATE msdb.dbo.sysobjects SET status = status | 0x80000000 WHERE name = 'sysalerts'
  PRINT ''

  CREATE UNIQUE CLUSTERED INDEX ByName ON sysalerts(name)
  CREATE UNIQUE INDEX ByID ON sysalerts(id)

  COMMIT TRANSACTION

  EXECUTE sp_dboption N'msdb', N'select into', FALSE
END
go

/*******************************************************************/
/* Sysalerts upgrade - PART II - Delete any alerts that reference  */
/* obsolete error numbers (several sysmessages rows have been      */
/* deleted in 7.0) or obsolete severities (110 - 140).             */
/*******************************************************************/

DECLARE @message_id INT
DECLARE @severity   INT
DECLARE @name       sysname
DECLARE all_alerts  CURSOR LOCAL FOR SELECT message_id, severity, name FROM msdb.dbo.sysalerts

OPEN all_alerts
FETCH NEXT FROM all_alerts INTO @message_id, @severity, @name

WHILE (@@fetch_status = 0)
BEGIN
  IF (@message_id > 0) AND
     (@message_id < 50000) AND
     (NOT EXISTS (SELECT * 
                  FROM master.dbo.sysmessages 
                  WHERE (error = @message_id)))
  BEGIN
    RAISERROR('Dropping 6.x alert ''%s'' which is defined on obsolete message #%ld...', 0, 103, @name, @message_id)
    EXECUTE msdb.dbo.sp_delete_alert @name = @name
  END
  IF (@severity < 0) OR (@severity > 25)
  BEGIN
    RAISERROR('Dropping 6.x alert ''%s'' which is defined on obsolete severity %ld...', 0, 104, @name, @severity)
    EXECUTE msdb.dbo.sp_delete_alert @name = @name
  END
  FETCH NEXT FROM all_alerts INTO @message_id, @severity, @name
END

DEALLOCATE all_alerts
go

/************************************************************************/
/* Sysoperators upgrade - Bring 6.x Operator data into 7.0 table.       */
/************************************************************************/

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.syscolumns
                WHERE (name = N'category_id')
                  AND (id = OBJECT_ID(N'sysoperators'))))
BEGIN
  PRINT ''
  PRINT 'Upgrading sysoperators definition...'

  CREATE TABLE sysoperators_temp
  (
  id                        INT IDENTITY  NOT NULL,
  name                      sysname       NOT NULL, -- Was length 50 in 6.x
  enabled                   TINYINT       NOT NULL,
  email_address             NVARCHAR(100) NULL,
  last_email_date           INT           NOT NULL, -- Was NULL in 6.x
  last_email_time           INT           NOT NULL, -- Was NULL in 6.x
  pager_address             NVARCHAR(100) NULL,
  last_pager_date           INT           NOT NULL, -- Was NULL in 6.x
  last_pager_time           INT           NOT NULL, -- Was NULL in 6.x
  weekday_pager_start_time  INT           NOT NULL,
  weekday_pager_end_time    INT           NOT NULL,
  saturday_pager_start_time INT           NOT NULL,
  saturday_pager_end_time   INT           NOT NULL,
  sunday_pager_start_time   INT           NOT NULL,
  sunday_pager_end_time     INT           NOT NULL,
  pager_days                TINYINT       NOT NULL,
  netsend_address           NVARCHAR(100) NULL,     -- New for 7.0
  last_netsend_date         INT           NOT NULL, -- New for 7.0
  last_netsend_time         INT           NOT NULL, -- New for 7.0
  category_id               INT           NOT NULL  -- New for 7.0
  )

  EXECUTE sp_dboption N'msdb', N'select into', TRUE

  BEGIN TRANSACTION

  SET IDENTITY_INSERT msdb.dbo.sysoperators_temp ON

  INSERT INTO msdb.dbo.sysoperators_temp
        (id,
         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)
  SELECT id,
         name,
         enabled,
         email_address,
         ISNULL(last_email_date, 0),
         ISNULL(last_email_time, 0),
         pager_address,
         ISNULL(last_pager_date, 0),
         ISNULL(last_pager_time, 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,
         NULL, -- netsend_address (new for 7.0)
         0,    -- last_netsend_date (new for 7.0)
         0,    -- last_netsend_time (new for 7.0)
         99    -- category_id (new for 7.0) NOTE: 99 is '[Uncategorized]' for operators
  FROM msdb.dbo.sysoperators

  SET IDENTITY_INSERT msdb.dbo.sysoperators_temp OFF

  DROP TABLE msdb.dbo.sysoperators

  IF ((SELECT status & 0x80000000 from msdb.dbo.sysobjects WHERE name = 'sysoperators_temp') <> 0)
    UPDATE msdb.dbo.sysobjects SET status = status ^ 0x80000000 WHERE name = 'sysoperators_temp'
  EXECUTE sp_rename 'msdb.dbo.sysoperators_temp', sysoperators
  UPDATE msdb.dbo.sysobjects SET status = status | 0x80000000 WHERE name = 'sysoperators'
  PRINT ''

  CREATE UNIQUE CLUSTERED INDEX ByName ON sysoperators(name)
  CREATE UNIQUE INDEX ByID ON sysoperators(id)

  COMMIT TRANSACTION

  EXECUTE sp_dboption N'msdb', N'select into', FALSE
END
go

/**************************************************************/
/* Migrate registry keys for fail-safe operator.              */
/**************************************************************/

DECLARE @root_key   NVARCHAR(200)
DECLARE @char_value NVARCHAR(200)
DECLARE @int_value  INT

SET NOCOUNT ON

CREATE TABLE #temp (key_exists INT NOT NULL)

INSERT INTO #temp
EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServ65\SQLExecutive'
IF (EXISTS (SELECT * FROM #temp WHERE (key_exists = 1)))
  SELECT @root_key = N'SOFTWARE\Microsoft\MSSQLServ65\SQLExecutive'
ELSE
BEGIN
  DELETE FROM #temp
  INSERT INTO #temp
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServ60\SQLExecutive'
  IF (EXISTS (SELECT * FROM #temp WHERE (key_exists = 1)))
    SELECT @root_key = N'SOFTWARE\Microsoft\MSSQLServ60\SQLExecutive'
END

IF (@root_key IS NOT NULL)
BEGIN
  -- Copy AlertFailSafeOperator 
  SELECT @char_value = NULL
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                @root_key,
                                N'AlertFailSafeOperator',
                                @char_value OUTPUT,
                                N'no_output'
  IF (@char_value IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'AlertFailSafeOperator',
                                   N'REG_SZ',
                                   @char_value
  -- Copy AlertFailSafeEmailAddress
  SELECT @char_value = NULL
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                @root_key,
                                N'AlertFailSafeEmailAddress',
                                @char_value OUTPUT,
                                N'no_output'
  IF (@char_value IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'AlertFailSafeEmailAddress',
                                   N'REG_SZ',
                                   @char_value
  -- Copy AlertFailSafePagerAddress
  SELECT @char_value = NULL
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                @root_key,
                                N'AlertFailSafePagerAddress',
                                @char_value OUTPUT,
                                N'no_output'
  IF (@char_value IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'AlertFailSafePagerAddress',
                                   N'REG_SZ',
                                   @char_value
  -- Copy AlertNotificationMethod
  SELECT @int_value = NULL
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                @root_key,
                                N'AlertNotificationMethod',
                                @int_value OUTPUT,
                                N'no_output'
  IF (@int_value IS NOT NULL)
    EXECUTE master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE',
                                   N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                   N'AlertNotificationMethod',
                                   N'REG_DWORD',
                                   @int_value

  -- Upgrade the MailAutoStart value
  PRINT ''
  PRINT 'Upgrading SQLMail auto-start setting...'

  SELECT @int_value = NULL
  EXECUTE master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                                @root_key,
                                N'MailAutoStart',
                                @int_value OUTPUT,
                                N'no_output'
  IF (@int_value IS NOT NULL)
  BEGIN
    IF (@int_value = 1)
      EXECUTE master.dbo.sp_procoption 'master.dbo.xp_startmail', 'startup', 'true'
    IF (@int_value = 0)
      EXECUTE master.dbo.sp_procoption 'master.dbo.xp_startmail', 'startup', 'false'
  END
END

DROP TABLE #temp
go

/**************************************************************/
/* Systasks Upgrade - In preparation for conversion to 7.0    */
/*                    Job schema                              */
/**************************************************************/
/**************************************************************/
/* Systasks.subsystem upgrade - In 7.0 the replication 'Sync' */
/*                              subsystem is renamed as       */
/*                             'Snapshot')                    */
/**************************************************************/

PRINT ''
PRINT 'Upgrading systasks.subsystem...'
go

SET NOCOUNT ON

DECLARE @row_count INT

UPDATE msdb.dbo.systasks
SET subsystem = 'Snapshot' 
WHERE (LOWER(subsystem) = 'sync')

SELECT @row_count = @@rowcount
IF (@row_count > 0)
  RAISERROR('%ld ''%s'' tasks were upgraded.', 0, 105, @row_count, 'Sync')
go

/**************************************************************/
/* Systasks.activestartdate upgrade (since activestartdate    */
/* was not restricted, but it is in 7.0).                     */
/**************************************************************/

IF (EXISTS (SELECT * 
            FROM msdb.dbo.systasks 
            WHERE (activestartdate < 19900101)))
BEGIN
  PRINT ''
  PRINT 'Upgrading systasks.activestartdate...'

  DECLARE @row_count       INT
  DECLARE @total_row_count INT

  SELECT @total_row_count = COUNT(*)
  FROM msdb.dbo.systasks

  UPDATE msdb.dbo.systasks
  SET activestartdate = 19900101
  WHERE (activestartdate < 19900101)

  SELECT @row_count = @@rowcount
  RAISERROR('%ld (of %ld) tasks were upgraded.', 0, 107, @row_count, @total_row_count)
END
go

/**************************************************************/
/* Systasks.username/databasename upgrade (TSQL and CmdExec   */
/* tasks only) - CmdExec tasks should not have a username or  */
/*               databasename.                                */
/*             - TSQL tasks need a valid username and         */
/*               database name.                               */
/**************************************************************/

PRINT ''
PRINT 'Upgrading systasks.username and systasks.databasename...'
go

SET NOCOUNT ON

DECLARE @row_count INT

-- CmdExec tasks should not have a username or databasename
UPDATE msdb.dbo.systasks
SET username = NULL, 
    databasename = NULL
WHERE (subsystem = 'CmdExec')
  AND ((username IS NOT NULL) OR (databasename IS NOT NULL))

SELECT @row_count = @@rowcount
IF (@row_count > 0)
  RAISERROR('%ld ''%s'' tasks were upgraded.', 0, 108, @row_count, 'CmdExec')
go

-- If the TSQL jobstep databases doesn't exist we allow it since the user may be doing an
-- incremental upgrade (eg. MSDB first, then user databases).
-- TSQL jobsteps need to have NULL database user names (for non-SA-owned jobs) since
-- SQLServerAgent will determine the user context at runtime.
-- For SA-owned task we preserve any existing database user name, even if it's invalid
-- since the user may be doing an incremental upgrade (eg. MSDB first, then user databases).

DECLARE systasks_upgrade CURSOR
FOR SELECT id, 
           ownerloginid, 
           databasename, 
           username
    FROM msdb.dbo.systasks
    WHERE (subsystem = 'TSQL')
FOR UPDATE OF databasename,
              username

CREATE TABLE #username (user_name VARCHAR(30) NULL)
go

SET NOCOUNT ON

DECLARE @id             INT
DECLARE @ownerloginid   SMALLINT
DECLARE @databasename   VARCHAR(30)
DECLARE @username       VARCHAR(30)

OPEN systasks_upgrade

FETCH NEXT
FROM systasks_upgrade 
INTO @id, 
     @ownerloginid, 
     @databasename, 
     @username
WHILE (@@fetch_status = 0)
BEGIN
  TRUNCATE TABLE #username

  -- Print a warning if the task's target database doesn't exists.
  IF (NOT EXISTS (SELECT * 
                  FROM master.dbo.sysdatabases 
                  WHERE (name = ISNULL(@databasename, ''))))
  BEGIN
    SELECT @databasename = ISNULL(@databasename, '(null)')
    RAISERROR('Warning: The database (''%s'') for ''TSQL'' task %ld does not exist.', 0, 109, @databasename, @id)
  END

  -- All non-SA-owned jobs should have a NULL database user name.
  IF ((@ownerloginid <> 1) AND (@username IS NOT NULL))
  BEGIN
    UPDATE msdb.dbo.systasks 
    SET username = NULL
    WHERE (id = @id)
    RAISERROR('Updating username for ''TSQL'' task %ld from %s to NULL.', 0, 110, @id, @username)
    SELECT @username = NULL -- So that we skip the validation below
  END

  -- Print a warning if the username [for an SA owned task] is invalid.
  IF (@username IS NOT NULL)
  BEGIN
    -- Data cleanup...
    IF ((UPPER(@username) = 'SA') OR (UPPER(@username) = 'DBO'))
    BEGIN
      UPDATE msdb.dbo.systasks 
      SET username = NULL
      WHERE (id = @id)
      RAISERROR('Updating username for ''TSQL'' task %ld from %s to NULL.', 0, 111, @id, @username)
    END

    -- Report a warning if the user-name no longer exists 
    IF (EXISTS (SELECT * 
                FROM master.dbo.sysdatabases 
                WHERE (name = ISNULL(@databasename, ''))))
    BEGIN 
      EXECUTE ('INSERT INTO #username(user_name) 
                SELECT name 
                FROM ' + @databasename + '.dbo.sysusers 
                WHERE (name = '''+ @username + ''')')
      IF (NOT EXISTS (SELECT * FROM #username))
      BEGIN
        UPDATE msdb.dbo.systasks
        SET username = NULL
        WHERE (id = @id)
        RAISERROR('Warning: The username (''%s''), for ''TSQL'' task %ld does not exists and has been changed to NULL.', 0, 112, @username, @id)
        SELECT @username = NULL
      END
      DELETE FROM #username
    END
  END

  FETCH NEXT
  FROM systasks_upgrade 
  INTO @id, 
       @ownerloginid, 
       @databasename, 
       @username
END
go

DROP TABLE #username
DEALLOCATE systasks_upgrade
go

/**************************************************************/
/* Upgrade SYSTASKS to sysjobs, sysjobsteps, sysjobschedules  */
/* and sysjobservers.                                         */
/**************************************************************/

PRINT ''
PRINT 'Upgrading systasks table...'
go

SET NOCOUNT ON

BEGIN TRANSACTION 

  DELETE FROM msdb.dbo.sysjobhistory
  DELETE FROM msdb.dbo.sysjobs
  DELETE FROM msdb.dbo.sysjobsteps
  DELETE FROM msdb.dbo.sysjobschedules
  DELETE FROM msdb.dbo.sysjobservers
  DELETE FROM msdb.dbo.systaskids

  DECLARE @job_id        UNIQUEIDENTIFIER
  DECLARE @schedule_name sysname

  INSERT INTO msdb.dbo.sysjobs
  SELECT CONVERT(UNIQUEIDENTIFIER, CONVERT(BINARY(16), id)),
         UPPER(CONVERT(NVARCHAR(30), SERVERPROPERTY('ServerName'))), -- originating_server
         st.name,
         enabled,
         LTRIM(RTRIM(description)) + SPACE(CONVERT(BIT, PATINDEX('%[^ ]%', LTRIM(RTRIM(description))))) + '[Upgraded from a 6.x task.]',
         1,                            -- start_step _id
         0,                            -- category
         0x01,
         loghistcompletionlevel,
         emailcompletionlevel,
         0,                            -- notify_level_netsend
         0,                            -- notify_level_page
         ISNULL(emailoperatorid, 0),
         0,                            -- notify_netsend_operator_id
         0,                            -- notify_page_operator_id
         0,                            -- delete_level
         datecreated,
         datemodified,
         0                             -- version 
  FROM msdb.dbo.systasks                    st

  -- If we hit an error, rollback the upgrade
  IF (@@error <> 0)
  BEGIN
    ROLLBACK TRANSACTION
    GOTO Systasks_Upgrade_Error
  END

  -- Update the category_id for database maintenance tasks
  UPDATE msdb.dbo.sysjobs
  SET category_id = 3
  WHERE ((name LIKE '%Daily Automated Maintenance%') OR 
         (name LIKE '%Weekly Automated Maintenance%'))

  -- Convert job id's to true GUID's
  DECLARE upgrade_jobid_cursor CURSOR LOCAL SCROLL
  FOR SELECT job_id
      FROM msdb.dbo.sysjobs
  FOR UPDATE OF job_id
  
  OPEN upgrade_jobid_cursor
  FETCH NEXT FROM upgrade_jobid_cursor INTO @job_id
  WHILE (@@fetch_status = 0)
  BEGIN
    UPDATE msdb.dbo.sysjobs
    SET job_id = NEWID()
    WHERE (job_id = @job_id)
    FETCH NEXT FROM upgrade_jobid_cursor INTO @job_id
  END
  DEALLOCATE upgrade_jobid_cursor

  -- Populate the ID mapping table
  SET IDENTITY_INSERT msdb.dbo.systaskids ON
  INSERT INTO msdb.dbo.systaskids (job_id, task_id)
  SELECT sj.job_id, st.id
  FROM msdb.dbo.sysjobs  sj,
       msdb.dbo.systasks st
  WHERE (sj.name = st.name)
  SET IDENTITY_INSERT msdb.dbo.systaskids OFF

  -- Verify the existing schedules.  We do this by running sp_update_jobschedule on each new
  -- schedule (the update itself is a no-op, but the value verification process performed by
  -- the proc 'cleanses' the freq_x data for us).
  DECLARE all_job_schedules CURSOR LOCAL
  FOR SELECT job_id, 
             name
  FROM msdb.dbo.sysjobschedules

  OPEN all_job_schedules
  FETCH NEXT FROM all_job_schedules INTO @job_id, @schedule_name
  WHILE (@@fetch_status = 0)
  BEGIN
    EXECUTE sp_update_jobschedule @job_id = @job_id, @name = @schedule_name
    FETCH NEXT FROM all_job_schedules INTO @job_id, @schedule_name
  END
  DEALLOCATE all_job_schedules

  -- Create schedules for the tasks EXCEPT for OnDemand tasks which simply have no 
  -- schedule in 7.0
  INSERT INTO msdb.dbo.sysjobschedules
  SELECT sti.job_id,
         '6.x schedule',
         1,
         st.freqtype,
         st.freqinterval,
         st.freqsubtype,
         st.freqsubinterval,
         st.freqrelativeinterval,
         st.freqrecurrencefactor,
         st.activestartdate,
         st.activeenddate,
         st.activestarttimeofday,
         st.activeendtimeofday,
         st.nextrundate,
         st.nextruntime,       
         GETDATE()
  FROM msdb.dbo.systasks   st,
       msdb.dbo.systaskids sti
  WHERE (st.id = sti.task_id)
    AND (st.freqtype <> 0x2)

  -- Add a row for the job to sysjobservers
  INSERT INTO msdb.dbo.sysjobservers
  SELECT sti.job_id,
         0,                          -- server_id (0 means '(local)')
         CASE st.lastruncompletionlevel & 0xff -- Because the column is now a TINYINT in 7.0
           WHEN 0 THEN 0 -- Fail
           WHEN 1 THEN 1 -- Succeed
           WHEN 2 THEN 2 -- Retry
           WHEN 3 THEN 3 -- Cancel
                         -- 4 = InProgress (new for 7.0)
           ELSE 5        -- Unknown 
         END,
         NULL,                       -- last_outcome_message (we'll fix this up after we upgrade syshistory)
         st.lastrundate,
         st.lastruntime,
         0                           -- last_run_duration (we'll fix this up after we upgrade syshistory)
  FROM msdb.dbo.systasks   st,
       msdb.dbo.systaskids sti
  WHERE (st.id = sti.task_id)

  -- Make sure that tasks which have never run get a last_run_outcome of 'Unknown'
  UPDATE msdb.dbo.sysjobservers
  SET last_run_outcome = 5 -- Unknown
  WHERE (last_run_date = 0) 
    AND (last_run_time = 0)

  -- If we hit an error, rollback the upgrade
  IF (@@error <> 0)
  BEGIN
    ROLLBACK TRANSACTION
    GOTO Systasks_Upgrade_Error
  END

  -- If we hit an error, rollback the upgrade
  IF (@@error <> 0)
  BEGIN
    ROLLBACK TRANSACTION
    GOTO Systasks_Upgrade_Error
  END

  DECLARE @parameters_clause         VARCHAR(100)
  DECLARE @cmdexecsuccesscode_clause VARCHAR(100)

  -- The 'parameters' column was added for 6.5 (ie. it wan't part of 6.0)
  IF (EXISTS (SELECT *
              FROM msdb.dbo.syscolumns
              WHERE (name = N'parameters')
                AND (id = OBJECT_ID(N'systasks'))))
    SELECT @parameters_clause = 'CONVERT(NTEXT, st.parameters)'
  ELSE
    SELECT @parameters_clause = 'NULL'

  -- The 'cmdexecsuccesscode' column was added for 6.5 (ie. it wan't part of 6.0)
  IF (EXISTS (SELECT *
              FROM msdb.dbo.syscolumns
              WHERE (name = N'cmdexecsuccesscode')
                AND (id = OBJECT_ID(N'systasks'))))
    SELECT @cmdexecsuccesscode_clause = 'ISNULL(st.cmdexecsuccesscode, 0)'
  ELSE
    SELECT @cmdexecsuccesscode_clause = '0'

  INSERT INTO sysjobsteps
  EXECUTE ('SELECT sti.job_id,
                 ''step_id'' = 1,
                   st.name,
                   st.subsystem,
                   st.command,
                 ''flags'' = 0,                  -- 0 = normal
                 ''additional_parameters'' = '+ @parameters_clause + ',
                 ''cmdexec_success_code'' = '+ @cmdexecsuccesscode_clause + ',
                 ''on_success_action'' = 1,      -- 1 = Quit With Success
                 ''on_success_step_id'' = 0,     -- 0 = No Step
                 ''on_fail_action'' = 2,         -- 2 = Quit With Failure
                 ''on_fail_step_id'' = 0,        -- 0 = No Step
                   st.server,
                   st.databasename,
                   st.username,
                   st.retryattempts,
                   st.retrydelay,        
                   st.runpriority,
                 ''output_file_name'' = CONVERT(VARCHAR, NULL),
                   st.lastruncompletionlevel,
                   st.lastrunduration,
                   st.lastrunretries,
                   st.lastrundate,
                   st.lastruntime
            FROM msdb.dbo.systasks   st,
                 msdb.dbo.systaskids sti
            WHERE (st.id = sti.task_id)')

  -- If we hit an error, rollback the upgrade
  IF (@@error <> 0)
  BEGIN
    ROLLBACK TRANSACTION
    GOTO Systasks_Upgrade_Error
  END

  -- Clean up job schedule data
  UPDATE msdb.dbo.sysjobschedules
  SET freq_interval          = 0,
      freq_subday_type       = 0,
      freq_subday_interval   = 0,
      freq_relative_interval = 0,
      freq_recurrence_factor = 0
  WHERE (freq_type IN (0x1, 0x40))  -- One-Time and Auto-Start

/**************************************************************/
/* Sysjobsteps.command upgrade (alert-task tokens, eg. [DBN], */
/* have been changed for 7.0 to A-XXX, eg. A-DBN; Also,       */
/* autostart Logreader/Distributor tasks need a new command   */
/* line parameter, -Continuous).                              */
/* NOTE: We do this upgrade AFTER moving the tasks to the new */
/*       job tables because sysjobsteps.command may grow to   */
/*       over 255 characters as a result of the upgrade.      */
/**************************************************************/

  PRINT ''
  PRINT 'Upgrading sysjobsteps.command...'

  DECLARE @cmd    NVARCHAR(3200)
  DECLARE @offset INT

  DECLARE all_tasks CURSOR LOCAL
  FOR SELECT job_id, command
      FROM msdb.dbo.sysjobsteps
  FOR UPDATE OF command

  OPEN all_tasks
  FETCH NEXT FROM all_tasks INTO @job_id, @cmd
  WHILE (@@fetch_status = 0)
  BEGIN
    SELECT @offset = PATINDEX('%[[]DBN]%', @cmd)
    IF (@offset <> 0)
      SELECT @cmd = STUFF(@cmd, @offset, DATALENGTH('[DBN]'), '[A-DBN]')
    SELECT @offset = PATINDEX('%[[]SVR]%', @cmd)
    IF (@offset <> 0)
      SELECT @cmd = STUFF(@cmd, @offset, DATALENGTH('[SVR]'), '[A-SVR]')
    SELECT @offset = PATINDEX('%[[]ERR]%', @cmd)
    IF (@offset <> 0)
      SELECT @cmd = STUFF(@cmd, @offset, DATALENGTH('[ERR]'), '[A-ERR]')
    SELECT @offset = PATINDEX('%[[]SEV]%', @cmd)
    IF (@offset <> 0)
      SELECT @cmd = STUFF(@cmd, @offset, DATALENGTH('[SEV]'), '[A-SEV]')
    SELECT @offset = PATINDEX('%[[]MSG]%', @cmd)
    IF (@offset <> 0)
      SELECT @cmd = STUFF(@cmd, @offset, DATALENGTH('[MSG]'), '[A-MSG]')

    UPDATE msdb.dbo.sysjobsteps
    SET command = @cmd
    WHERE (job_id = @job_id)

    FETCH NEXT FROM all_tasks INTO @job_id, @cmd
  END
  DEALLOCATE all_tasks

  DECLARE all_tasks CURSOR LOCAL
  FOR SELECT sjs.job_id, sjs.command
      FROM msdb.dbo.sysjobsteps     sjs,
           msdb.dbo.sysjobschedules sjsch
  WHERE (sjs.job_id = sjsch.job_id)
    AND (sjsch.freq_type = 0x40)
    AND ((UPPER(sjs.subsystem) = 'LOGREADER') OR (UPPER(sjs.subsystem) = 'DISTRIBUTION'))
  FOR UPDATE OF command

  OPEN all_tasks
  FETCH NEXT FROM all_tasks INTO @job_id, @cmd
  WHILE (@@fetch_status = 0)
  BEGIN
    UPDATE msdb.dbo.sysjobsteps
    SET command = command + ' -Continuous'
    WHERE (job_id = @job_id)

    FETCH NEXT FROM all_tasks INTO @job_id, @cmd
  END
  DEALLOCATE all_tasks

/**************************************************************/
/* Upgrade SYSALERTS.job_id                                   */
/**************************************************************/

  UPDATE msdb.dbo.sysalerts
  SET job_id = stid.job_id
  FROM msdb.dbo.sysalerts  sa,
       msdb.dbo.systaskids stid
  WHERE (CONVERT(INT, CONVERT(BINARY(16), sa.job_id)) = stid.task_id)

  -- If we hit an error, rollback the upgrade
  IF (@@error <> 0)
  BEGIN
    ROLLBACK TRANSACTION
    GOTO Systasks_Upgrade_Error
  END

/**************************************************************/
/* Upgrade SYSHISTORY to sysjobhistory                        */
/**************************************************************/

  PRINT ''
  PRINT 'Upgrading syshistory table...'

  INSERT INTO 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)
  SELECT sti.job_id, 
         0, 
         st.name, 
         ISNULL(sh.messageid, 0), 
         ISNULL(sh.severity, 0), 
         sh.comments, 
         sh.runstatus, 
         sh.rundate, 
         sh.runtime, 
         sh.runduration, 
         ISNULL(sh.emailoperatorid, 0), 
         0,
         0,
         sh.retries,
         UPPER(CONVERT(NVARCHAR(30), SERVERPROPERTY('ServerName')))
  FROM msdb.dbo.syshistory sh,
       msdb.dbo.systasks   st,
       msdb.dbo.systaskids sti
  WHERE (sh.taskid = st.id)
    AND (st.id = sti.task_id)

  IF (@@error <> 0)
  BEGIN
    ROLLBACK TRANSACTION
    GOTO Syshistory_Upgrade_Error
  END

  -- Update the columns in sysjobserver that we couldn't get directly from systasks
  UPDATE msdb.dbo.sysjobservers
  SET last_outcome_message = LTRIM(RTRIM(sjh.message)),
      last_run_duration = sjh.run_duration
  FROM msdb.dbo.sysjobservers sjs, 
       msdb.dbo.sysjobhistory sjh
  WHERE (sjs.job_id = sjh.job_id)
    AND (sjh.instance_id = (SELECT MAX(instance_id) 
                            FROM msdb.dbo.sysjobhistory sjh2 
                            WHERE (sjh2.job_id = sjs.job_id)))

  IF (@@error <> 0)
  BEGIN
    ROLLBACK TRANSACTION
    GOTO Syshistory_Upgrade_Error
  END


/**************************************************************/
/* Upgrade MSwebtasks (if it exists)                          */
/**************************************************************/

  IF (EXISTS (SELECT * 
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'MSwebtasks')
                AND (type = 'U')))
  BEGIN
    -- [1] Add new 7.0 columns to MSwebtasks
    IF (NOT EXISTS (SELECT * 
                    FROM msdb.dbo.syscolumns 
                    WHERE (id = OBJECT_ID(N'MSwebtasks'))
                     AND (name = N'trigflags')))
    BEGIN
      ALTER TABLE msdb.dbo.MSwebtasks ADD trigflags SMALLINT NULL
    END
    IF (NOT EXISTS (SELECT * 
                    FROM msdb.dbo.syscolumns 
                    WHERE (id = OBJECT_ID(N'MSwebtasks'))
                      AND (name = N'taskid')))
    BEGIN
      ALTER TABLE msdb.dbo.MSwebtasks ADD taskid INT NULL
    END

    -- [2] Populate the MSwebtasks.taskid column
    --     NOTE: We do this as a dynamic EXEC to avoid a compile error (207, 'Invalid column name')
    EXECUTE ('UPDATE msdb.dbo.MSwebtasks
              SET taskid = st.id
              FROM msdb.dbo.MSwebtasks wt,
                   msdb.dbo.systasks   st
              WHERE (wt.procname = st.name)

              PRINT ''''
              RAISERROR(''%ld Web task(s) upgraded...'', 0, 113, @@rowcount)')

    -- [3] Rename MSwebtasks as mswebtasks
    IF ((SELECT status & 0x80000000 from msdb.dbo.sysobjects WHERE name = 'MSwebtasks') <> 0)
      UPDATE msdb.dbo.sysobjects SET status = status ^ 0x80000000 WHERE name = 'MSwebtasks'
    EXECUTE sp_rename 'MSwebtasks', 'mswebtasks', 'OBJECT'
	UPDATE msdb.dbo.sysobjects SET status = status | 0x80000000 WHERE name = 'mswebtasks'

    -- [4] Upgrade the wparams column to 7.0 format
    IF (EXISTS (SELECT * 
                FROM master.dbo.sysobjects 
                WHERE (name = N'sp_convertwebtasks')
                  AND (type = 'P')))
    BEGIN
      PRINT ''
      EXECUTE master.dbo.sp_convertwebtasks
    END
  END

/**************************************************************/
/* Drop obsolete 6.x objects                                  */
/**************************************************************/

  PRINT ''
  PRINT 'Removing obsolete 6.x obects...'

  -- Removed objects
  IF (EXISTS (SELECT * 
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'MSWork') 
                AND (type = 'U')))
    DROP TABLE msdb.dbo.MSWork

  IF (EXISTS (SELECT * 
              FROM master.dbo.sysobjects 
              WHERE (name = N'xp_enumqueuedtasks')
                AND (type = 'X')))
    EXECUTE master.dbo.sp_dropextendedproc 'xp_enumqueuedtasks'

  IF (EXISTS (SELECT * 
              FROM master.dbo.sysobjects 
              WHERE (name = N'xp_schedulersignal')
                AND (type = 'X')))
    EXECUTE master.dbo.sp_dropextendedproc 'xp_schedulersignal'

  -- Renamed objects
  IF (EXISTS (SELECT * 
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sp_schedulersignal') 
                AND (type = 'P')))
    DROP PROCEDURE sp_schedulersignal

  IF (EXISTS (SELECT * 
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_validatealert') 
               AND (type = 'P')))
    DROP PROCEDURE sp_validatealert

  IF (EXISTS (SELECT * 
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_addalert') 
                AND (type = 'P')))
    DROP PROCEDURE sp_addalert

  IF (EXISTS (SELECT * 
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sp_updatealert')
                AND (type = 'P')))
    DROP PROCEDURE sp_updatealert

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_dropalert') 
                AND (type = 'P')))
    DROP PROCEDURE sp_dropalert

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_helpalert') 
                AND (type = 'P')))
    DROP PROCEDURE sp_helpalert

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_addoperator')
                AND (type = 'P')))
    DROP PROCEDURE sp_addoperator

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_updateoperator')
                AND (type = 'P')))
    DROP PROCEDURE sp_updateoperator

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sp_dropoperator')
                AND (type = 'P')))
    DROP PROCEDURE sp_dropoperator

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sp_helpoperator')
                AND (type = 'P')))
    DROP PROCEDURE sp_helpoperator

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_addnotification') 
                AND (type = 'P')))
    DROP PROCEDURE sp_addnotification

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sp_updatenotification')
                AND (type = 'P')))
    DROP PROCEDURE sp_updatenotification

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sp_dropnotification')
                AND (type = 'P')))
    DROP PROCEDURE sp_dropnotification

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sp_helpnotification')
                AND (type = 'P')))
    DROP PROCEDURE sp_helpnotification

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects
              WHERE (name = N'sp_MSsendlargemail')
                AND (type = 'P')))
    DROP PROCEDURE sp_MSsendlargemail

  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

  -- Replaced objects
  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'systasks')
                AND (type = 'U')))
    DROP TABLE msdb.dbo.systasks

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'syshistory')
                AND (type = 'U')))
    DROP TABLE msdb.dbo.syshistory

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

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

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

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

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_verifytaskdate')
                AND (type = 'P')))
    DROP PROCEDURE sp_verifytaskdate

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_verifytasktime')
                AND (type = 'P')))
    DROP PROCEDURE sp_verifytasktime

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_verifytasksched')
                AND (type = 'P')))
    DROP PROCEDURE sp_verifytasksched

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_verifytask')
                AND (type = 'P')))
    DROP PROCEDURE sp_verifytask

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_schedulerrefresh')
                AND (type = 'P')))
    DROP PROCEDURE sp_schedulerrefresh

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_schedulerlog')
                AND (type = 'P')))
    DROP PROCEDURE sp_schedulerlog

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_MScheckforownedtasks')
                AND (type = 'P')))
    DROP PROCEDURE sp_MScheckforownedtasks

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_MStaskparameters')
                AND (type = 'P')))
    DROP PROCEDURE sp_MStaskparameters

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_runtask')
                AND (type = 'P')))
    DROP PROCEDURE sp_runtask

  IF (EXISTS (SELECT *
              FROM msdb.dbo.sysobjects 
              WHERE (name = N'sp_stoptask')
                AND (type = 'P')))
    DROP PROCEDURE sp_stoptask

  IF (EXISTS (SELECT *
              FROM master.dbo.sysobjects 
              WHERE (name = N'xp_schedulersignal')
                AND (type = 'X')))
    EXECUTE sp_dropextendedproc xp_schedulersignal

  IF (EXISTS (SELECT *
              FROM master.dbo.sysobjects 
              WHERE (name = N'xp_enumqueuedtasks')
                AND (type = 'X')))
    EXECUTE sp_dropextendedproc xp_enumqueuedtasks

  -- Obsolete SQLDMO procedures
  IF (EXISTS (SELECT *
              FROM master.dbo.sysobjects 
              WHERE (name = N'sp_MSgetexecinfo')
                AND (type = 'P')))
    EXECUTE ('USE master DROP PROCEDURE sp_MSgetexecinfo')

  IF (EXISTS (SELECT *
              FROM master.dbo.sysobjects 
              WHERE (name = N'sp_MSenumsubsystems')
                AND (type = 'P')))
    EXECUTE ('USE master DROP PROCEDURE sp_MSenumsubsystems')

/**************************************************************/
/* Undo the temporary renaming of the systasks view (which we */
/* did in PRE60TO7.SQL).                                      */
/**************************************************************/
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'systasks_temp')
              AND (type = 'V')))
  EXECUTE sp_rename systasks_temp, systasks
  UPDATE msdb.dbo.sysobjects SET status = status | 0x80000000 WHERE name = 'systasks'

/**************************************************************/
/* Set the "upgrade complete" registry flag                   */
/**************************************************************/

  EXECUTE master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                                 'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                 'UpgradedFrom6X',
                                 'REG_DWORD', 1

COMMIT TRANSACTION

/**************************************************************/
/* Drop the suid_6x_lookup work table.                        */
/**************************************************************/
IF (EXISTS (SELECT * 
            FROM msdb.dbo.sysobjects 
            WHERE (type = 'U') 
              AND (name = 'suid_6x_lookup')))
  DROP TABLE msdb.dbo.suid_6x_lookup

/************************************************************************/
/* Report number of rows in the tables (to aid problem determination).  */
/************************************************************************/

DECLARE @num_alerts    INT
DECLARE @num_operators INT
DECLARE @num_jobs      INT
SELECT @num_alerts    = COUNT(*) FROM msdb.dbo.sysalerts
SELECT @num_operators = COUNT(*) FROM msdb.dbo.sysoperators
SELECT @num_jobs      = COUNT(*) FROM msdb.dbo.sysjobs
PRINT ''
PRINT 'Row counts after upgrade:'
RAISERROR('Number of Alerts   : %ld', 0, 114, @num_alerts)
RAISERROR('Number of Operators: %ld', 0, 115, @num_operators)
RAISERROR('Number of Jobs     : %ld', 0, 116, @num_jobs)

GOTO End_Of_Upgrade

Systasks_Upgrade_Error:
RAISERROR('ERROR: 6.x systasks upgrade failed.', 0, 117)
GOTO End_Of_Upgrade

Syshistory_Upgrade_Error:
RAISERROR('ERROR: 6.x syshistory upgrade failed.', 0, 118)
GOTO End_Of_Upgrade

End_Of_Upgrade:
go

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

/**************************************************************/
/* If the log file is larger than 2MB, request it to shrink.  */
/**************************************************************/
IF ((SELECT (size * 8) / 1024.0 FROM msdb.dbo.sysfiles WHERE (name = 'MSDBLog')) > 2.0)
BEGIN
  RAISERROR('Requesting MSDBLog to shrink to 2 MB...', 0, 119)
  DBCC SHRINKFILE('MSDBLog', 2)
END
go

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

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