/**********************************************************************/
/* PRE60TO7.SQL                                                       */
/*                                                                    */
/* Creates the following 6.0 MSDB tables in order to "stage" the data */
/* being imported by Convert:                                         */
/*                                                                    */
/*   systasks                                                         */
/*   syshistory                                                       */
/*   sysalerts                                                        */
/*   sysoperators                                                     */
/*   sysservermessages [which ultimately we throw away in MSDB6TO7]   */
/*                                                                    */
/* MSDB6TO7.SQL should be run following the import,                   */
/*
/*                                                                    */
** Copyright Microsoft, Inc. 1994 - 2000
** All Rights Reserved.
*/
/**********************************************************************/

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

USE msdb
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('The MSDB database has already been successfully upgraded to 7.0.', 20, 127) WITH LOG
go

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

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

/**************************************************************/
/* Temporarily rename the 7.0 systasks view so that we can    */
/* create the 6.0 systasks table.                             */
/**************************************************************/
IF ((SELECT status & 0x80000000 from msdb.dbo.sysobjects WHERE name = 'systasks') <> 0)
  UPDATE msdb.dbo.sysobjects SET status = status ^ 0x80000000 WHERE name = 'systasks'
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'systasks')
              AND (type = 'V')))
  EXECUTE sp_rename systasks, systasks_temp
go

/**************************************************************/
/* SYSTASKS                                                   */
/**************************************************************/

PRINT ''
PRINT 'Creating 6.0 table systasks...'
go

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

CREATE TABLE systasks
(
id                     int           IDENTITY,
name                   varchar(100)  NOT NULL,
subsystem              varchar(30)   NOT NULL,
server                 varchar(30)   NULL, 
username               varchar(30)   NULL,
ownerloginid           smallint      NOT NULL,
databasename           varchar(30)   NULL, 
enabled                tinyint       NOT NULL,
freqtype               int           NOT NULL,
freqinterval           int           NOT NULL,
freqsubtype            int           NOT NULL,
freqsubinterval        int           NOT NULL,
freqrelativeinterval   int           NOT NULL,
freqrecurrencefactor   int           NOT NULL,
activestartdate        int           NOT NULL,
activeenddate          int           NOT NULL,
activestarttimeofday   int           NOT NULL,
activeendtimeofday     int           NOT NULL,
lastrundate            int           NOT NULL,
lastruntime            int           NOT NULL,
nextrundate            int           NOT NULL,
nextruntime            int           NOT NULL,
runpriority            int           NOT NULL,
emailoperatorid        int           NULL,
retryattempts          int           NOT NULL,
retrydelay             int           NOT NULL,
datecreated            datetime      NOT NULL,
datemodified           datetime      NOT NULL,
command                varchar(255)  NULL,
lastruncompletionlevel int           NOT NULL,
lastrunduration        int           NOT NULL,
lastrunretries         int           NOT NULL,
loghistcompletionlevel int           NOT NULL,
emailcompletionlevel   int           NOT NULL,
description            varchar(255)  NULL,
tagadditionalinfo      varchar(96)   NULL,
tagobjectid            int           NULL,
tagobjecttype          int           NULL
)
go

CREATE UNIQUE CLUSTERED INDEX ucsystasks ON systasks(name)
CREATE UNIQUE NONCLUSTERED INDEX uncsystasks ON systasks(id)
go

/**************************************************************/
/* SYSHISTORY                                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating 6.0 table syshistory...'
go

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

CREATE TABLE syshistory
(
id                int             IDENTITY,
eventid           int             NULL,
messageid         int             NULL,
severity          int             NULL,
taskid            int             NULL,
source            varchar(30)     NULL,
category          varchar(30)     NULL,
runstatus         int             NULL,
rundate           int             NOT NULL,
runtime           int             NOT NULL,
runduration       int             NOT NULL,
reviewstatus      varchar(32)     NULL,
emailoperatorid   int             NULL,
retries           int             NOT NULL,
comments          varchar(255)    NULL,
timesskipped      int             NOT NULL
)
go

CREATE UNIQUE CLUSTERED INDEX ucsyshistory ON syshistory(id)
CREATE NONCLUSTERED INDEX uncsyshistory ON syshistory(taskid)
go

/**************************************************************/
/* SYSNOTIFICATIONS                                           */
/**************************************************************/

-- This table definition hasn't changed, so just truncate it (for safety)
TRUNCATE TABLE sysnotifications
go

/**************************************************************/
/* SYSALERTS                                                  */
/**************************************************************/

PRINT ''
PRINT 'Creating 6.0 table sysalerts...'
go

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

CREATE TABLE sysalerts
(
id                            int             IDENTITY,
name                          varchar(60)     NOT NULL,
event_source                  varchar(30)     NOT NULL,
event_categoryname            varchar(30)     NULL,
event_id                      varchar(20)     NULL,
message_id                    int             NULL,
severity                      int             NULL,
enabled                       tinyint         NOT NULL,
delay_between_notifications   int             NOT NULL,
last_occurrence_date          int             NULL,
last_occurrence_time          int             NULL,
last_response_date            int             NULL,
last_response_time            int             NULL,
notification_message          varchar(255)    NULL,
include_event_description     tinyint         NOT NULL,
database_name                 varchar(30)     NULL,
event_description_keyword     varchar(100)    NULL,
occurrence_count              int             NOT NULL,
count_reset_date              int             NULL,
count_reset_time              int             NULL,
task_id                       int             NULL,
has_email_notification        int             NOT NULL,
has_pager_notification        int             NOT NULL
)
go

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

/**************************************************************/
/* SYSOPERATORS                                               */
/**************************************************************/

PRINT ''
PRINT 'Creating 6.0 table sysoperators...'
go

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

CREATE TABLE sysoperators
(
id                        int             IDENTITY,
name                      varchar(50)     NOT NULL,
enabled                   tinyint         NOT NULL,
email_address             varchar(100)    NULL,
last_email_date           int             NULL,
last_email_time           int             NULL,
pager_address             varchar(100)    NULL,
last_pager_date           int             NULL,
last_pager_time           int             NULL,
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
)
go

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

/**************************************************************/
/* SYSSERVERMESSAGES                                          */
/**************************************************************/

PRINT 'Creating [temporary] 6.0 table sysservermessages...'
go

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

CREATE TABLE sysservermessages 
(
error       INT          NOT NULL,
severity    SMALLINT     NOT NULL,
dlevel      SMALLINT     NOT NULL,
description VARCHAR(255) NOT NULL,
language    VARCHAR(30)  NOT NULL
)
go

-- Insert a dummy row to prevent needless import of rows
SET NOCOUNT ON
INSERT INTO msdb.dbo.sysservermessages
       (error,
        severity,
        dlevel,
        description,
        language)
VALUES (911,
        10,
        0,
       'dummy',
       'dummy')
go

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

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

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