Microsoft Internet Business Development Kit/PRODUCT_CD.iso/sqlsvr/ppc/instmsdb.sql

Text File | 1995-12-12 | 156.3 KB | 4,729 lines

/**********************************************************************/
/* INSTMSDB.SQL                                                       */
/*                                                                    */
/* Installs the MSDB database and creates the Tasks, Alerts and       */
/* Operators T-SQL database objects used by SQLDMO and SQLExecutive.  */
/*                                                                    */
/* Copyright Microsoft, Inc. 1994, 1995, 1996.                        */
/* All Rights Reserved.                                               */
/*                                                                    */
/* Use, duplication, or disclosure by the United States Government    */
/* is subject to restrictions as set forth in subdivision (c) (1)(ii) */
/* of the Rights in Technical Data and Computer Software clause       */
/* at CFR 252.227-7013.                                               */
/* Microsoft, Inc. One Microsoft Way, Redmond WA. 98052.              */
/**********************************************************************/

/**************************************************************/
/**                                                          **/
/**                        T A S K S                         **/
/**                                                          **/
/**************************************************************/

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

if not exists (select name from master.dbo.sysdatabases where name = 'msdb')
begin
    PRINT 'Creating the msdb database...'
    PRINT ''
end
go

use master
go

set nocount on

if not exists (select name from master.dbo.sysdatabases where name = 'msdb')
    create database msdb on MSDBData = 6 log on MSDBLog = 2
else
begin
    print 'Checking the size of the MSDBData device...'
    exec ('dbcc updateusage(''msdb'') with no_infomsgs')

    /*
    ** Determine amount of free space in msdb. We need at least 2MB (1024 pages) free.
    */
    declare @size_of_dev int
    declare @free_db_space int
    declare @free_dev_space int
    declare @cmd varchar(255)

    create table #temp (size int)
    exec ('insert into #temp select (select sum(size) from master.dbo.sysusages where dbid = (select dbid from master.dbo.sysdatabases where name = ''msdb'') and segmap & 0x3 = 0x3)
                           - (select sum(reserved) from msdb.dbo.sysindexes where indid in (0, 1, 255))')
    select @free_db_space = size from #temp
        drop table #temp

    if (@free_db_space < 1024)
    begin
        /*
        ** See if the msdb device has enough free space to alter the msdb database.
        */
        select @size_of_dev = (select sum(high-low+1) from master.dbo.sysdevices where name = 'MSDBData')
        select @free_dev_space = @size_of_dev -
                         (select sum(size)
                      from master.dbo.sysusages
                      where vstart between
                              (select low from master.dbo.sysdevices where name = 'MSDBData')
                      and
                       (select high from master.dbo.sysdevices where name = 'MSDBData'))
        if (@free_dev_space < 1024)
        begin
            print ''
            print 'Attempting to expand the MSDBData device...'
            select @cmd = 'disk resize name = MSDBData, size = ' + convert(varchar(10), @size_of_dev + 1024)
            exec (@cmd)
            if (@@error <> 0)
                raiserror('Could not expand msdb device.  See event viewer or errorlog for details.', 16, 127) with log
          end

        print 'Attempting to alter the msdb database...'
        exec ('alter database msdb on MSDBData = 2')
        if @@error <> 0
            raiserror('Unable to expand the msdb database - "Data segment full" errors may result.', 16, 127) with log
    end
    print ''
end
go

sp_dboption msdb, 'trunc. log on chkpt.', true
go

use msdb
go

checkpoint
go

dump tran msdb with no_log
go

if not exists (select name from sysusers where name = 'repl_subscriber')
begin
  exec sp_adduser 'repl_subscriber'
end

if not exists (select name from sysusers where name = 'guest')
begin
  exec sp_adduser 'guest'
end
go

/**************************************************************/
/* DROPS                                                      */
/**************************************************************/

/* First, extract the build number from the version string */
set nocount on
declare @rebuild_needed bit
declare @version varchar(30)
declare @build_number int
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                           'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion',
                           'CurrentVersion',
                            @param = @version OUTPUT
if (@version = null)
  /* An error occurred reading the key, so don't force a rebuild */
  select @build_number = 999999
else
if (@version like '%NT 4.2%')
  /* We're running on a 4.2x or pre-4.2x server, so set the build number to 1 (to ensure a full build) */
  select @build_number = 1
else
  /* We're running on a 6.x server so extract the current build number */
  select @build_number = convert(int, right(@version, datalength(@version) - (patindex('%.[0-9][0-9].%',@version) + 4) + 1))

if (@build_number > 0) and (@build_number < 85)  /* The oldest build with the current schema */
begin
        select @rebuild_needed = 1
        print ''
        select 'Server Version ID' = @version, 'Table Rebuild Needed' = @rebuild_needed
        print 'Dropping Tables...'
end
else
        select @rebuild_needed = 0

/* Drop the tables ONLY if upgrading from a build EARLIER than the oldest build with the current schema */
if (exists (select * from sysobjects where id = object_id('dbo.sysalerts') and sysstat & 0xf = 3) and (@rebuild_needed = 1))
begin
    print ''
    print 'Dropping table sysalerts...'
    drop table dbo.sysalerts
end

if (exists (select * from sysobjects where id = object_id('sysoperators') and sysstat & 0xf = 3) and (@rebuild_needed = 1))
begin
    print ''
    print 'Dropping table sysoperators...'
    drop table sysoperators
end

if (exists (select * from sysobjects where name = 'systasks' and sysstat & 0xf = 3) and (@rebuild_needed = 1))
begin
    print ''
    print 'Dropping table systasks...'
    drop table systasks
end

if (exists (select * from sysobjects where name = 'syshistory' and sysstat & 0xf = 3) and (@rebuild_needed = 1))
begin
    print ''
    print 'Dropping table syshistory...'
    drop table syshistory
end

if (exists (SELECT * from sysobjects where name = 'sysnotifications' and sysstat & 0xf = 3) and (@rebuild_needed = 1))
begin
    print ''
    print 'Dropping table sysnotifications...'
    drop table sysnotifications
end

if (exists (select * from sysobjects where name = 'sysvolumelabel' and sysstat & 0xf = 3) and (@rebuild_needed = 1))
begin
    print ''
    print 'Dropping table sysvolumelabel...'
    drop table sysvolumelabel
end
go

if (exists (select * from sysobjects where id = object_id('dbo.MSWork') and sysstat & 0xf = 3))
    drop table MSWork
if (exists (select * from sysobjects where id = object_id('dbo.sysalerts_temp') and sysstat & 0xf = 3))
    drop table sysalerts_temp
if (exists (select * from sysobjects where id = object_id('dbo.sysoperators_temp') and sysstat & 0xf = 3))
    drop table sysoperators_temp
if (exists (select * from sysobjects where id = object_id('dbo.sysnotifications_temp') and sysstat & 0xf = 3))
    drop table sysnotifications_temp
if (exists (select * from sysobjects where id = object_id('dbo.systasks_temp') and sysstat & 0xf = 3))
    drop table systasks_temp
if (exists (select * from sysobjects where id = object_id('dbo.syshistory_temp') and sysstat & 0xf = 3))
    drop table syshistory_temp
go

PRINT ''
PRINT 'Dropping Task Procedures and Triggers...'
go
/* Now drop the procedures  and triggers */
if exists (select * from sysobjects where name = 'syshistory_row_limiter' and sysstat & 0xf = 8)
        drop trigger syshistory_row_limiter
go
if exists (select * from sysobjects where name = 'taskrefresh_ins' and sysstat & 0xf = 8)
        drop trigger taskrefresh_ins
go
if exists (select * from sysobjects where name = 'taskrefresh_del' and sysstat & 0xf = 8)
        drop trigger taskrefresh_del
go
if exists (select * from sysobjects where name = 'taskrefresh_upd' and sysstat & 0xf = 8)
        drop trigger taskrefresh_upd
go
if exists (select * from sysobjects where name = 'sp_verifytaskdate' and sysstat & 0xf = 4)
    drop proc sp_verifytaskdate
go
if exists (select * from sysobjects where name = 'sp_verifytasktime' and sysstat & 0xf = 4)
    drop proc sp_verifytasktime
go
if exists (select * from sysobjects where name = 'sp_verifytasksched' and sysstat & 0xf = 4)
    drop proc sp_verifytasksched
go
if exists (select * from sysobjects where name = 'sp_verifytask' and sysstat & 0xf = 4)
    drop proc sp_verifytask
go
if exists (select * from sysobjects where name = 'sp_verifytaskid' and sysstat & 0xf = 4)
    drop proc sp_verifytaskid
go
if exists (select * from sysobjects where name = 'sp_addtask' and sysstat & 0xf = 4)
    drop proc sp_addtask
go
if exists (select * from sysobjects where name = 'sp_updatetask' and sysstat & 0xf = 4)
    drop proc sp_updatetask
go
if exists (select * from sysobjects where name = 'sp_droptask' and sysstat & 0xf = 4)
    drop proc sp_droptask
go
if exists (select * from sysobjects where name = 'sp_reassigntask' and sysstat & 0xf = 4)
    drop proc sp_reassigntask
go
if exists (select * from sysobjects where name = 'sp_uniquetaskname' and sysstat & 0xf = 4)
    drop proc sp_uniquetaskname
go
if exists (select * from sysobjects where name = 'sp_helptask' and sysstat & 0xf = 4)
    drop proc sp_helptask
go
if exists (select * from sysobjects where name = 'sp_helphistory' and sysstat & 0xf = 4)
    drop proc sp_helphistory
go
if exists (select * from sysobjects where name = 'sp_purgehistory' and sysstat & 0xf = 4)
    drop proc sp_purgehistory
go
if exists (select * from sysobjects where name = 'sp_schedulerrefresh' and sysstat & 0xf = 4)
    drop proc sp_schedulerrefresh
go
if exists (select * from sysobjects where name = 'sp_schedulerlog' and sysstat & 0xf = 4)
    drop proc sp_schedulerlog
go
if exists (select * from sysobjects where name = 'sp_MScheckforownedtasks ' and sysstat & 0xf = 4)
    drop proc sp_MScheckforownedtasks
go
if exists (select * from sysobjects where name = 'sp_MScheckforownedtasks ' and sysstat & 0xf = 4)
    drop proc sp_MScheckforownedtasks
go
if exists (select * from sysobjects where name = 'sp_runtask' and sysstat & 0xf = 4)
    drop proc sp_runtask
go
if exists (select * from sysobjects where name = 'sp_MStaskparameters' and sysstat & 0xf = 4)
    drop proc sp_MStaskparameters
go
if exists (select * from sysobjects where name = 'systasks_view' and sysstat & 0xf = 2)
    drop view systasks_view
go

dump tran msdb with no_log
go

/**************************************************************/
/* MSWORK                                                     */
/**************************************************************/
PRINT ''
PRINT 'Creating table MSWork...'
go
CREATE TABLE MSWork
(
spid   smallint,
value1 varchar(255) NULL,
value2 varchar(255) NULL,
value3 varchar(255) NULL,
value4 varchar(255) NULL,
value5 varchar(255) NULL
)
go

/**************************************************************/
/* SYSALERTS                                                  */
/**************************************************************/
if not exists(select * from sysobjects where id = object_id('dbo.sysalerts') and sysstat & 0xf = 3)
begin
  PRINT ''
  PRINT 'Creating table sysalerts...'

  CREATE TABLE sysalerts_temp
  (
  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,
  flags                 int                NOT NULL
  )
  EXEC sp_rename 'sysalerts_temp', 'sysalerts'

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


/**************************************************************/
/* SYSOPERATORS                                               */
/**************************************************************/
if not exists(select * from sysobjects where id = object_id('dbo.sysoperators') and sysstat & 0xf = 3)
begin
  PRINT ''
  PRINT 'Creating table sysoperators...'

  CREATE TABLE sysoperators_temp
  (
  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
  )
  EXEC sp_rename 'sysoperators_temp', 'sysoperators'

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


/**************************************************************/
/* SYSNOTIFICATIONS                                           */
/**************************************************************/
if not exists(select * from sysobjects where id = object_id('dbo.sysnotifications') and sysstat & 0xf = 3)
begin
  PRINT ''
  PRINT 'Creating table sysnotifications...'

  CREATE TABLE sysnotifications_temp
  (
  alert_id            int                NOT NULL,
  operator_id            int                NOT NULL,
  notification_method        tinyint                NOT NULL
  )
  EXEC sp_rename 'sysnotifications_temp', 'sysnotifications'

  CREATE UNIQUE CLUSTERED INDEX ByAlertIDAndOperatorID ON dbo.sysnotifications(alert_id, operator_id)
end
go


/**************************************************************/
/* SYSTASKS                                                   */
/**************************************************************/
if not exists(select * from sysobjects where id = object_id('dbo.systasks') and sysstat & 0xf = 3)
begin
  PRINT ''
  PRINT 'Creating table systasks...'

  create table systasks_temp
  (
  id                 int IDENTITY,        /* task id */
  name                varchar(100),        /* task name:  Long enough for server_database_publication_NNNNNNN. */
  subsystem            varchar(30),        /* task executive subsystem */
  server            varchar(30)    null,    /* target server (replication only) */
  username            varchar(30)    null,    /* user to setuser in database for this task */
  ownerloginid            smallint,        /* login id of task creator (should REFERENCE master.dbo.syslogins but cross-db not allowed). */
  databasename            varchar(30)    null,    /* database to use for this task */
  enabled            tinyint,        /* invocable if nonzero (boolean; tinyint for expandability) */
  freqtype            int,            /* main frequency type (day, week, month, monthrelative) */
  freqinterval            int,            /* see docs */
  freqsubtype            int,            /* for daily type; see docs */
  freqsubinterval        int,            /* for daily type; see docs */
  freqrelativeinterval         int,            /* see docs */
  freqrecurrencefactor        int,            /* see docs */
  activestartdate        int,            /* date for task to begin being scheduled */
  activeenddate            int,            /* date for task to stop being scheduled */
  activestarttimeofday        int,            /* time of day for task to begin being scheduled */
  activeendtimeofday        int,            /* time of day for task to stop being scheduled */
  lastrundate            int,            /* last day this task was run (yyyymmdd) */
  lastruntime            int,            /* time of last day this task was run (hhmmss) */
  nextrundate            int,            /* next day to run task */
  nextruntime            int,            /* time of next day to run task */
  runpriority            int,            /* nt execution priority for this task */
  emailoperatorid        int         null,    /* id of operator to email on emailcompletionlevel */
  retryattempts            int,            /* number of retries to attempt on failure */
  retrydelay            int,            /* time in seconds to delay between retries */
  datecreated            datetime,        /* date and time task was created */
  datemodified            datetime,        /* date and time task was last modified */
  command            varchar(255)     null,    /* command to be passed to executive subsystem */
  lastruncompletionlevel    int,            /* completion level of last run (succeed, fail, running, abort, skipped) */
  lastrunduration        int,            /* duration of last run (hhmmss) */
  lastrunretries        int,             /* number of retries attempted for last run of this task */
  loghistcompletionlevel     int,            /* completion level (succeed/fail/always) for which history records are logged */
  emailcompletionlevel         int,            /* completion level (succeed/fail/always) for which email is sent to emailoperator */
  description            varchar(255)    null,    /* task description */
  tagadditionalinfo           varchar(96)    null,    /* additional tagging info for this task */
  tagobjectid                 int        null,    /* id of object related to this task (e.g. publication) */
  tagobjecttype          int        null    /* type of object related to this task (e.g. publication) */
  )
  exec sp_rename 'systasks_temp', 'systasks'

  create unique clustered index ucsystasks on systasks(name)
  create unique nonclustered index uncsystasks on systasks(id)
end
go

PRINT ''
PRINT 'Creating view systasks_view...'
go
CREATE VIEW systasks_view
AS
SELECT *
FROM msdb.dbo.systasks
WHERE ownerloginid = suser_id()
   OR suser_name() IN ('sa', 'repl_publisher', 'repl_subscriber')
go


/************************************************************************/
/* Systasks upgrade (add 'parameters' column if not already present)    */
/************************************************************************/
SET NOCOUNT ON
IF NOT EXISTS (SELECT *
               FROM msdb.dbo.syscolumns
               WHERE id = object_id('dbo.systasks')
                 AND name = 'parameters')
BEGIN
  PRINT ''
  PRINT 'Upgrading systasks definition...'
  ALTER TABLE msdb.dbo.systasks ADD parameters TEXT NULL
END
go


/**************************************************************/
/* SYSHISTORY                                                 */
/**************************************************************/
if not exists(select * from sysobjects where id = object_id('dbo.syshistory') and sysstat & 0xf = 3)
begin
  PRINT ''
  PRINT 'Creating table syshistory...'

  create table syshistory_temp
  (
  id                int IDENTITY,        /* id of this history entry */
  eventid             int        null,    /* id of sql event (alert) causing this, if not a task */
  messageid            int        null,    /* sql message number */
  severity            int        null,    /* sql severity level */
  taskid            int        null,    /* task id (if not an alert) */
  source            varchar(30)    null,    /* eventlog source of this entry */
  category            varchar(30)    null,    /* eventlog category of this entry */
  runstatus            int        null,    /* completion level of this task run (sqlole_completion_type), or eventlog state (sqlole_event_type) */
  rundate            int,            /* date of task run or alert (yyyymmdd) */
  runtime            int,            /* time of task run or alert (hhmmss) */
  runduration            int,            /* duration of task run (hhmmss) */
  reviewstatus            varchar(32)    null,    /* tbd */
  emailoperatorid        int        null,    /* id of operator emailed, if any */
  retries            int,            /* retries attempted on this run */
  comments            varchar(255)    null,    /* tbd (subsystem entry) */
  timesskipped            int            /* if nonzero, task skipped this many times due to down scheduler, */
  )
  exec sp_rename 'syshistory_temp', 'syshistory'

  create unique clustered index ucsyshistory on syshistory(id)
end
go


/**************************************************************/
/* SYSHISTORY_ROW_LIMITER TRIGGER                             */
/**************************************************************/
PRINT ''
PRINT 'Creating trigger syshistory_row_limiter...'
go

create trigger syshistory_row_limiter
on msdb.dbo.syshistory
for insert
as
begin
        declare @oldest_id int
        declare @max_rows int
        declare @current_rows int
        declare @limit_syshistory_rows int

        /* Are we limiting syshistory entries ? */
        execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                      'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                      'SyshistoryLimitRows',
                                      @param = @limit_syshistory_rows output
        if (isnull(@limit_syshistory_rows, 0) = 0)
                return

        /* Get the id of the oldest row */
        select @oldest_id = min(id)
        from msdb.dbo.syshistory

        /* Get the maximum number of rows to keep from the registry */
        execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                      'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                      'SyshistoryMaxRows',
                                      @param = @max_rows output
        if (isnull(@max_rows, 0) < 2)
                return

        select @current_rows = rows
        from sysindexes
        where id = object_id('msdb.dbo.syshistory')
          and indid = 1

        /* Delete the oldest row if inserting the new row has pushed us over MaxSyshistoryRows */
        if (@current_rows > (@max_rows - 1))
                delete from msdb.dbo.syshistory
                where id < (@oldest_id + (@current_rows - (@max_rows - 1)))
end
go


/**************************************************************/
/* SP_SCHEDULERSIGNAL (a 'master' database proc)              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_schedulersignal...'
go
USE master
go
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
PRINT 'The RECONFIGURE command was run.'
go

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_schedulersignal' AND sysstat & 0xf = 4)
  DROP PROC sp_schedulersignal
go
/* Clear down lingering permissions */
DELETE FROM sysprotects WHERE id = object_id('xp_schedulersignal')
go

/* Create the procedure */
CREATE PROCEDURE sp_schedulersignal
(
@OpType      char(1),          /* One of R, O, I, T, A, G, D or E.                */
@ID          int      = NULL,  /* Task ID (required for OpTypes R, O and I only). */
@TriggerType char(1)  = NULL   /* One of I, U, D (required for OpType R only).    */
)
AS
BEGIN
  DECLARE @RetVal int
  SELECT @RetVal = 0 /* Success */

  /* Parameter Validation */
  IF (charindex(@OpType, 'ROITAGDE') = 0)
  BEGIN
    RAISERROR (14249, -1, -1)
    RETURN 1 /* Failure */
  END

  IF (charindex(@OpType, 'ROI') <> 0) AND ((@ID = NULL) OR NOT EXISTS (SELECT id FROM msdb.dbo.systasks WHERE id = @ID))
  BEGIN
    IF NOT ((@OpType = 'R') AND (@ID <> NULL) AND (@TriggerType = 'D'))
    BEGIN
      RAISERROR (14250, -1, -1)
      RETURN 1 /* Failure */
    END
  END

  IF ((@OpType = 'R') AND ((@TriggerType = NULL) OR charindex(@TriggerType, 'IUD') = 0))
  BEGIN
    RAISERROR (14251, -1, -1)
    RETURN 1 /* Failure */
  END

  /* Parameters are valid, so now check execution permissions */
  IF (charindex(@OpType, 'ROI') <> 0)
  BEGIN
    /* Caller must be SA, DBO, repl_subscriber, repl_publisher or the task owner */
    IF NOT ((suser_id() = 1) OR
            (upper(user_name()) = 'DBO') OR
            (upper(user_name()) = 'REPL_SUBSCRIBER') OR
            (upper(user_name()) = 'REPL_PUBLISHER') OR
            (EXISTS (SELECT ownerloginid
                     FROM msdb.dbo.systasks
                     WHERE id = @ID AND ownerloginid = suser_id())))
    BEGIN
      IF NOT ((@OpType = 'R') AND (@ID <> NULL) AND (@TriggerType = 'D'))
      BEGIN
        RAISERROR (14252, -1, -1)
        RETURN 1 /* Failure */
      END
    END
  END

  /* Ok, let's do it... */
  IF (@OpType = 'R')
    EXEC @RetVal = master.dbo.xp_schedulersignal @OpType, @ID, @TriggerType
  ELSE
  IF (charindex(@OpType, 'OI') <> 0)
    EXEC @RetVal = master.dbo.xp_schedulersignal @OpType, @ID
  ELSE
  IF (charindex(@OpType, 'TAGDE') <> 0)
    EXEC @RetVal = master.dbo.xp_schedulersignal @OpType

  RETURN @RetVal
END
go
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
PRINT 'The RECONFIGURE command was run.'
go
USE msdb
go


/**************************************************************/
/* SP_VERIFYTASKDATE                                          */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_verifytaskdate...'
go

/* verify format of a date:  yyyymmdd */
create proc sp_verifytaskdate
 @date int,
 @errmsg int
as
    if (@date = 0)
        return 0
    if ((((@date % 10000) / 100) > 12) or ((@date % 100) > 31))
    begin
        RAISERROR (@errmsg, -1, -1)
        return 1
    end
    return 0
go
/* end sp_verifytaskdate */


/**************************************************************/
/* SP_VERIFYTASKTIME                                          */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_verifytasktime...'
go

/* verify format of a time:  hhmmss */
create proc sp_verifytasktime
 @time int,
 @errmsg int
as
    if (@time = 0)
        return 0
    if ((@time / 10000) > 23
            or ((@time % 10000) / 100) > 59
            or (@time % 100) > 59)
    begin
        RAISERROR (@errmsg, -1, -1)
        return 1
    end
    return 0
go
/* end sp_verifytasktime */


/**************************************************************/
/* SP_VERIFYTASKSCHED                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_verifytasksched...'
go

/* verify that a task's frequency values are correctly defined.  Broken out of sp_verifytask for replication. */
/* Default param values are for replication. */
create proc sp_verifytasksched
 @freqtype                int,
 @freqinterval            int,
 @freqsubtype            int,
 @freqrelativeinterval    int,
 @activestartdate        int = 0,
 @activeenddate        int = 0     out,
 @activestarttimeofday    int = 0,
 @activeendtimeofday    int = 0  out,
 @nextrundate            int = 0     out,
 @nextruntime            int = 0     out,
 @enabled           int = 0                     /* pass as 0 to prevent checking of start/end/nextrun date/times */
as
    declare @curdate int, @curtime int, @valid int
    select @curdate = datepart(year, getdate()) * 10000 + datepart(month, getdate()) * 100 + datepart(day, getdate())
    select @curtime = datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())

    /* verify that freqtype and freqsubtype are valid.  see sqlole.h. */
    /* sqlolefreq_valid */
    if (@freqtype & 0x007f = 0)
    begin
        RAISERROR (14200, -1, -1)
        return 1
    end

    /* sqlolefreqsub_valid */
    if (@freqsubtype <> 0 and @freqsubtype & 0x000f = 0)
    begin
        RAISERROR (14201, -1, -1)
        return 1
    end

    /* verify that the interval is valid if it has a special meaning for this frequency type */
    /* see sqlole\task.cpp for details of weekly (8; bitmask of weekdays) and monthlyrelative */
    /* (20; single day type) interval validation. */
    if ((@freqtype = 8 and @freqinterval & 0x007f <> @freqinterval) or
            (@freqtype = 0x0020 and (@freqinterval < 1 or @freqinterval > 10)))
    begin
        RAISERROR (14202, -1, -1)
        return 1
    end
    if (@freqtype = 0x0020 and (@freqrelativeinterval & 0x001f <> @freqrelativeinterval))
    begin
        RAISERROR (14203, -1, -1)
        return 1
    end

    /* verify dates, kinda:  ignore months not having 31 days */
    /* for scheduler logic, which requires a math comparison of activestart% < activeend%, */
    /* set to max values (from sqlole.h; sqlole_noenddate/time). */
    if (@activeenddate = 0)
    begin
        select @activeenddate = 99991231
    end else begin
        exec @valid = sp_verifytaskdate @activeenddate, 14236
        if (@valid <> 0)
            return 1
        if (@activeenddate < @activestartdate)
        begin
            RAISERROR (14204, -1, -1)
            return 1
        end
    end

    /* verify times.. same math logic for max time of day. */
    if (@activeendtimeofday = 0)
    begin
        select @activeendtimeofday = 235959
    end else begin
        exec @valid = sp_verifytasktime @activeendtimeofday, 14237
        if (@valid <> 0)
            return 1
        if (@activeendtimeofday < @activestarttimeofday)
        begin
            RAISERROR (14205, -1, -1)
            return 1
        end
    end

    /* verify activestartdate, activestarttimeofday, nextrundate and nextruntime if not ondemand (0x0002) */
    if (@enabled = 0 or @freqtype = 2 or @freqtype = 64)
    begin
        select @nextrundate = 0
        select @nextruntime = 0
    end else begin
        /* startdate/time; 0 will be valid. */
        exec @valid = sp_verifytaskdate @activestartdate, 14238
        if (@valid = 0)
            exec @valid = sp_verifytasktime @activestarttimeofday, 14239
        if (@valid <> 0)
            return 1

        exec @valid = sp_verifytaskdate @nextrundate, 14240
        if (@valid <> 0)
            return 1
        if (@curdate > @nextrundate)
        begin
/************ Changed for replication, if no problems, nuke this later.*****************
            raiserror 66666 'Next run date cannot be less than current date'
            return 1
***************************************************************************************/
         select @nextrundate = 0
        end
        if (@nextrundate <> 0) and ((@activeenddate <> 0 and @nextrundate > @activeenddate) or (@nextrundate < @activestartdate))
        begin
            RAISERROR (14206, -1, -1)
            return 1
        end

        exec @valid = sp_verifytasktime @nextruntime, 14241
        if (@valid <> 0)
            return 1
        if (@curdate = @nextrundate and @curtime > @nextruntime)
        begin
/************ Changed for replication, if no problems, nuke this later.*****************
            raiserror 66666 'Next run time cannot be less than current time if next run date is the current date'
            return 1
***************************************************************************************/
         select @nextruntime = 0
        end
        if (@nextruntime <> 0) and ((@activeendtimeofday <> 0 and @nextruntime > @activeendtimeofday) or (@nextruntime < @activestarttimeofday))
        begin
            RAISERROR (14207, -1, -1)
            return 1
        end
    end

    /* success */
    return 0
go
/* end sp_verifytasksched */


/**************************************************************/
/* SP_VERIFYTASK                                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_verifytask...'
go

/* verify that a task is correctly defined.  also, fill in the id output params from the name input params. */
create proc sp_verifytask
 @id                    int,
 @name                     varchar(100),
 @server                varchar(30),
 @databasename            varchar(30),
 @enabled                int,
 @freqtype                int,
 @freqinterval            int,
 @freqsubtype            int,
 @freqrelativeinterval    int,
 @activestartdate        int,
 @activeenddate            int          out,
 @activestarttimeofday    int,
 @activeendtimeofday    int          out,
 @nextrundate            int          out,
 @nextruntime            int          out,
 @runpriority            int,
 @emailoperatorname        varchar(30),
 @emailoperatorid        int          out,
 @command                varchar(255),
 @loghistcompletionlevel int,
 @emailcompletionlevel int
as
    declare @valid int

    /* Make sure empty strings are interpreted as NULLs */
    select @name = ltrim(@name)
    select @server = ltrim(@server)
    select @databasename = ltrim(@databasename)
    select @emailoperatorname = ltrim(@emailoperatorname)
    select @command = ltrim(@command)

    if (@name is null)
    begin
        RAISERROR (14208, -1, -1)
        return 1
    end

    if (@server is not null and not exists (select * from master.dbo.sysservers where srvname = @server))
    begin
        RAISERROR (14209, -1, -1, @server)
        return 1
    end

    if exists (select * from msdb.dbo.systasks where name = @name and id <> @id)
    begin
        declare @owner_login_id varchar(30)
        select @owner_login_id = suser_name(ownerloginid) from systasks    where name = @name
        RAISERROR (14210, -1, -1, @name, @owner_login_id)
        return 1
    end

    /*
     * (@databasename currently not verified).
     */

    /* Verify email operator. */
    select @emailoperatorid = id from msdb.dbo.sysoperators where name = @emailoperatorname
    if (@emailoperatorid is null and @emailoperatorname is not null and @emailoperatorname <> '')
    begin
        RAISERROR (14212, -1, -1, @emailoperatorname)
        return 1
    end

   /* Make sure the frequency info and start/end info is set okay. */

    exec @valid = sp_verifytasksched @freqtype,
       @freqinterval,
       @freqsubtype,
       @freqrelativeinterval,
       @activestartdate,
       @activeenddate        out,
       @activestarttimeofday,
       @activeendtimeofday    out,
       @nextrundate          out,
       @nextruntime          out,
       @enabled
    if (@valid <> 0)
        return 1

    /* Verify run priority: must be a valid value to pass to SetThreadPriority. */
    if (@runpriority not in (-15, -2, -1, 0, 1, 2, 15))
    begin
        RAISERROR (14213, -1, -1)
        return 1
    end

    /* verify completion levels:  succeed, fail, always (see sqlole.h, sqlole_completion_type). */
    /* sqlolecomp_always */
    if (@loghistcompletionlevel & 0x0003 <> @loghistcompletionlevel)
    begin
        RAISERROR (14214, -1, -1)
        return 1
    end
    if (@emailcompletionlevel & 0x0003 <> @emailcompletionlevel)
    begin
        RAISERROR (14215, -1, -1)
        return 1
    end

    /* success */
    return 0
go
/* end sp_verifytask */

/**************************************************************/
/* SP_VERIFYTASKID   Used by instrepl.sql                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_verifytaskid...'
go
create procedure sp_verifytaskid
@taskid int,
@subsystem varchar (30) = '%'
AS

   IF EXISTS (SELECT * FROM msdb.dbo.systasks WHERE id = @taskid AND
      lower(subsystem) like lower(@subsystem))
      return 0
   ELSE
      return 1
go

/**************************************************************/
/* SP_ADDTASK                                                 */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_addtask...'
go

create proc sp_addtask
 @name                    varchar(100),
 @subsystem                varchar(30) = 'TSQL',
 @server                varchar(30) = null,
 @username                varchar(30) = null,
 @databasename            varchar(30) = null,
 @enabled                tinyint = 0,
 @freqtype                int = 2,  /* 2 == 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        varchar(30) = null,
 @retryattempts            int = 0,
 @retrydelay            int = 10,
 @command                varchar(255) = null,
 @loghistcompletionlevel    int = 2,
 @emailcompletionlevel        int = 0,
 @description            varchar(255) = null,
 @tagadditionalinfo     varchar(96) = null,
 @tagobjectid           int = null,
 @tagobjecttype         int = null,
 @newid                 int = null OUT,
 @parameters            text = null
as
    declare @emailoperatorid int
    declare @verify int
    declare @usernameintargetdb varchar(30)
    set nocount on

    declare @curdate int, @curtime int
    select @curdate = datepart(year, getdate()) * 10000 + datepart(month, getdate()) * 100 + datepart(day, getdate())
    select @curtime = datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())

    /* Get the username of the caller in the target database */
    if (@subsystem = 'TSQL')
    begin
        /* But first check if remote server name has been supplied and exit if so */
        if (@server <> null) and (upper(@server) <> upper(@@servername))
        begin
            raiserror(14253, -1, -1)
            return 1
        end
        select @server = null

        if (ltrim(rtrim(@databasename)) = null)
            select @databasename = 'master'
        delete from MSWork where spid = @@spid

        /* The insert into MSWork will always fail if the database is not found, but it does so with */
        /* a cryptic 208 ("xxx.dbo.sysusers not valid object") message, so pre-validate the database. */
        if (db_id(@databasename) is null) begin
            raiserror (14211, -1, -1, @databasename)
            return 1
        end

        /* This next step may fail with a 916 (and then an annoying 208), but that's */
            /* OK since it's good to know ahead of time that the task won't run */
        exec ('insert into MSWork(spid, value1) select @@spid, name from '+ @databasename +'.dbo.sysusers where suid = suser_id()')
        if (@@error <> 0)
            return 1
        exec ('if not exists (select * from MSWork where spid = @@spid) insert into MSWork(spid, value1) select @@spid, name from '+
                @databasename +'.dbo.sysusers where suid = (select altsuid from '+ @databasename +'.dbo.sysalternates where suid = suser_id())')
        exec ('if not exists (select * from MSWork where spid = @@spid) insert into MSWork(spid, value1) select @@spid, name from '+ @databasename +'.dbo.sysusers where name = ''guest''')
        select @usernameintargetdb = value1 from MSWork where spid = @@spid
        delete from MSWork where spid = @@spid

        /* Now check if the caller has supplied a permissible @username */
        if (suser_id() = 1) and ((upper(@username) = 'SA') OR (upper(@username) = 'DBO'))
            select @username = null
        else
        if (suser_id() <> 1) and (ltrim(rtrim(@username)) = null)
            select @username = @usernameintargetdb
        else
        /* Only the SA or the DBO of the target db can set a "not me" @username */
        if (suser_id() <> 1) and (upper(@usernameintargetdb) <> 'DBO') and (isnull(@username, '') <> @usernameintargetdb)
               begin
            raiserror(14247, -1, -1, @username, @usernameintargetdb, @databasename, @databasename)
            return 1
        end
    end

    exec @verify = sp_verifytask 0, @name, @server, @databasename,
         @enabled,
         @freqtype,
         @freqinterval,
         @freqsubtype,
         @freqrelativeinterval,
         @activestartdate,
         @activeenddate out,
         @activestarttimeofday,
         @activeendtimeofday out,
         @nextrundate out,
         @nextruntime out,
         @runpriority,
         @emailoperatorname, @emailoperatorid out,
         @command,
         @loghistcompletionlevel,
         @emailcompletionlevel
    if (@verify <> 0)
        return 1

      /* insert the row. */
    insert msdb.dbo.systasks
    (
          name,
          subsystem,
          server,
          username,
          ownerloginid,
          databasename,
          enabled,
          freqtype,
          freqinterval,
          freqsubtype,
          freqsubinterval,
          freqrelativeinterval,
          freqrecurrencefactor,
          activestartdate,
          activeenddate,
          activestarttimeofday,
          activeendtimeofday,
          lastrundate,
          lastruntime,
          nextrundate,
          nextruntime,
          runpriority,
          emailoperatorid,
          retryattempts,
          retrydelay,
          datecreated,
          datemodified,
          command,
          lastruncompletionlevel,
          lastrunduration,
          lastrunretries,
          loghistcompletionlevel,
          emailcompletionlevel,
          description,
          tagadditionalinfo,
          tagobjectid,
          tagobjecttype,
          parameters
    )
    values
    (
         @name,
         @subsystem,
         @server,
         @username,
         suser_id(),
         @databasename,
         @enabled,
         @freqtype,
         @freqinterval,
         @freqsubtype,
         @freqsubinterval,
         @freqrelativeinterval,
         @freqrecurrencefactor,
         @activestartdate,
         @activeenddate,
         @activestarttimeofday,
         @activeendtimeofday,
        0,
         0,
         @nextrundate,
         @nextruntime,
         @runpriority,
         @emailoperatorid,
         @retryattempts,
         @retrydelay,
        getdate(),
        getdate(),
         @command,
        0,
        0,
        0,
         @loghistcompletionlevel,
         @emailcompletionlevel,
         @description,
        @tagadditionalinfo,
        @tagobjectid,
        @tagobjecttype,
         @parameters
    )
    if (@@error <> 0)
        return 1

    select @newid = id from msdb.dbo.systasks where name = @name
    return 0
go
/* end sp_addtask */


/**************************************************************/
/* SP_UPDATETASK                                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_updatetask...'
go

create proc sp_updatetask
 @currentname            varchar(100) = null,
 @id                    int = null,
 @name                    varchar(100) = null,
 @subsystem                varchar(30) = null,
 @server                varchar(30) = null,
 @username                varchar(30) = null,
 @databasename            varchar(30) = null,
 @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        varchar(30) = null,
 @retryattempts            int = null,
 @retrydelay            int = null,
 @command                varchar(255) = null,
 @loghistcompletionlevel     int = null,
 @emailcompletionlevel         int = null,
 @description            varchar(255) = null,
 @tagadditionalinfo     varchar(96) = null,
 @tagobjectid           int = null,
 @tagobjecttype         int = null,
 @parameters            text = null
as
    if (@currentname is null and @id is null)
    begin
        RAISERROR (14246, -1, -1)
        return 1
    end

    /* We'll key the update off task id, so get that first. */
    if (@id is not null)
    begin
        if not exists (select * from msdb.dbo.systasks where id = @id)
        begin
            RAISERROR (14216, -1, -1)
            return 1
        end
    end else begin
        select @id = id from msdb.dbo.systasks where name = @currentname
        if @id is null
        begin
            RAISERROR (14222, -1, -1, @currentname)
            return 1
        end
    end

    /* grab all the stuff we need into local variables, either from the input params or from the table. */
    /* to make this fast, load locals into vars, then copy into those vars from nonnull params. */
    declare @tname                    varchar(100),
            @tsubsystem                varchar(30),
            @tserver                varchar(30),
            @tusername                varchar(30),
            @tdatabasename            varchar(30),
            @tenabled                tinyint,
            @tfreqtype                int,
            @tfreqinterval            int,
            @tfreqsubtype            int,
            @tfreqsubinterval        int,
            @tfreqrelativeinterval     int,
            @tfreqrecurrencefactor    int,
            @tactivestartdate        int,
            @tactiveenddate            int,
            @tactivestarttimeofday    int,
            @tactiveendtimeofday    int,
            @tnextrundate            int,
            @tnextruntime            int,
            @trunpriority            int,
            @temailoperatorid             int,
            @temailoperatorname        varchar(30),
            @tretryattempts            int,
            @tretrydelay            int,
            @tcommand                varchar(255),
            @tloghistcompletionlevel int,
            @temailcompletionlevel     int,
            @tdescription            varchar(255),
            @townerloginid            int,
            @ttagadditionalinfo        varchar(96),
            @ttagobjectid            int,
            @ttagobjecttype            int
    declare @verify int
    declare @usernameintargetdb varchar(30)
    set nocount on

    /* load the local variables */
    select @tname                    = name,
            @tsubsystem                = subsystem,
            @tserver                = server,
            @tusername                = username,
            @tdatabasename            = databasename,
            @tenabled                = enabled,
            @tfreqtype                = freqtype,
            @tfreqinterval            = freqinterval,
            @tfreqsubtype            = freqsubtype,
            @tfreqsubinterval        = freqsubinterval,
            @tfreqrelativeinterval     = freqrelativeinterval,
            @tfreqrecurrencefactor    = freqrecurrencefactor,
            @tactivestartdate        = activestartdate,
            @tactiveenddate            = activeenddate,
            @tactivestarttimeofday    = activestarttimeofday,
            @tactiveendtimeofday    = activeendtimeofday,
            @tnextrundate            = nextrundate,
            @tnextruntime            = nextruntime,
            @trunpriority            = runpriority,
            @temailoperatorid             = emailoperatorid,
            @tretryattempts            = retryattempts,
            @tretrydelay            = retrydelay,
            @tcommand                = command,
            @tloghistcompletionlevel = loghistcompletionlevel,
            @temailcompletionlevel     = emailcompletionlevel,
            @tdescription            = description,
            @townerloginid             = ownerloginid,
            @ttagadditionalinfo     = tagadditionalinfo,
            @ttagobjectid             = tagobjectid,
            @ttagobjecttype         = tagobjecttype
        from msdb.dbo.systasks
        where id = @id

    /* check the ones we convert from name to id.  these also may be overridden below */
    select @temailoperatorname = name from msdb.dbo.systasks where id = @temailoperatorid

    /* make sure this guy is the task owner or the sa, or he can't do this. */
    if (suser_id() <> 1 and @townerloginid <> suser_id())
    begin
        RAISERROR (14217, -1, -1)
        return 1
    end

    /* overwrite them with any nonnull params */
    /* NOTE:  To null out a column in the table, pass '' -- I'll make it null in the next block below */
    if (@name is not null) select @tname = @name
    if (@subsystem is not null) select @tsubsystem = @subsystem
    if (@server is not null) select @tserver = @server
    if (@username is not null) select @tusername = @username
    if (@databasename is not null) select @tdatabasename = @databasename
    if (@enabled is not null) select @tenabled = @enabled
    if (@freqtype is not null) select @tfreqtype = @freqtype
    if (@freqinterval is not null) select @tfreqinterval = @freqinterval
    if (@freqsubtype is not null) select @tfreqsubtype = @freqsubtype
    if (@freqsubinterval is not null) select @tfreqsubinterval = @freqsubinterval
    if (@freqrelativeinterval is not null) select @tfreqrelativeinterval = @freqrelativeinterval
    if (@freqrecurrencefactor is not null) select @tfreqrecurrencefactor = @freqrecurrencefactor
    if (@activestartdate is not null) select @tactivestartdate = @activestartdate
    if (@activeenddate is not null) select @tactiveenddate = @activeenddate
    if (@activestarttimeofday is not null) select @tactivestarttimeofday = @activestarttimeofday
    if (@activeendtimeofday is not null) select @tactiveendtimeofday = @activeendtimeofday
    if (@nextrundate is not null) select @tnextrundate = @nextrundate
    if (@nextruntime is not null) select @tnextruntime = @nextruntime
    if (@runpriority is not null) select @trunpriority = @runpriority
    if (@emailoperatorname is not null) select @temailoperatorname = @emailoperatorname
    if (@retryattempts is not null) select @tretryattempts = @retryattempts
    if (@retrydelay is not null) select @tretrydelay = @retrydelay
    if (@command is not null) select @tcommand = @command
    if (@loghistcompletionlevel is not null) select @tloghistcompletionlevel = @loghistcompletionlevel
    if (@emailcompletionlevel is not null) select @temailcompletionlevel = @emailcompletionlevel
    if (@description is not null) select @tdescription = @description
    if (@tagadditionalinfo is not null) select @ttagadditionalinfo = @tagadditionalinfo
    if (@tagobjectid is not null) select @ttagobjectid = @tagobjectid
    if (@tagobjecttype is not null) select @ttagobjecttype = @tagobjecttype

   /* Check the nullables for a '' or 0 new value where appropriate, and use NULL (for a more readable systable) if found. */
   if (ltrim(@tserver) = null) select @tserver = null
   if (ltrim(@tusername) = null) select @tusername = null
   if (ltrim(@tdatabasename) = null) select @tdatabasename = null
   if (ltrim(@temailoperatorname) = null) select @temailoperatorname = null
   if (ltrim(@tdescription) = null) select @tdescription = null
   if (ltrim(@tcommand) = null) select @tcommand = null
   if (ltrim(@ttagadditionalinfo) = null) select @ttagadditionalinfo = null
   if (@ttagobjectid = 0) select @ttagobjectid = null
   if (@ttagobjecttype = 0) select @ttagobjecttype = null

    /* now verify the local variables */
    exec @verify = sp_verifytask @id, @tname, @server, @databasename,
         @tenabled,
         @tfreqtype,
         @tfreqinterval,
         @tfreqsubtype,
         @tfreqrelativeinterval,
         @tactivestartdate,
         @tactiveenddate out,
         @tactivestarttimeofday,
         @tactiveendtimeofday out,
         @tnextrundate out,
         @tnextruntime out,
         @trunpriority,
         @emailoperatorname, @temailoperatorid out,
         @tcommand,
         @tloghistcompletionlevel,
         @temailcompletionlevel
    if (@verify <> 0)
        return 1

    /* Get the username of the caller in the target database */
    if (@tsubsystem = 'TSQL')
    begin
        /* But first check if remote server name has been supplied and exit if so */
        if (@tserver <> null) and (upper(@tserver) <> upper(@@servername))
        begin
            raiserror(14253, -1, -1)
            return 1
        end
        select @tserver = null

        if (ltrim(rtrim(@tdatabasename)) = null)
            select @tdatabasename = 'master'
        delete from MSWork where spid = @@spid

        /* The insert into MSWork will always fail if the database is not found, but it does so with */
        /* a cryptic 208 ("xxx.dbo.sysusers not valid object") message, so pre-validate the database. */
        if (db_id(@tdatabasename) is null) begin
            raiserror (14211, -1, -1, @tdatabasename)
            return 1
        end

        /* This next step may fail with a 916 (and then an annoying 208), but that's */
            /* OK since it's good to know ahead of time that the task won't run */
        exec ('insert into MSWork(spid, value1) select @@spid, name from '+ @tdatabasename +'.dbo.sysusers where suid = suser_id()')
        if (@@error <> 0)
            return 1
        exec ('if not exists (select * from MSWork where spid = @@spid) insert into MSWork(spid, value1) select @@spid, name from '+
                @tdatabasename +'.dbo.sysusers where suid = (select altsuid from '+ @tdatabasename +'.dbo.sysalternates where suid = suser_id())')
        exec ('if not exists (select * from MSWork where spid = @@spid) insert into MSWork(spid, value1) select @@spid, name from '+ @tdatabasename +'.dbo.sysusers where name = ''guest''')
        select @usernameintargetdb = value1 from MSWork where spid = @@spid
        delete from MSWork where spid = @@spid

        /* Now check if the caller has supplied a permissible @username */
        if (suser_id() = 1) and ((upper(@username) = 'SA') OR (upper(@username) = 'DBO'))
            select @tusername = null
        else
        if (suser_id() <> 1) and (ltrim(rtrim(@username)) = null)
            select @tusername = @usernameintargetdb
        else
        /* Only the SA or the DBO of the target db can set a "not me" @username */
        if (suser_id() <> 1) and (upper(@usernameintargetdb) <> 'DBO') and (isnull(@tusername, '') <> @usernameintargetdb)
               begin
            raiserror(14247, -1, -1, @tusername, @usernameintargetdb, @tdatabasename, @tdatabasename)
            return 1
        end
    end

    /* all seems valid, so generate the update statement */
    if (@parameters <> NULL)
    begin
        update msdb.dbo.systasks
        set parameters = @parameters
        where id = @id
    end
    update msdb.dbo.systasks set
            name = @tname,
            subsystem = @tsubsystem,
            server = @tserver,
            username = @tusername,
            databasename = @tdatabasename,
            enabled = @tenabled,
            freqtype = @tfreqtype,
            freqinterval = @tfreqinterval,
            freqsubtype = @tfreqsubtype,
            freqsubinterval = @tfreqsubinterval,
            freqrelativeinterval = @tfreqrelativeinterval,
            freqrecurrencefactor = @tfreqrecurrencefactor,
            activestartdate = @tactivestartdate,
            activeenddate = @tactiveenddate,
            activestarttimeofday = @tactivestarttimeofday,
            activeendtimeofday = @tactiveendtimeofday,
            nextrundate = @tnextrundate,
            nextruntime = @tnextruntime,
            runpriority = @trunpriority,
            emailoperatorid = @temailoperatorid,
            retryattempts = @tretryattempts,
            retrydelay = @tretrydelay,
            command = @tcommand,
            loghistcompletionlevel = @tloghistcompletionlevel,
            emailcompletionlevel = @temailcompletionlevel,
            description = @tdescription,
            datemodified = getdate(),
            tagadditionalinfo = @ttagadditionalinfo,
            tagobjectid = @ttagobjectid,
            tagobjecttype = @ttagobjecttype
        where id = @id
   if (@@error <> 0)
      return 1

    /* If any scheduling stuff changed or the subsystem changed or the command changed, */
    /* clear the last/next run details.                                                 */
    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 or
            @subsystem is not null or
            @command is not null
            )
        update msdb.dbo.systasks set
        lastrundate = 0,
        lastruntime = 0,
        nextrundate = 0,
        nextruntime = 0,
        lastruncompletionlevel = 0,
        lastrunduration = 0,
        lastrunretries = 0
        where id = @id
   if (@@error <> 0)
      return 1

    return 0
go
/* end sp_updatetask */


/**************************************************************/
/* SP_DROPTASK                                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_droptask...'
go

create proc sp_droptask
 @name varchar(100) = null,
 @loginname varchar(30) = null,
 @id int = null
as
    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
    end

    /* First see if we should drop all tasks for a login. */
    declare @ownerid int, @uppersubsys varchar(30), @destserver varchar(30)
    if (@loginname is not null)
    begin
        select @ownerid = suser_id(@loginname)
        if (@ownerid is null)
        begin
            RAISERROR (14220, -1, -1, @loginname)
            return 1
        end
        if (suser_id() <> 1 and @ownerid <> suser_id())
        begin
            RAISERROR (14219, -1, -1)
            return 1
        end
        delete from msdb.dbo.syshistory where taskid in (select id from systasks where ownerloginid = @ownerid)
        delete from msdb.dbo.systasks where ownerloginid = @ownerid
        return 0
    end

    if (@name is not null)
       begin
       select @id = id, @ownerid = ownerloginid, @uppersubsys =
              UPPER(subsystem), @destserver = server from msdb.dbo.systasks
       where name = @name
         if (@id is null)
          begin
              RAISERROR (14222, -1, -1, @name)
              return 1
          end
       end
    else
       begin
       select @name = name, @ownerid = ownerloginid, @uppersubsys =
              UPPER(subsystem), @destserver = server from msdb.dbo.systasks
       where id = @id
         if (@name is null)
          begin
              declare @id_as_char varchar(10)
              select @id_as_char = convert(varchar, @id)
              RAISERROR (14222, -1, -1, @id_as_char)
              return 1
          end
       end

    if (suser_id() <> 1 and @ownerid <> suser_id() and NOT(@uppersubsys = 'DISTRIBUTION' and @@REMSERVER = @destserver))
    begin
        RAISERROR (14219, -1, -1)
        return 1
    end
    delete from msdb.dbo.syshistory where taskid = @id
    delete from msdb.dbo.systasks where id = @id
    return 0
go
/* end sp_droptask */


/**************************************************************/
/* SP_REASSIGNTASK                                            */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_reassigntask...'
go

create proc sp_reassigntask
  @taskname varchar(100) = null,
  @newloginname varchar(30),
  @oldloginname varchar(30) = null
as
    if (suser_id() <> 1)
    begin
        RAISERROR (14244, -1, -1)
        return 1
    end

    declare @oldid int, @newid int
    select @newid = suser_id(@newloginname)

    /* Got to have either task or oldlogin name (or both, though it's redundant). */
    if (@taskname is null and @oldloginname is null)
    begin
        RAISERROR (14255, -1, -1)
        return 1
    end

    /* Always check newid, but only check oldid if taskname is null. */
    if (@newid is null)
    begin
        RAISERROR (14220, -1, -1, @newloginname)
        return 1
    end

    if (@taskname is not null)
    begin
        if not exists (select * from systasks where name = @taskname)
        begin
            RAISERROR (14222, -1, -1, @taskname)
            return 1
        end
        update msdb.dbo.systasks set ownerloginid = @newid    where name = @taskname
        return 0
    end

    select @oldid = suser_id(@oldloginname)
    if (@oldid is null)
    begin
        RAISERROR (14220, -1, -1, @oldloginname)
        return 1
    end

    update msdb.dbo.systasks set ownerloginid = @newid    where ownerloginid = @oldid
   if (@@error <> 0)
      return 1
    return 0
go
/* end sp_reassigntask */

/**************************************************************/
/* SP_UNIQUETASKNAME                                          */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_uniquetaskname...'
go

create proc sp_uniquetaskname
@seed varchar(92)
as
    if exists (select * from systasks)
        select @seed + convert(varchar(7), (select min(id + 1) from systasks where
            @seed + convert(varchar(7), id + 1) not in (select name from systasks)))
    else
        select @seed + '1'
go
/* end sp_uniquetaskname */

/**************************************************************/
/* SP_HELPTASK                                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_helptask...'
go

create proc sp_helptask
 @taskname varchar(100) = null,
 @taskid int = null,
 @loginname varchar(30) = null,
 @operatorname varchar(30) = null,
 @subsystem varchar(30) = null,
 @mode varchar(10) = null            /* full or quick (default) */
as
    declare @operatorid int, @loginid int

    /* see if they wanted to qualify by owner; set @loginid if so. */
    if (@loginname is not null)
    begin
        if (suser_name() not in ("sa", "repl_subscriber", "repl_publisher", @loginname))
        begin
          RAISERROR(14254, -1, -1)
          return 1
        end
        select @loginid = suser_id(@loginname)
        if (@loginid is null)
        begin
            RAISERROR (14220, -1, -1, @loginname)
            return 1
        end
    end

    /* see if they wanted to qualify by operator; set @operatorid if so. */
    if (@operatorname is not null)
    begin
        select @operatorid = id from msdb.dbo.sysoperators where name = @operatorname
        if (@operatorid is null)
        begin
            RAISERROR (14221, -1, -1, @operatorname)
            return 1
        end
    end

    /* return task info based on params. if full specified, this info format is relied upon by */
    /* sqlole and starfighter; do not change it. */
    if (@mode = 'full')
    begin
        select t.name,
            t.id,
            t.subsystem,
            t.server,
            t.username,
            ownerloginname = suser_name(t.ownerloginid),
            t.databasename,
            t.enabled,
            t.freqtype,
            t.freqinterval,
            t.freqsubtype,
            t.freqsubinterval,
            t.freqrelativeinterval,
            t.freqrecurrencefactor,
            t.activestartdate,
            t.activeenddate,
            t.activestarttimeofday,
            t.activeendtimeofday,
            t.lastrundate,
            t.lastruntime,
            t.nextrundate,
            t.nextruntime,
            t.runpriority,
            emailoperatorname = a.name,
            t.retryattempts,
            t.retrydelay,
            t.datecreated,
            t.datemodified,
            t.command,
            t.lastruncompletionlevel,
            t.lastrunduration,
            t.lastrunretries,
            t.loghistcompletionlevel,
            t.emailcompletionlevel,
            t.description,
         t.tagadditionalinfo,
         t.tagobjectid,
         t.tagobjecttype
        from msdb.dbo.systasks_view t, msdb.dbo.sysoperators a where a.id =* t.emailoperatorid
                and (@loginid is null or t.ownerloginid = @loginid)
                and (@subsystem is null or t.subsystem like @subsystem)
                and (@operatorid is null or t.emailoperatorid = @operatorid)
                and (@taskname is null or t.name like @taskname)
                and (@taskid is null or @taskid = t.id)
            order by t.name
    end else begin
        select
            name = substring(t.name, 1, 20),
            t.id,
            subsystem = substring(t.subsystem, 1, 15),
            server = substring(t.server, 1, 20),
            username = substring(t.username, 1, 20),
            dbname = substring(t.databasename, 1, 20),
            t.enabled
        from msdb.dbo.systasks_view t
            where (@loginid is null or t.ownerloginid = @loginid)
                and (@subsystem is null or t.subsystem like @subsystem)
                and (@operatorid is null or t.emailoperatorid = @operatorid)
                and (@taskname is null or t.name like @taskname)
                and (@taskid is null or @taskid = t.id)
            order by t.name
    end
go
/* end sp_helptask */


/**************************************************************/
/* TASKREFRESH_INS                                            */
/**************************************************************/
PRINT ''
PRINT 'Creating trigger taskrefresh_ins...'
go

/* SYSTASKS INSERT TRIGGER */

CREATE TRIGGER taskrefresh_ins
on msdb.dbo.systasks
for insert as
begin
declare @taskID int
declare @lastID int
select @lastID = 0
set rowcount 1
loop:

    Select @taskID = id from inserted where id > @lastID order by id
    if @taskID > 0
    BEGIN
        EXEC master.dbo.sp_schedulersignal 'R', @taskID, 'I'
        select @lastID = @taskID
        select @taskID = 0
        goto loop
    END
end
go


/**************************************************************/
/* TASKREFRESH_DEL                                            */
/**************************************************************/
PRINT ''
PRINT 'Creating trigger taskrefresh_del...'
go

CREATE TRIGGER taskrefresh_del
on msdb.dbo.systasks
for delete as
begin
declare @taskID int
declare @lastID int
select @lastID = 0
set rowcount 1
loop:

    Select @taskID = id from deleted where id > @lastID order by id
    if @taskID > 0
    BEGIN
        EXEC master.dbo.sp_schedulersignal 'R', @taskID, 'D'
        select @lastID = @taskID
        select @taskID = 0
        goto loop
    END

/* Now, clean up any dangling references in sysalerts to the deleted task(s) */
set rowcount 0
update msdb.dbo.sysalerts
set task_id = null
from deleted
where msdb.dbo.sysalerts.task_id  = deleted.id
end
go


/**************************************************************/
/* TASKREFRESH_UPD                                            */
/**************************************************************/
PRINT ''
PRINT 'Creating trigger taskrefresh_upd...'
go

CREATE TRIGGER taskrefresh_upd
on msdb.dbo.systasks
for update as
begin

declare @taskID int
declare @lastID int


 if update (id                      ) or
    update (name                  ) or
    update (subsystem             ) or
    update (server                ) or
    update (username              ) or
    update (ownerloginid          ) or
    update (databasename          ) or
    update (enabled               ) or

    update (freqtype              ) or
    update (freqinterval          ) or
    update (freqsubtype           ) or
    update (freqsubinterval       ) or
    update (freqrelativeinterval  ) or
    update (freqrecurrencefactor  ) or
    update (activestartdate       ) or
    update (activeenddate         ) or
    update (activestarttimeofday  ) or
    update (activeendtimeofday    ) or

    update (runpriority           ) or
    update (emailoperatorid       ) or
    update (retryattempts         ) or
    update (retrydelay            ) or
    update (datecreated           ) or
    update (command               ) or

    update (loghistcompletionlevel) or
    update (emailcompletionlevel  ) or

    update (description           )

 begin


  select @lastID = 0
  set rowcount 1
  loop:

    Select @taskID = inserted.id
    from inserted , deleted  where inserted.id > @lastID and
    (inserted.id = deleted.id) and
    ((inserted.name                   <> deleted.name                   ) or
     (inserted.subsystem              <> deleted.subsystem             ) or
     (inserted.enabled                <> deleted.enabled               ) or
     (inserted.ownerloginid           <> deleted.ownerloginid          ) or

     (isnull(inserted.server, "")         <> isnull(deleted.server, "")       ) or
     (isnull(inserted.username, "")       <> isnull(deleted.username, "")     ) or
     (isnull(inserted.databasename, "")   <> isnull(deleted.databasename, "") ) or
     (isnull(inserted.emailoperatorid, 0) <> isnull(deleted.emailoperatorid, 0)) or
     (isnull(inserted.command, "")        <> isnull(deleted.command, "")      ) or
     (isnull(inserted.description, "")    <> isnull(deleted.description, "") or

     (inserted.freqtype               <> deleted.freqtype              ) or
     (inserted.freqinterval           <> deleted.freqinterval          ) or
     (inserted.freqsubtype            <> deleted.freqsubtype           ) or
     (inserted.freqsubinterval        <> deleted.freqsubinterval       ) or
     (inserted.freqrelativeinterval   <> deleted.freqrelativeinterval  ) or
     (inserted.freqrecurrencefactor   <> deleted.freqrecurrencefactor  ) or
     (inserted.activestartdate        <> deleted.activestartdate       ) or
     (inserted.activeenddate          <> deleted.activeenddate         ) or
     (inserted.activestarttimeofday   <> deleted.activestarttimeofday  ) or
     (inserted.activeendtimeofday     <> deleted.activeendtimeofday    ) or

     (inserted.runpriority            <> deleted.runpriority           ) or
     (inserted.retryattempts          <> deleted.retryattempts         ) or
     (inserted.retrydelay             <> deleted.retrydelay            ) or
     (inserted.datecreated            <> deleted.datecreated           ) or
     (inserted.loghistcompletionlevel <> deleted.loghistcompletionlevel) or
     (inserted.emailcompletionlevel   <> deleted.emailcompletionlevel  ))

    )  order by inserted.id

    if @taskID > 0
    BEGIN
        EXEC master.dbo.sp_schedulersignal 'R', @taskID, 'U'
        select @lastID = @taskID
        select @taskID = 0
        goto loop
    END
 End
end
go


/**************************************************************/
/* SP_HELPHISTORY                                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_helphistory...'
go

create proc sp_helphistory
 @taskname                varchar(100) = null,
 @taskid                int = null,
 @eventid                 int = null,
 @messageid                int = null,
 @severity                int = null,
 @source                varchar(30) = null,
 @category                varchar(30) = null,
 @startdate                int = null,
 @enddate                int = null,
 @starttime                int = null,
 @endtime                int = null,
 @minimumtimesskipped    int = null,
 @minimumrunduration    int = null, /* hhmmss */
 @runstatusmask            int = null, /* SQLOLE_EVENT_TYPES for events; SQLOLE_COMPLETION_STATUS for tasks */
 @minimumretries        int = null,
 @oldestfirst            int = null,
 @mode varchar(10) = null            /* full or quick (default) */
as
    /* validate taskid if set, else task name if set. */
    if (@taskname is not null)
    begin
        select @taskid = id from msdb.dbo.systasks where name = @taskname
        if (@taskid is null)
        begin
            begin
                RAISERROR (14222, -1, -1, @taskname)
                return 1
            end
        end
    end else if (@taskid is not null)
    begin
        if not exists (select * from msdb.dbo.systasks where id = @taskid)
        begin
            RAISERROR (14223, -1, -1, @taskid)
            return 1
        end
    end

    /* ugly tsql hackaround to get sort-by-date on same statement.  normally we'd order by */
    /* most recent event first, i.e. desc. */
    declare @orderby int
    select @orderby = -1
    if (@oldestfirst is not null and @oldestfirst > 0)
        select @orderby = 1

    /* return task info based on params. if full specified, this info format is relied upon by */
    /* sqlole and starfighter; do not change it. */
    if (@mode = 'full')
    begin
        select h.id,
            h.eventid,
            h.messageid,
            h.severity,
            taskname = t.name,
            h.source,
            h.category,
            h.runstatus,
            h.rundate,
            h.runtime,
            h.runduration,
            h.reviewstatus,
            emailoperatorname = a.name,
            h.retries,
            h.comments,
            h.timesskipped
        from msdb.dbo.syshistory h, msdb.dbo.systasks_view t, msdb.dbo.sysoperators a
        where t.id = h.taskid and a.id =* h.emailoperatorid
            and (@taskid is null or @taskid = h.taskid)
            and (@eventid is null or @eventid = h.eventid)
            and (@messageid is null or @messageid = h.messageid)
            and (@severity is null or @severity = h.severity)
            and (@source is null or @source = h.source)
            and (@category is null or @category = h.category)
            and (@startdate is null or @startdate <= h.rundate)
            and (@enddate is null or @enddate >= h.rundate)
            and (@starttime is null or @starttime <= h.runtime)
            and (@endtime is null or @endtime >= h.runtime)
            and (@minimumtimesskipped is null or @minimumtimesskipped <= h.timesskipped)
            and (@minimumrunduration is null or @minimumrunduration <= h.runduration)
            and (@runstatusmask is null or @runstatusmask = 0 or @runstatusmask & h.runstatus <> 0)
            and (@minimumretries is null or @minimumretries <= h.retries)
        order by (h.id * @orderby)
    end else begin
        select
            taskname = t.name,
            h.source,
            h.runstatus,
            h.rundate,
            h.runtime,
            h.runduration,
            emailoperatorname = substring(a.name, 1, 10),
            h.retries
        from msdb.dbo.syshistory h, msdb.dbo.systasks_view t, msdb.dbo.sysoperators a
        where t.id = h.taskid and a.id =* h.emailoperatorid
            and (@taskid is null or @taskid = h.taskid)
            and (@eventid is null or @eventid = h.eventid)
            and (@messageid is null or @messageid = h.messageid)
            and (@severity is null or @severity = h.severity)
            and (@source is null or @source = h.source)
            and (@category is null or @category = h.category)
            and (@startdate is null or @startdate <= h.rundate)
            and (@enddate is null or @enddate >= h.rundate)
            and (@starttime is null or @starttime <= h.runtime)
            and (@endtime is null or @endtime >= h.runtime)
            and (@minimumtimesskipped is null or @minimumtimesskipped <= h.timesskipped)
            and (@minimumrunduration is null or @minimumrunduration <= h.runduration)
            and (@runstatusmask is null or @runstatusmask = 0 or @runstatusmask & h.runstatus <> 0)
            and (@minimumretries is null or @minimumretries <= h.retries)
        order by (h.id * @orderby)
    end
go
/* end sp_helphistory */


/**************************************************************/
/* SP_PURGEHISTORY                                            */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_purgehistory...'
go

create proc sp_purgehistory
 @taskname                varchar(100) = null,
 @taskid                int = null,
 @eventid                 int = null,
 @messageid                int = null,
 @severity                int = null,
 @source                varchar(30) = null,
 @category                varchar(30) = null,
 @startdate                int = null,
 @enddate                int = null,
 @starttime                int = null,
 @endtime                int = null,
 @minimumtimesskipped    int = null,
 @minimumrunduration    int = null, /* hhmmss */
 @runstatusmask            int = null, /* SQLOLE_EVENT_TYPES for events; SQLOLE_COMPLETION_STATUS for tasks */
 @minimumretries        int = null
as
    /* Validate taskname if set, else taskid if set (one is required if non-sa). */
    if (@taskname is not null)
    begin
        select @taskid = id from msdb.dbo.systasks where name = @taskname
        if (@taskid is null)
        begin
            RAISERROR (14222, -1, -1, @taskname)
            return 1
        end
    end
    else if (@taskid is not null)
    begin
        if not exists (select * from msdb.dbo.systasks where id = @taskid)
        begin
            RAISERROR (14223, -1, -1, @taskid)
            return 1
        end
    end
    else if (suser_id() <> 1)
    begin
        RAISERROR (14224, -1, -1)
        return 1
    end

    /* If not sa, we have @taskid set.  Make sure this guy is the task owner, or he can't do this. */
    if (suser_id() <> 1 and not exists (select * from msdb.dbo.systasks where id = @taskid and ownerloginid = suser_id()))
    begin
        RAISERROR (14224, -1, -1)
        return 1
    end

    delete msdb.dbo.syshistory
            where (@taskid is null or @taskid = taskid)
            and (@eventid is null or @eventid = eventid)
            and (@messageid is null or @messageid = messageid)
            and (@severity is null or @severity = severity)
            and (@source is null or @source = source)
            and (@category is null or @category = category)
            and (@startdate is null or @startdate <= rundate)
            and (@enddate is null or @enddate >= rundate)
            and (@starttime is null or @starttime <= runtime)
            and (@endtime is null or @endtime >= runtime)
            and (@minimumtimesskipped is null or @minimumtimesskipped <= timesskipped)
            and (@minimumrunduration is null or @minimumrunduration <= runduration)
            and (@runstatusmask is null or @runstatusmask = 0 or @runstatusmask & runstatus <> 0)
            and (@minimumretries is null or @minimumretries <= retries)
go
/* end sp_purgehistory */


/**************************************************************/
/* SP_SCHEDULERREFRESH                                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_schedulerrefresh...'
go

create proc sp_schedulerrefresh @id int = null as
select
t.id,
t.name,
subsystem,
server,
username,
l.name,
l.password,
databasename,
freqtype,
freqinterval,
freqsubtype,
freqsubinterval,
freqrelativeinterval,
freqrecurrencefactor,
activestartdate,
activeenddate,
activestarttimeofday,
activeendtimeofday,
lastrundate,
lastruntime,
nextrundate,
nextruntime,
runpriority,
a.email_address,
emailcompletionlevel,
retryattempts,
retrydelay,
loghistcompletionlevel,
command,
t.enabled,
emailoperatorid
from msdb.dbo.systasks t,master.dbo.syslogins l,msdb.dbo.sysoperators a
where (@id=null or (@id<>null and t.id=@id)) and
      l.suid=ownerloginid and
      a.id=*emailoperatorid
order by t.id
go


/**************************************************************/
/* SP_SCHEDULERLOG                                            */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_schedulerlog...'
go

create proc sp_schedulerlog @eventid int,@messageid int,@severity int,@taskid int,@source varchar(30),
                                @category varchar(30),@runstatus int,@rundate int,@runtime int,@runduration int,
                                @reviewstatus varchar(32),@emailoperatorid smallint,@retriesattempted int,
                                @comments varchar(255),@timesskipped int as

    insert into msdb.dbo.syshistory (eventid,messageid,severity,taskid,source,category,runstatus,rundate,runtime,
                            runduration,reviewstatus,emailoperatorid,retries,comments,timesskipped)
        values (@eventid,@messageid,@severity,@taskid,@source,@category,@runstatus,@rundate,@runtime,
                @runduration,@reviewstatus,@emailoperatorid,@retriesattempted,@comments,@timesskipped)
go


/**************************************************************/
/* SP_MSCHECKFOROWNEDTASKS                                    */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_MScheckforownedtasks...'
go

CREATE PROCEDURE sp_MScheckforownedtasks
(
@tablename varchar(92),
@loginname varchar(30)
)
AS
/*
** This procedure is called by sp_droplogin to check if the login being dropped
** still owns tasks.  The return value (the number of tasks owned) is passed back
** via the supplied table name [this cumbersome approach is necessary because
** sp_MScheckforownedtasks is invoked via an EXEC()].
*/
BEGIN
  IF EXISTS (SELECT * FROM sysobjects WHERE (name = 'systasks') AND (sysstat & 0xf = 3))
    EXEC ('INSERT INTO ' + @tablename + ' SELECT count(*) FROM systasks WHERE ownerloginid = suser_id(''' + @loginname + ''')')
END
go


/**************************************************************/
/* SP_MSTASKPARAMETERS                                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_MStaskparameters...'
go

CREATE PROCEDURE sp_MStaskparameters
    @taskname varchar(100)
as
    /* Return a text column as multiple readtexts of maxcol length */
    if not exists (select * from msdb.dbo.systasks where name = @taskname) begin
        RAISERROR (14222, 11, -1, @taskname)
        return 1
    end

    declare @val varbinary(16), @len int, @ii int, @chunk int
    select @val = textptr(parameters), @len = datalength(parameters) from msdb.dbo.systasks where name = @taskname
    select @ii = 0, @chunk = 255

    /* Get all the rows of an maxcol size */
    while @len > @chunk begin
        readtext msdb.dbo.systasks.parameters @val @ii @chunk
        select @ii = @ii + @chunk, @len = @len - @chunk
    end

    /* Get the last chunk */
    if (@len > 0)
        readtext msdb.dbo.systasks.parameters @val @ii @len
    return 0
go

/**************************************************************/
/* SP_RUNTASK                                                 */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_runtask...'
go

CREATE PROCEDURE sp_runtask
@taskname varchar(100) = NULL,
@taskid   int          = NULL
AS
BEGIN
  DECLARE @msg    varchar(255)
  DECLARE @retval int

  /* We must have either a taskid or a taskname */
  IF ((@taskid = NULL) AND (@taskname = NULL)) OR
     ((@taskid != NULL) AND (@taskname != NULL))
  BEGIN
    PRINT 'USAGE: sp_runtask <task name> | @taskid = <task id>'
    RETURN(1)
  END

  /* Is the taskname valid? */
  IF (@taskid = NULL)
  BEGIN
    SELECT @taskid = id
    FROM msdb.dbo.systasks
    WHERE name = @taskname
    IF (@taskid = NULL)
    BEGIN
      SELECT @msg = 'Task ''' + @taskname + ''' does not exist.'
      PRINT @msg
      RETURN(1)
    END
  END
  ELSE
  /* Is the taskid valid? */
  BEGIN
    SELECT @taskname = name
    FROM msdb.dbo.systasks
    WHERE id = @taskid
    IF (@taskname = NULL)
    BEGIN
      SELECT @msg = 'Task #' + convert(varchar(10), @taskid) +' does not exist.'
      PRINT @msg
      RETURN(1)
    END
  END

  /* Run (start) the task */
  EXECUTE @retval = sp_schedulersignal 'O', @taskid
  IF (@retval = 0)
  BEGIN
    SELECT @msg = 'Task #' + convert(varchar(10), @taskid) + ' started successfully.'
    PRINT @msg
    RETURN(@retval)
  END
END
go


/************************************************************************/
/* Systasks username/databasename upgrade (TSQL and CmdExec tasks only) */
/************************************************************************/
PRINT ''
PRINT 'Upgrading systasks data...'
go

set nocount on
declare @msg varchar(255)
declare @row_count int

/* CmdExec Tasks (because of UI change) */
update systasks
set username = null, databasename = null
where subsystem = 'CmdExec'
  and ((username <> null) or (databasename <> null))
select @row_count = @@rowcount
if (@row_count > 0)
begin
    select @msg = convert(varchar(5), @row_count) + ' ''CmdExec'' Tasks were upgraded.'
    print @msg
end
go

/* TSQL Tasks */
declare systasks_upgrade cursor
for select id, ownerloginid, databasename, username, subsystem from systasks
for update of databasename, username

create table #username (username varchar(30))
go

set nocount on
declare @id int
declare @ownerloginid smallint
declare @databasename varchar(30)
declare @username varchar(30)
declare @usernameintargetdb varchar(30)
declare @subsystem varchar(30)
declare @msg varchar(255)
declare @SZownerloginid varchar(5)

open systasks_upgrade

fetch next from systasks_upgrade into @id, @ownerloginid, @databasename, @username, @subsystem
while (@@fetch_status <> -1)
begin
    truncate table #username
    if (@subsystem = 'TSQL')
    begin
        if not exists (select name from master.dbo.sysdatabases where name = isnull(@databasename, ''))
        begin
            select @msg = 'Updating database for ''TSQL'' task ' + convert(varchar, @id) + ' from ' + isnull(@databasename, '(null)') +' to master.'
            print @msg
            select @databasename = 'master'
            update systasks set databasename = @databasename where id = @id
        end
        select @SZownerloginid = convert(varchar(5), @ownerloginid)
        exec ('insert into #username(username) select name from '+ @databasename +'.dbo.sysusers where suid = '+ @SZownerloginid)
        exec ('if not exists (select * from #username) insert into #username(username) select name from '+ @databasename +'.dbo.sysusers where suid = (select altsuid from '+ @databasename +'.dbo.sysalternates where suid = '+ @SZownerloginid + ')')
        exec ('if not exists (select * from #username) insert into #username(username) select name from '+ @databasename +'.dbo.sysusers where name = ''guest''')
        exec ('if not exists (select * from #username) insert into #username(username) select ''No Available Username''')
        select @usernameintargetdb = username from #username
        if (@ownerloginid <> 1) and (upper(@usernameintargetdb) <> 'DBO') and (isnull(@username, '') <> @usernameintargetdb)
        begin
            select @msg = 'Updating username for ''TSQL'' task ' + convert(varchar, @id) + ' from ' + isnull(@username, '(null)') +' to ' + @usernameintargetdb + '.'
            print @msg
            update systasks set username = @usernameintargetdb where id = @id
        end
    end
    fetch next from systasks_upgrade into @id, @ownerloginid, @databasename, @username, @subsystem
end
go

drop table #username
deallocate systasks_upgrade
go

/************************************************************************/
/* Sysalerts upgrade (add 'flags' column if not already present)        */
/************************************************************************/
SET NOCOUNT ON
IF NOT EXISTS (SELECT *
               FROM msdb.dbo.syscolumns
               WHERE id = object_id('dbo.sysalerts')
                 AND name = 'flags')
BEGIN
  PRINT ''
  PRINT 'Upgrading sysalerts definition...'
  ALTER TABLE msdb.dbo.sysalerts ADD flags INT NULL
  EXEC ('UPDATE msdb.dbo.sysalerts SET flags = 0')
END
go



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

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

/**************************************************************/
/* NOTE: The new sysmessages needed are added by MESSAGES.SQL */
/**************************************************************/

DUMP TRANSACTION msdb WITH NO_LOG
go

/**************************************************************/
/* DROPS                                                      */
/**************************************************************/
PRINT ''
PRINT 'Dropping Alert/Operator Procedures and Triggers...'
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.NewOrChangedNotification')
            AND sysstat & 0xf = 8)
BEGIN
  DROP TRIGGER NewOrChangedNotification
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.RemovedNotification')
            AND sysstat & 0xf = 8)
BEGIN
  DROP TRIGGER RemovedNotification
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_validatealert')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_validatealert
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_addalert')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_addalert
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_updatealert')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_updatealert
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_dropalert')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_dropalert
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_validateoperator')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_validateoperator
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_addoperator')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_addoperator
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_updateoperator')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_updateoperator
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_dropoperator')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_dropoperator
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_addnotification')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_addnotification
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_updatenotification')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_updatenotification
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_dropnotification')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_dropnotification
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_helpalert')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_helpalert
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_helpoperator')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_helpoperator
END
go

IF EXISTS(SELECT *
          FROM sysobjects
          WHERE id = object_id('dbo.sp_helpnotification')
            AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_helpnotification
END
go

DUMP TRANSACTION msdb WITH NO_LOG
go

/**************************************************************/
/* SP_VALIDATEALERT                                           */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_validatealert...'
go
CREATE PROCEDURE sp_validatealert
(
@new_name                      varchar(60),
@message_id                    int,
@severity                      int,
@enabled                       tinyint,
@delay_between_responses       int,
@notification_message          varchar(255),
@include_event_description_in  varchar(10),
@database_name                 varchar(30),
@event_description_keyword     varchar(100),
@task_name                     varchar(100),
@occurrence_count              int,
@raise_snmp_trap               tinyint
)
AS
BEGIN
  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Is the NewName unique? */
  IF EXISTS (SELECT *
             FROM msdb.dbo.sysalerts
             WHERE name = @new_name)
  BEGIN
    RAISERROR(14528, 16, 1)
    RETURN(1)
  END

  /* Remap NULL MessageID/Severity to zeros prior to validation */
  IF (@message_id = NULL) SELECT @message_id = 0
  IF (@severity = NULL) SELECT @severity = 0
  /* Has the user supplied MessageID OR Severity? */
  IF NOT ((@message_id = 0) AND (@severity NOT IN (0, NULL)))
  AND NOT ((@severity = 0) AND (@message_id NOT IN (0, NULL)))
  BEGIN
    RAISERROR(14500, 16, 1)
    RETURN(1)
  END

  /* Valid TaskName? */
  IF (ltrim(rtrim(@task_name)) <> NULL) AND (NOT EXISTS(SELECT *
                                                        FROM msdb.dbo.systasks
                                                        WHERE name = ltrim(rtrim(@task_name))
                                                        HAVING count(*) = 1))
  BEGIN
    RAISERROR(14501, 16, 1)
    RETURN(1)
    /* Is this task an 'On Demand' task? */
    IF NOT EXISTS(SELECT *
                  FROM msdb.dbo.systasks
                  WHERE (name = ltrim(rtrim(@task_name)))
                    AND (freqtype = 2))
    BEGIN
      RAISERROR(14531, 16, 1)
      RETURN(1)
    END
  END

  /* Valid Severity? */
  IF ((@severity < 0) OR (@severity > 25)) AND (@severity NOT IN (110, 120, 130, 140))
  BEGIN
    RAISERROR(14502, 16, 1)
    RETURN(1)
  END

  /* Valid MessageID? */
  IF (@message_id < 0)
  BEGIN
    RAISERROR(14518, 16, 1)
    RETURN(1)
  END

  /* Is it legal to set an alert on this MessageID ? */
  IF (@message_id in (4002, 1204))
  BEGIN
    RAISERROR(14533, 16, 1, @message_id)
    RETURN(1)
  END

  /* Valid IncludeEventDescriptionIn? */
  IF (@include_event_description_in NOT IN ('EMAIL', 'PAGER', 'BOTH', 'NONE'))
  BEGIN
    RAISERROR(14503, 16, 1)
    RETURN(1)
  END

  /* DelayBetweenResponses must be > 0 */
  IF (@delay_between_responses < 0)
  BEGIN
    RAISERROR(14504, 16, 1)
    RETURN(1)
  END

  /* OccurrenceCount must be > 0 */
  IF (@occurrence_count < 0)
  BEGIN
    RAISERROR(14505, 16, 1)
    RETURN(1)
  END

  /* Enabled must be 0 or 1 */
  IF (@enabled NOT IN (0, 1))
  BEGIN
    RAISERROR(14516, 16, 1)
    RETURN(1)
  END

  /* RaiseSNMPTrap must be 0 or 1 */
  IF (@raise_snmp_trap NOT IN (0, 1))
  BEGIN
    RAISERROR(14532, 16, 1)
    RETURN(1)
  END
END
go


/**************************************************************/
/* SP_ADDALERT                                                */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_addalert...'
go
CREATE PROCEDURE sp_addalert
(
@name                          varchar(60),
@message_id                    int,
@severity                      int,
@enabled                       tinyint = 1,
@delay_between_responses       int = 0,
@notification_message          varchar(255) = NULL,
@include_event_description_in  varchar(10) = 'NONE',
@database_name                 varchar(30) = NULL,
@event_description_keyword     varchar(100) = NULL,
@task_name                     varchar(100) = NULL,
@raise_snmp_trap               tinyint = 0
)
AS
BEGIN
  /* Turn empty strings into NULLs */
  IF (ltrim(@notification_message) = null) SELECT @notification_message = NULL
  IF (ltrim(@include_event_description_in) = null) SELECT @include_event_description_in = NULL
  IF (ltrim(@database_name) = null) SELECT @database_name = NULL
  IF (ltrim(@event_description_keyword) = null) SELECT @event_description_keyword = NULL
  IF (ltrim(@task_name) = null) SELECT @task_name = NULL

  DECLARE @event_source              varchar(30)
  DECLARE @event_category_name       varchar(30)
  DECLARE @event_id                  varchar(20)
  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_email_notification    int
  DECLARE @has_pager_notification    int
  DECLARE @task_id                   int
  DECLARE @include_event_desc_code   tinyint

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Hard-code the new Alert defaults */
  SELECT @event_source = 'MSSQLServer'
  SELECT @event_category_name = NULL
  SELECT @event_id = NULL
  SELECT @last_occurrence_date = NULL
  SELECT @last_occurrence_time = NULL
  SELECT @last_notification_date = NULL
  SELECT @last_notification_time = NULL
  SELECT @occurrence_count = 0
  SELECT @count_reset_date = NULL
  SELECT @count_reset_time = NULL
  SELECT @has_email_notification = 0
  SELECT @has_pager_notification = 0
  SELECT @task_id = NULL

  /* Convert the include_event_description_in parameter to upper case */
  SELECT @include_event_description_in = upper(@include_event_description_in)

  /* Validate the Alert */
  DECLARE @return_code tinyint
  EXEC @return_code = sp_validatealert
  @name,
  @message_id,
  @severity,
  @enabled,
  @delay_between_responses,
  @notification_message,
  @include_event_description_in,
  @database_name,
  @event_description_keyword,
  @task_name,
  @occurrence_count,
  @raise_snmp_trap
  IF (@return_code <> 0) RETURN(1)

  /* Does this Alert already exist? */
  DECLARE @duplicate_id varchar(10)
  SELECT @duplicate_id = '(unknown)'
  SELECT @duplicate_id = convert(varchar(10), id)
  FROM msdb.dbo.sysalerts
  WHERE ((message_id = @message_id) OR (severity = @severity))
    AND (database_name = @database_name)
    AND (event_description_keyword = @event_description_keyword)
  IF (@duplicate_id <> '(unknown)')
  BEGIN
    RAISERROR(14506, 16, 1, @duplicate_id)
    RETURN(1)
  END

  /*Convert the TaskName into an ID */
  IF (@task_name <> NULL)
  BEGIN
    SELECT @task_id = id
    FROM msdb.dbo.systasks
    WHERE name = ltrim(rtrim(@task_name))
  END

  /* Convert IncludeEventDescriptionIn to a code */
  IF (@include_event_description_in = 'NONE') SELECT @include_event_desc_code = 0
  IF (@include_event_description_in = 'EMAIL') SELECT @include_event_desc_code = 1
  IF (@include_event_description_in = 'PAGER') SELECT @include_event_desc_code = 2
  IF (@include_event_description_in = 'BOTH') SELECT @include_event_desc_code = 3

  /* Convert zero MessageID/Severity into NULLs */
  IF (@message_id = 0) SELECT @message_id = NULL
  IF (@severity = 0) SELECT @severity = NULL

  /* Finally, do the actual INSERT */
  INSERT INTO msdb.dbo.sysalerts
  (
  name,
  event_source,
  event_categoryname,
  event_id,
  message_id,
  severity,
  enabled,
  delay_between_notifications,
  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,
  task_id,
  has_email_notification,
  has_pager_notification,
  flags
  )
  VALUES
  (
  @name,
  @event_source,
  @event_category_name,
  @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_desc_code,
  @database_name,
  @event_description_keyword,
  @occurrence_count,
  @count_reset_date,
  @count_reset_time,
  @task_id,
  @has_email_notification,
  @has_pager_notification,
  @raise_snmp_trap
  )

  /*
  ** Notify the SQLExecutive of the change. This xp causes a complete refresh (the only kind
  ** of refresh for Alerts) of the Schedulers sysalerts cache.
  */
  EXEC master.dbo.sp_schedulersignal 'A'
  RETURN(0)
END
go


/**************************************************************/
/* SP_UPDATEALERT                                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_updatealert...'
go
CREATE PROCEDURE sp_updatealert
(
@name                          varchar(60),
@new_name                      varchar(60) = NULL,
@enabled                       tinyint = NULL,
@message_id                    int = NULL,
@severity                      int = NULL,
@delay_between_responses       int = NULL,
@notification_message          varchar(255) = NULL,
@include_event_description_in  varchar(10) = NULL,
@database_name                 varchar(30) = NULL,
@event_description_keyword     varchar(100) = NULL,
@task_name                     varchar(100) = NULL,
@occurrence_count              int = NULL,
@count_reset_date              int = NULL,
@count_reset_time              int = NULL,
@last_occurrence_date          int = NULL,
@last_occurrence_time          int = NULL,
@last_response_date            int = NULL,
@last_response_time            int = NULL,
@raise_snmp_trap               tinyint = NULL
)
AS
BEGIN
  DECLARE @include_event_desc_code  tinyint
  DECLARE @task_id                  int
  DECLARE @flags                    int

  SELECT @task_id = NULL

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Does this Alert exist? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysalerts
                WHERE name = @name)
  BEGIN
    RAISERROR(14507, 16, 1)
    RETURN(1)
  END

  /* Convert the @IncludeEventDescriptionIn parameter to upper case */
  IF (@include_event_description_in <> NULL) SELECT @include_event_description_in = upper(@include_event_description_in)

  BEGIN TRANSACTION
    IF (@enabled = NULL) SELECT @enabled = enabled FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@message_id = NULL) SELECT @message_id = message_id FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@severity = NULL) SELECT @severity = severity FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@delay_between_responses = NULL) SELECT @delay_between_responses = delay_between_notifications FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@notification_message = NULL) SELECT @notification_message = notification_message FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@include_event_description_in = NULL) SELECT @include_event_desc_code = include_event_description FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@database_name = NULL) SELECT @database_name = database_name FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@event_description_keyword = NULL) SELECT @event_description_keyword = event_description_keyword FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@task_name = NULL) SELECT @task_id = st.id FROM msdb.dbo.sysalerts sa, msdb.dbo.systasks st WHERE (sa.task_id = st.id) AND (sa.name = @name)
    IF (@occurrence_count = NULL) SELECT @occurrence_count = occurrence_count FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@count_reset_date = NULL) SELECT @count_reset_date = count_reset_date FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@count_reset_time = NULL) SELECT @count_reset_time = count_reset_time FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@last_occurrence_date = NULL) SELECT @last_occurrence_date = last_occurrence_date FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@last_occurrence_time = NULL) SELECT @last_occurrence_time = last_occurrence_time FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@last_response_date = NULL) SELECT @last_response_date = last_response_date FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@last_response_time = NULL) SELECT @last_response_time = last_response_time FROM msdb.dbo.sysalerts WHERE name = @name
    IF (@raise_snmp_trap = NULL) SELECT @raise_snmp_trap = flags & 0x1 FROM msdb.dbo.sysalerts WHERE name = @name
    SELECT @flags = flags FROM msdb.dbo.sysalerts WHERE name = @name

    /* Turn empty strings into NULLs */
    IF (ltrim(@new_name) = null) SELECT @new_name = NULL
    IF (ltrim(@notification_message) = null) SELECT @notification_message = NULL
    IF (ltrim(@include_event_description_in) = null) SELECT @include_event_description_in = NULL
    IF (ltrim(@database_name) = null) SELECT @database_name = NULL
    IF (ltrim(@event_description_keyword) = null) SELECT @event_description_keyword = NULL
    IF (ltrim(@task_name) = null) SELECT @task_name = NULL

    /* Would this alert match an already existing alert? */
    DECLARE @duplicate_id varchar(10)
    SELECT @duplicate_id = '(unknown)'
    SELECT @duplicate_id = convert(varchar(10), id)
    FROM msdb.dbo.sysalerts
    WHERE (message_id = @message_id)
      AND (severity = @severity)
      AND (database_name = @database_name)
      AND (event_description_keyword = @event_description_keyword)
      AND (name <> @name)
    IF (@duplicate_id <> '(unknown)')
    BEGIN
      RAISERROR(14506, 16, 1, @duplicate_id)
      ROLLBACK TRANSACTION
      RETURN(1)
    END

    /* Convert @IncludeEventDescriptionInCode to it's string evivalent */
    IF (@include_event_description_in = NULL)
    BEGIN
      IF (@include_event_desc_code = 0) SELECT @include_event_description_in = 'NONE'
      IF (@include_event_desc_code = 1) SELECT @include_event_description_in = 'EMAIL'
      IF (@include_event_desc_code = 2) SELECT @include_event_description_in = 'PAGER'
      IF (@include_event_desc_code = 3) SELECT @include_event_description_in = 'BOTH'
    END

    /* Validate the Alert */
    DECLARE @return_code tinyint
    EXEC @return_code = sp_validatealert
    @new_name,
    @message_id,
    @severity,
    @enabled,
    @delay_between_responses,
    @notification_message,
    @include_event_description_in,
    @database_name,
    @event_description_keyword,
    @task_name,
    @occurrence_count,
    @raise_snmp_trap
    IF (@return_code <> 0)
    BEGIN
      ROLLBACK TRANSACTION
      RETURN(1)
    END

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

    /* Convert the TaskName into an ID */
    IF (@task_name <> NULL)
    BEGIN
      SELECT @task_id = id
      FROM msdb.dbo.systasks
      WHERE name = ltrim(rtrim(@task_name))
    END

    /* Convert IncludeEventDescriptionIn back to a code */
    IF (@include_event_description_in = 'NONE') SELECT @include_event_desc_code = 0
    IF (@include_event_description_in = 'EMAIL') SELECT @include_event_desc_code = 1
    IF (@include_event_description_in = 'PAGER') SELECT @include_event_desc_code = 2
    IF (@include_event_description_in = 'BOTH') SELECT @include_event_desc_code = 3

    /* Convert zero MessageID/Severity into NULLs */
    IF (@message_id = 0) SELECT @message_id = NULL
    IF (@severity = 0) SELECT @severity = NULL

    /* Turn the 1st 'flags' bit on or off accordingly */
    IF (@raise_snmp_trap = 0)
      SELECT @flags = @flags & 0xFFFE
    ELSE
      SELECT @flags = @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_notifications = @delay_between_responses,
    notification_message = @notification_message,
    include_event_description = @include_event_desc_code,
    database_name = @database_name,
    event_description_keyword = @event_description_keyword,
    task_id = @task_id,
    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 = @flags
    WHERE (name = @name)
  COMMIT TRANSACTION

  /*
  ** Notify SQLExecutive of the change.
  */
  EXEC master.dbo.sp_schedulersignal 'A'
  RETURN(0)
END
go


/**************************************************************/
/* SP_DROPALERT                                               */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_dropalert...'
go
CREATE PROCEDURE sp_dropalert
(
@name  varchar(60)
)
AS
BEGIN
  DECLARE @id int

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Does this Alert exist? */
  IF NOT EXISTS (SELECT *
                 FROM msdb.dbo.sysalerts
                 WHERE name = @name)
  BEGIN
    RAISERROR(14507, 16, 1)
    RETURN(1)
  END

  /* Convert the Name to it's ID */
  SELECT @id = id
  FROM msdb.dbo.sysalerts
  WHERE name = @name

  BEGIN TRANSACTION
    /* Delete sysnotifications entries */
    DELETE FROM msdb.dbo.sysnotifications
    WHERE alert_id = @id
    /* Finally, do the actual DELETE */
    DELETE FROM msdb.dbo.sysalerts
    WHERE id = @id
  COMMIT TRANSACTION

  /*
  ** Notify the SQLExecutive of the change. This xp causes a complete refresh (the only kind
  ** of refresh for Alerts) of the Schedulers sysalerts cache.
  */
  EXEC master.dbo.sp_schedulersignal 'A'
  RETURN(0)
END
go


/**************************************************************/
/* SP_HELPALERT                                               */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_helpalert...'
go
CREATE PROCEDURE sp_helpalert
(
@having_name_like varchar(60) = '%',
@order_by varchar(60) = 'name'
)
AS
BEGIN
  DECLARE @FlagsColumn varchar(255)

  IF EXISTS (SELECT *
             FROM msdb.dbo.syscolumns
             WHERE id = object_id('dbo.sysalerts')
               AND name = 'flags')
    SELECT @FlagsColumn = 'flags = sa.flags '
  ELSE
    SELECT @FlagsColumn = 'flags = 0 '

  IF (@order_by NOT LIKE 'task_name%') AND (@order_by NOT LIKE 'task_id%')
    SELECT @order_by = 'sa.' + @order_by
  EXEC('SELECT
        sa.id,
        sa.name,
        sa.event_source,
        sa.event_categoryname,
        sa.event_id,
        sa.message_id,
        sa.severity,
        sa.enabled,
        sa.delay_between_notifications,
        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,
        task_name = st.name,
        sa.has_email_notification,
        sa.has_pager_notification,
        task_id = st.id, ' + @FlagsColumn +
       'FROM msdb.dbo.sysalerts sa, msdb.dbo.systasks st
        WHERE (sa.task_id *= st.id)
          AND (sa.name LIKE ''' + @having_name_like + ''')
        ORDER BY ' + @order_by)
END
go


/**************************************************************/
/* SP_ADDOPERATOR                                             */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_addoperator...'
go
CREATE PROCEDURE sp_addoperator
(
@name                         varchar(50),
@enabled                      tinyint = 1,
@email_address                varchar(100) = NULL,
@pager_address                varchar(100) = NULL,
@weekday_pager_start_time     int = 090000, /* HHMMSS using 24 hour clock */
@weekday_pager_end_time       int = 180000, /* HHMMSS using 24 hour clock */
@saturday_pager_start_time    int = 090000, /* HHMMSS using 24 hour clock */
@saturday_pager_end_time      int = 180000, /* HHMMSS using 24 hour clock */
@sunday_pager_start_time      int = 090000, /* HHMMSS using 24 hour clock */
@sunday_pager_end_time        int = 180000, /* HHMMSS using 24 hour clock */
@pager_days                   tinyint = 0
)
AS
BEGIN
  /* Turn empty strings into NULLs */
  IF (ltrim(@email_address) = null) SELECT @email_address = NULL
  IF (ltrim(@pager_address) = null) SELECT @pager_address = NULL

  DECLARE @return_code tinyint

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Does this Operator already exist? */
  IF EXISTS(SELECT *
            FROM msdb.dbo.sysoperators
            WHERE name = @name)
  BEGIN
    RAISERROR(14513, 16, 1)
    RETURN(1)
  END

  /* Valid Start/End Times? */
  EXEC @return_code = sp_verifytasktime @weekday_pager_start_time, 14522
  IF (@return_code <> 0) RETURN(1)
  EXEC @return_code = sp_verifytasktime @weekday_pager_end_time, 14523
  IF (@return_code <> 0) RETURN(1)
  EXEC @return_code = sp_verifytasktime @saturday_pager_start_time, 14524
  IF (@return_code <> 0) RETURN(1)
  EXEC @return_code = sp_verifytasktime @saturday_pager_end_time, 14525
  IF (@return_code <> 0) RETURN(1)
  EXEC @return_code = sp_verifytasktime @sunday_pager_start_time, 14526
  IF (@return_code <> 0) RETURN(1)
  EXEC @return_code = sp_verifytasktime @sunday_pager_end_time, 14527
  IF (@return_code <> 0) RETURN(1)

  /* Valid PagerDays? */
  IF (@pager_days < 0) OR (@pager_days > 127)
  BEGIN
    RAISERROR(14514, 16, 1)
    RETURN(1)
  END

  /* Enabled must be 0 or 1 */
  IF (@enabled NOT IN (0, 1))
  BEGIN
    RAISERROR(14516, 16, 1)
    RETURN(1)
  END

  /* 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
  )
  VALUES
  (
  @name,
  @enabled,
  @email_address,
  NULL,
  NULL,
  @pager_address,
  NULL,
  NULL,
  @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
  )
  RETURN(0)
END
go


/**************************************************************/
/* SP_UPDATEOPERATOR                                          */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_updateoperator...'
go
CREATE PROCEDURE sp_updateoperator
(
@name                         varchar(50),
@new_name                     varchar(50) = NULL,
@enabled                      tinyint = NULL,
@email_address                varchar(100) = NULL,
@pager_address                varchar(100) = NULL,
@weekday_pager_start_time     int = NULL, /* HHMMSS using 24 hour clock */
@weekday_pager_end_time       int = NULL, /* HHMMSS using 24 hour clock */
@saturday_pager_start_time    int = NULL, /* HHMMSS using 24 hour clock */
@saturday_pager_end_time      int = NULL, /* HHMMSS using 24 hour clock */
@sunday_pager_start_time      int = NULL, /* HHMMSS using 24 hour clock */
@sunday_pager_end_time        int = NULL, /* HHMMSS using 24 hour clock */
@pager_days                   tinyint = NULL
)
AS
BEGIN
  DECLARE @return_code               tinyint
  DECLARE @alert_fail_safe_operator  varchar(64)

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Does this Operator exist? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysoperators
                WHERE name = @name)
  BEGIN
    RAISERROR(14508, 16, 1)
    RETURN(1)
  END

  /* Is the NewName unique? */
  IF (@new_name <> NULL) AND (EXISTS (SELECT *
                                      FROM msdb.dbo.sysoperators
                                      WHERE name = @new_name))
  BEGIN
    RAISERROR(14529, 16, 1)
    RETURN(1)
  END

  /* Valid Start/End Times? */
  IF (@weekday_pager_start_time <> NULL)
  BEGIN
    EXEC @return_code = sp_verifytasktime @weekday_pager_start_time, 14522
    IF (@return_code <> 0) RETURN(1)
  END
  IF (@weekday_pager_end_time <> NULL)
  BEGIN
    EXEC @return_code = sp_verifytasktime @weekday_pager_end_time, 14523
    IF (@return_code <> 0) RETURN(1)
  END
  IF (@saturday_pager_start_time <> NULL)
  BEGIN
    EXEC @return_code = sp_verifytasktime @saturday_pager_start_time, 14524
    IF (@return_code <> 0) RETURN(1)
  END
  IF (@saturday_pager_end_time <> NULL)
  BEGIN
    EXEC @return_code = sp_verifytasktime @saturday_pager_end_time, 14525
    IF (@return_code <> 0) RETURN(1)
  END
  IF (@sunday_pager_start_time <> NULL)
  BEGIN
    EXEC @return_code = sp_verifytasktime @sunday_pager_start_time, 14526
    IF (@return_code <> 0) RETURN(1)
  END
  IF (@sunday_pager_end_time <> NULL)
  BEGIN
    EXEC @return_code = sp_verifytasktime @sunday_pager_end_time, 14527
    IF (@return_code <> 0) RETURN(1)
  END

  /* Valid PagerDays? */
  IF (@pager_days <> NULL) AND ((@pager_days < 0) OR (@pager_days > 127))
  BEGIN
    RAISERROR(14514, 16, 1)
    RETURN(1)
  END

  /* Enabled must be 0 or 1 */
  IF (@enabled <> NULL) AND (@enabled NOT IN (0, 1))
  BEGIN
    RAISERROR(14516, 16, 1)
    RETURN(1)
  END

  BEGIN TRANSACTION
    IF (@new_name = NULL) SELECT @new_name = @name
    IF (@enabled = NULL) SELECT @enabled = enabled FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@email_address = NULL) SELECT @email_address = email_address FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@pager_address = NULL) SELECT @pager_address = pager_address FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@weekday_pager_start_time = NULL) SELECT @weekday_pager_start_time = weekday_pager_start_time FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@weekday_pager_end_time = NULL) SELECT @weekday_pager_end_time = weekday_pager_end_time FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@saturday_pager_start_time = NULL) SELECT @saturday_pager_start_time = saturday_pager_start_time FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@saturday_pager_end_time = NULL) SELECT @saturday_pager_end_time = saturday_pager_end_time FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@sunday_pager_start_time = NULL) SELECT @sunday_pager_start_time = sunday_pager_start_time FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@sunday_pager_end_time = NULL) SELECT @sunday_pager_end_time = sunday_pager_end_time FROM msdb.dbo.sysoperators WHERE name = @name
    IF (@pager_days = NULL) SELECT @pager_days = pager_days FROM msdb.dbo.sysoperators WHERE name = @name

    /* Turn empty strings into NULLs */
    IF (ltrim(@email_address) = null) SELECT @email_address = NULL
    IF (ltrim(@pager_address) = null) SELECT @pager_address = NULL

    /* Finally, 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
    WHERE (name = @name)
  COMMIT TRANSACTION

  /* Is this operator the Server.AlertFailSafeOperator? */
  EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                'AlertFailSafeOperator',
                                @param = @alert_fail_safe_operator OUTPUT

  /* If it is, we update the 3 'AlertFailSafe...' registry entries  and AlertNotificationMethod */
  IF (ltrim(rtrim(@alert_fail_safe_operator)) = ltrim(rtrim(@name)))
  BEGIN
    /* Update AlertFailSafeX values*/
    EXECUTE master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                                   'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                   'AlertFailSafeOperator',REG_SZ,@new_name
    EXECUTE master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                                   'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                   'AlertFailSafeEmailAddress',REG_SZ,@email_address
    EXECUTE master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                                   'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                   'AlertFailSafePagerAddress',REG_SZ,@pager_address
    /* Update AlertNotificationMethod values*/
    DECLARE @notification_method int
    EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                  'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                  'AlertNotificationMethod',@param = @notification_method OUTPUT
    IF (ltrim(rtrim(@email_address)) = null)
      SELECT @notification_method = @notification_method & ~1
    IF (ltrim(rtrim(@pager_address)) = null)
      SELECT @notification_method = @notification_method & ~2
    EXECUTE master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                                   'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                   'AlertNotificationMethod',REG_DWORD,@notification_method
    /* And finally, let the SQLExecutive know of the changes... */
    EXECUTE master.dbo.sp_schedulersignal 'G'
  END

  RETURN(0)
END
go


/**************************************************************/
/* SP_DROPOPERATOR                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_dropoperator...'
go
CREATE PROCEDURE sp_dropoperator
(
@name varchar(50)
)
AS
BEGIN
  DECLARE @id                        int
  DECLARE @alert_fail_safe_operator  varchar(64)
  DECLARE @warning                   varchar(255)

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Does this Operator exist? */
  IF NOT EXISTS (SELECT *
                 FROM msdb.dbo.sysoperators
                 WHERE name = @name)
  BEGIN
    RAISERROR(14508, 16, 1)
    RETURN(1)
  END

  /* Is this operator the Server.AlertFailSafeOperator? */
  EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                'AlertFailSafeOperator',
                                @param = @alert_fail_safe_operator OUTPUT

  /* If it is, we disallow the drop operation. */
  IF (ltrim(rtrim(@alert_fail_safe_operator)) = ltrim(rtrim(@name)))
  BEGIN
    SELECT @name = ltrim(rtrim(@name))
    RAISERROR(14530, 16, 1, @name, @name)
    RETURN(1)
  END

  /* Convert the Name to it's ID */
  SELECT @id = id
  FROM msdb.dbo.sysoperators
  WHERE name = @name

  BEGIN TRANSACTION
    /* Delete sysnotifications entries */
    DELETE FROM msdb.dbo.sysnotifications
    WHERE operator_id = @id
    /* Finally, do the actual DELETE */
    DELETE FROM msdb.dbo.sysoperators
    WHERE id = @id
  COMMIT TRANSACTION
  RETURN(0)
END
go


/**************************************************************/
/* SP_HELPOPERATOR                                            */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_helpoperator...'
go
CREATE PROCEDURE sp_helpoperator
(
@having_name_like varchar(50) = '%'
)
AS
BEGIN
  SELECT
  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
  FROM msdb.dbo.sysoperators
  WHERE (name LIKE @having_name_like)
END
go


/**************************************************************/
/* SP_ADDNOTIFICATION                                         */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_addnotification...'
go
CREATE PROCEDURE sp_addnotification
(
@alert_name           varchar(60),
@operator_name        varchar(50),
@notification_method  varchar(10)
)
AS
BEGIN
  DECLARE @alert_id                   int
  DECLARE @operator_id                int
  DECLARE @notification_method_code   tinyint

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Is the AlertName valid? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysalerts
                WHERE name = ltrim(rtrim(@alert_name))
                HAVING count(*) = 1)
  BEGIN
    RAISERROR(14507, 16, 1)
    RETURN(1)
  END

  /* Is the OperatorName valid? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysoperators
                WHERE name = ltrim(rtrim(@operator_name))
                HAVING count(*) = 1)
  BEGIN
    RAISERROR(14508, 16, 1)
    RETURN(1)
  END

  /* Is the NotificationMethod valid? */
  SELECT @notification_method = upper(@notification_method)
  IF (@notification_method NOT IN ('EMAIL', 'PAGER', 'BOTH'))
  BEGIN
    RAISERROR(14517, 16, 1)
    RETURN(1)
  END

  /* Convert the AlertName to an ID */
  SELECT @alert_id = id
  FROM msdb.dbo.sysalerts
  WHERE name = ltrim(rtrim(@alert_name))

  /* Convert the OperatorName to an ID */
  SELECT @operator_id = id
  FROM msdb.dbo.sysoperators
  WHERE name = ltrim(rtrim(@operator_name))

  /* Convert the NotificationMethod to it's matching code */
  IF (@notification_method = 'EMAIL') SELECT @notification_method_code = 1
  IF (@notification_method = 'PAGER') SELECT @notification_method_code = 2
  IF (@notification_method = 'BOTH') SELECT @notification_method_code = 3

  /* Does this notification already exist? */
  /* NOTE: The unique index would catch this, but testing     */
  /*       for the problem here let's us control the message. */
  IF EXISTS(SELECT *
            FROM msdb.dbo.sysnotifications
            WHERE (alert_id = @alert_id)
              AND (operator_id = @operator_id))
  BEGIN
    RAISERROR(14515, 16, 1)
    RETURN(0)
  END

  /* Finally, do the INSERT */
  INSERT INTO sysnotifications
  (
  alert_id,
  operator_id,
  notification_method
  )
  VALUES
  (
  @alert_id,
  @operator_id,
  @notification_method_code
  )
  RETURN(0)
END
go


/**************************************************************/
/* SP_UPDATENOTIFICATION                                      */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_updatenotification...'
go
CREATE PROCEDURE sp_updatenotification
(
@alert_name           varchar(60),
@operator_name        varchar(50),
@notification_method  varchar(10)
)
AS
BEGIN
  DECLARE @alert_id                  int
  DECLARE @operator_id               int
  DECLARE @notification_method_code  tinyint

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Is the AlertName valid? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysalerts
                WHERE name = ltrim(rtrim(@alert_name))
                HAVING count(*) = 1)
  BEGIN
    RAISERROR(14507, 16, 1)
    RETURN(1)
  END

  /* Is the OperatorName valid? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysoperators
                WHERE name = ltrim(rtrim(@operator_name))
                HAVING count(*) = 1)
  BEGIN
    RAISERROR(14508, 16, 1)
    RETURN(1)
  END

  /* Convert the AlertName to an ID */
  SELECT @alert_id = id
  FROM msdb.dbo.sysalerts
  WHERE name = ltrim(rtrim(@alert_name))

  /* Convert the OperatorName to an ID */
  SELECT @operator_id = id
  FROM msdb.dbo.sysoperators
  WHERE name = ltrim(rtrim(@operator_name))

  /* Does this notification exist? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysnotifications
                WHERE alert_id = @alert_id
                  AND operator_id = @operator_id)
  BEGIN
    RAISERROR(14509, 16, 1)
    RETURN(1)
  END

  /* Is the NotificationMethod valid? */
  SELECT @notification_method = upper(@notification_method)
  IF (@notification_method NOT IN ('EMAIL', 'PAGER', 'BOTH'))
  BEGIN
    RAISERROR(14517, 16, 1)
    RETURN(1)
  END

  /* Convert the NotificationMethod to it's matching code */
  IF (@notification_method = 'EMAIL') SELECT @notification_method_code = 1
  IF (@notification_method = 'PAGER') SELECT @notification_method_code = 2
  IF (@notification_method = 'BOTH') SELECT @notification_method_code = 3

  /* Do the INSERT */
  UPDATE msdb.dbo.sysnotifications
  SET notification_method = @notification_method_code
  WHERE (alert_id = @alert_id)
    AND (operator_id = @operator_id)

  RETURN(0)
END
go


/**************************************************************/
/* SP_DROPNOTIFICATION                                        */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_dropnotification...'
go
CREATE PROCEDURE sp_dropnotification
(
@alert_name     varchar(60),
@operator_name  varchar(50)
)
AS
BEGIN
  DECLARE @alert_id     int
  DECLARE @operator_id  int

  /* Is this user the SA? */
  IF (suser_id() <> 1)
  BEGIN
    RAISERROR(15003, 16, 1)
    RETURN(1)
  END

  /* Is the AlertName valid? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysalerts
                WHERE name = ltrim(rtrim(@alert_name))
                HAVING count(*) = 1)
  BEGIN
    RAISERROR(14507, 16, 1)
    RETURN(1)
  END

  /* Is the OperatorName valid? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysoperators
                WHERE name = ltrim(rtrim(@operator_name))
                HAVING count(*) = 1)
  BEGIN
    RAISERROR(14508, 16, 1)
    RETURN(1)
  END

  /* Convert the AlertName to an ID */
  SELECT @alert_id = id
  FROM msdb.dbo.sysalerts
  WHERE name = ltrim(rtrim(@alert_name))

  /* Convert the OperatorName to an ID */
  SELECT @operator_id = id
  FROM msdb.dbo.sysoperators
  WHERE name = ltrim(rtrim(@operator_name))

  /* Does this notification exist? */
  IF NOT EXISTS(SELECT *
                FROM msdb.dbo.sysnotifications
                WHERE alert_id = @alert_id
                  AND operator_id = @operator_id)
  BEGIN
    RAISERROR(14509, 16, 1)
    RETURN(1)
  END

  /* Finally, do the Delete */
  DELETE FROM msdb.dbo.sysnotifications
  WHERE (alert_id = @alert_id)
    AND (operator_id = @operator_id)
  RETURN(0)
END
go


/**************************************************************/
/* SP_HELPNOTIFICATION                                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_helpnotification...'
go
CREATE PROCEDURE sp_helpnotification
(
@object_type          char(9),  /* Either 'ALERTS' (Enum Alerts for given Operator),   */
                                /*     or 'OPERATORS' (Enum Operators for given Alert) */
@name                 char(60), /* Either an Operator Name (if @ObjectType = 'ALERTS') */
                                /*     or an Alert Name (if @ObjectType = 'OPERATORS') */
@enum_type            char(10), /* Either 'ALL' (Enumerate all objects)                                  */
                                /*     or 'ACTUAL' (Enumerate only the associated objects)               */
                                /*     or 'TARGET' (Enumerate only the objects matching @TargetName) */
@notification_method  char(10), /* Either 'EMAIL' (show only email notification info.),    */
                                /*     or 'PAGER' (show only pager notification info.),    */
                                /*     or 'BOTH' (show both pager and email notification info.) */
@target_name          char(60) = NULL /* Either an Alert Name (if @ObjectType = 'ALERTS')             */
                                      /*     or an Operator Name (if @ObjectType = 'OPERATORS')       */
                                      /* This parameter is only required if @EnumType = 'TARGET') */
)
AS
BEGIN
  DECLARE @id               int /*We will use this to store the decode of @Name */
  DECLARE @target_id        int /*We will use this to store the decode of @TargetName */
  DECLARE @select_clause_1  varchar(255)
  DECLARE @select_clause_2  varchar(255)
  DECLARE @from_clause      varchar(255)
  DECLARE @where_clause     varchar(255)
  DECLARE @bitmap           varchar(255)

  SELECT @object_type = upper(@object_type)
  SELECT @enum_type = upper(@enum_type)
  SELECT @notification_method = upper(@notification_method)

  /* Valid ObjectType? */
  IF (@object_type NOT IN ('ALERTS', 'OPERATORS'))
  BEGIN
    RAISERROR(14510, 16, 1)
    RETURN(1)
  END

  /* Valid AlertName? */
  IF (@object_type = 'OPERATORS') AND (NOT EXISTS (SELECT *
                                                   FROM msdb.dbo.sysalerts
                                                   WHERE name = @name))
  BEGIN
    RAISERROR(14507, 16, 1)
    RETURN(1)
  END

  /* Valid OperatorName? */
  IF (@object_type = 'ALERTS') AND (NOT EXISTS (SELECT *
                                                FROM msdb.dbo.sysoperators
                                                WHERE name = @name))
  BEGIN
    RAISERROR(14508, 16, 1)
    RETURN(1)
  END

  /* Valid EnumType? */
  IF (@enum_type NOT IN ('ALL', 'ACTUAL', 'TARGET'))
  BEGIN
    RAISERROR(14511, 16, 1)
    RETURN(1)
  END

  /* Valid Notification Method? */
  IF (@notification_method NOT IN ('EMAIL', 'PAGER', 'BOTH'))
  BEGIN
    RAISERROR(14512, 16, 1)
    RETURN(1)
  END

  /* If EnumType is 'TARGET', do we have a @TargetName parameter? */
  IF (@enum_type = 'TARGET') AND (@target_name = NULL)
  BEGIN
    RAISERROR(14519, 16, 1)
    RETURN(1)
  END

  /* If EnumType isn't 'TARGET', we shouldn't have an @TargetName parameter */
  IF (@enum_type <> 'TARGET') AND (@target_name <> NULL)
  BEGIN
    RAISERROR(14520, 16, 1)
    RETURN(1)
  END

  /* Transalate 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

  /* Transalate the TargetName into a TargetID */
  IF (@target_name <> NULL) AND (@object_type = 'OPERATORS')
  BEGIN
    SELECT @target_id = id
    FROM msdb.dbo.sysoperators
    WHERE (name = @target_name )
  END
  IF (@target_name <> NULL) AND (@object_type = 'ALERTS')
  BEGIN
    SELECT @target_id = id
    FROM msdb.dbo.sysalerts
    WHERE (name = @target_name)
  END
  IF (@enum_type = 'TARGET') AND (@target_id = NULL) /* IE. the Target Name is invalid */
  BEGIN
    DECLARE @temp varchar(9)
    SELECT @temp = LOWER(@object_type)
    RAISERROR(14521, 16, 1, @temp)
    RETURN(1)
  END


  /* Ok, the parameters look good so generate the SQL then EXECUTE() it */
  IF (@object_type = 'OPERATORS') /* So we want a list of Operators for the supplied Alert ID */
  BEGIN
    SELECT @select_clause_1 = 'SELECT operator_id = o.id, operator_name = o.name, '
    SELECT @from_clause = 'FROM msdb.dbo.sysnotifications sn, msdb.dbo.sysoperators o '
  END
  IF (@object_type = 'ALERTS') /* So we want a list of Alerts for the supplied Operator ID */
  BEGIN
    SELECT @select_clause_1 = 'SELECT alert_id = a.id, alert_name = a.name, '
    SELECT @from_clause = 'FROM msdb.dbo.sysnotifications sn, msdb.dbo.sysalerts a '
  END

  IF (@notification_method = 'EMAIL') SELECT @bitmap = 'power(2, 0)'
  IF (@notification_method = 'PAGER') SELECT @bitmap = 'power(2, 1)'
  IF (@notification_method = 'BOTH') SELECT @bitmap = '0'

  IF (@notification_method = 'EMAIL') SELECT @select_clause_1 = @select_clause_1 + 'use_email = isnull((sn.notification_method&1) / power(2, 0), 0) '
  IF (@notification_method = 'PAGER') SELECT @select_clause_1 = @select_clause_1 + 'use_pager = isnull((sn.notification_method&2) / power(2, 1), 0) '
  IF (@notification_method = 'BOTH') SELECT @select_clause_1 = @select_clause_1 + 'use_email = isnull((sn.notification_method&1) / power(2, 0), 0), use_pager = isnull((sn.notification_method&2) / power(2, 1), 0) '

  IF (@object_type = 'OPERATORS') AND (@enum_type = 'ALL') SELECT @where_clause = 'WHERE (o.id *= sn.operator_id) AND (sn.alert_id = ' + convert(varchar(10), @id) + ')'
  IF (@object_type = 'OPERATORS') AND (@enum_type = 'ACTUAL') SELECT @where_clause = 'WHERE (o.id = sn.operator_id) AND (sn.alert_id = ' + convert(varchar(10), @id) + ') AND (sn.notification_method & ' + @bitmap + ' = ' + @bitmap + ')'
  IF (@object_type = 'OPERATORS') AND (@enum_type = 'TARGET') SELECT @where_clause = 'WHERE (o.id = sn.operator_id) AND (sn.operator_id = ' + convert(varchar(10), @target_id) + ') AND (sn.alert_id = ' + convert(varchar(10), @id) + ')'
  IF (@object_type = 'ALERTS') AND (@enum_type = 'ALL') SELECT @where_clause = 'WHERE (a.id *= sn.alert_id) AND (sn.operator_id = ' + convert(varchar(10), @id) + ')'
  IF (@object_type = 'ALERTS') AND (@enum_type = 'ACTUAL') SELECT @where_clause = 'WHERE (a.id = sn.alert_id) AND (sn.operator_id = ' + convert(varchar(10), @id) + ') AND (sn.notification_method & ' + @bitmap + ' = ' + @bitmap + ')'
  IF (@object_type = 'ALERTS') AND (@enum_type = 'TARGET') SELECT @where_clause = 'WHERE (a.id = sn.alert_id) AND (sn.alert_id = ' + convert(varchar(10), @target_id) + ') AND (sn.operator_id = ' + convert(varchar(10), @id) + ')'

  /* Additions for new has_email and has_pager columns */
  IF (@object_type = 'OPERATORS')
  BEGIN
    SELECT @select_clause_2 = ',has_email = convert(bit, isnull(ascii(substring(ltrim(rtrim(o.email_address)), 1, 1)), 0)),'
    SELECT @select_clause_2 = @select_clause_2 + 'has_pager = convert(bit, isnull(ascii(substring(ltrim(rtrim(o.pager_address)), 1, 1)), 0)) '
  END
  IF (@object_type = 'ALERTS')
  BEGIN
    SELECT @select_clause_2 = ',has_email = a.has_email_notification,'
    SELECT @select_clause_2 = @select_clause_2 + 'has_pager = a.has_pager_notification '
  END

  EXECUTE (@select_clause_1 + @select_clause_2 + @from_clause + @where_clause)

  RETURN(0)
END
go


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

DUMP TRANSACTION msdb WITH NO_LOG
go

/**************************************************************/
/* NEWORCHANGEDNOTIFICATION                                   */
/**************************************************************/

PRINT ''
PRINT 'Creating trigger NewOrChangedNotification...'
go
CREATE TRIGGER NewOrChangedNotification
ON sysnotifications
FOR UPDATE, INSERT
AS
BEGIN
  /* First, throw out 'non-notification' rows */
  DELETE FROM msdb.dbo.sysnotifications
  WHERE (notification_method = 0)

  /* Update sysalerts.has_email_notification */
  UPDATE sysalerts
  SET has_email_notification = (SELECT count(*)
                                FROM msdb.dbo.sysnotifications
                                WHERE alert_id = sysalerts.id
                                  AND notification_method&1 = 1)
  FROM msdb.dbo.sysalerts, inserted
  WHERE inserted.alert_id = sysalerts.id

  /*Update sysalerts.has_pager_notification */
  UPDATE sysalerts
  SET has_pager_notification = (SELECT count(*)
                                FROM msdb.dbo.sysnotifications
                                WHERE alert_id = sysalerts.id
                                  AND notification_method&2 = 2)
  FROM msdb.dbo.sysalerts, inserted
  WHERE inserted.alert_id = sysalerts.id
END
go

/**************************************************************/
/* REMOVEDNOTIFICATION                                        */
/**************************************************************/

PRINT ''
PRINT 'Creating trigger RemovedNotification...'
go
CREATE TRIGGER RemovedNotification
ON sysnotifications
FOR DELETE
AS
BEGIN
  /* Update sysalerts.has_email_notification */
  UPDATE sysalerts
  SET has_email_notification = (SELECT count(*)
                                FROM msdb.dbo.sysnotifications
                                WHERE alert_id = sysalerts.id
                                  AND notification_method&1 = 1)
  FROM msdb.dbo.sysalerts, deleted
  WHERE deleted.alert_id = sysalerts.id

  /*Update sysalerts.has_pager_notification */
  UPDATE sysalerts
  SET has_pager_notification = (SELECT count(*)
                                FROM msdb.dbo.sysnotifications
                                WHERE alert_id = sysalerts.id
                                  AND notification_method&2 = 2)
  FROM msdb.dbo.sysalerts, deleted
  WHERE deleted.alert_id = sysalerts.id
END
go

DUMP TRANSACTION msdb WITH NO_LOG
go


/**************************************************************/
/* SP_MSSENDLARGEMAIL                                         */
/**************************************************************/

IF EXISTS (SELECT *
           FROM sysobjects
           WHERE name = 'sp_MSsendlargemail'
             AND sysstat & 0xf = 4)
BEGIN
  DROP PROCEDURE dbo.sp_MSsendlargemail
END
go

PRINT ''
PRINT 'Creating procedure sp_MSsendlargemail...'
go
CREATE PROCEDURE sp_MSsendlargemail
(
@recipients      text,
@copy_recipients text = '',
@subject         text = 'SQL Server Message',
@message         text
)
AS
SET NOCOUNT ON
DECLARE @TableName varchar(30)
DECLARE @LengthOfBody varchar(10)
SELECT @LengthOfBody = convert(varchar(10), datalength(@message) + 1)
SELECT @TableName = '##TempMailTable' + convert(varchar(30), abs(convert(int, @@dbts)))
EXEC ('CREATE TABLE ' + @TableName + ' (EmailBody text)')
EXEC ('SET QUOTED_IDENTIFIER OFF
       INSERT INTO ' + @TableName + ' values ("' + @message + '")')
EXEC ('SET QUOTED_IDENTIFIER OFF
       EXEC master.dbo.xp_sendmail
       @recipients = "' + @recipients + '",
       @copy_recipients = "' + @copy_recipients + '",
       @query = "SELECT EmailBody FROM '+ @TableName + '",
       @subject = "' + @subject + '",
       @width = 4096, /* Do NOT change this */
       @no_header = "True"')
EXEC ('DROP TABLE ' + @TableName)
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                                                */
/**************************************************************/

/**************************************************************/
/* SYSBACKUPHISTORY                                           */
/**************************************************************/
IF NOT EXISTS(SELECT * FROM sysobjects WHERE id = object_id('dbo.sysbackuphistory') AND sysstat & 0xf = 1)
BEGIN
  PRINT ''
  PRINT 'Creating table sysbackuphistory...'

  CREATE TABLE sysbackuphistory
  (
  backup_id         int             identity(1,1), -- Primary key
  constraint        pk_backup_id    primary key clustered (backup_id),  -- with clustered index
  id_number         smallint        not null,      -- Position of the backup on the volume
  backup_type       smallint        not null,      -- Database(1), Transaction(2), Table(3)
  database_name     varchar(30)     not null,      -- Database name
  table_owner       varchar(30)     null,          -- Single Table Owner
  table_name        varchar(30)     null,          -- Table name for table backups
  operator_name     char(30)        not null,      -- Name of person who performed the backup
  backup_start      datetime        not null,      -- Date and time of the oldest committed transaction
  backup_finish     datetime        not null,      -- Date and time of the last committed transaction
  server_sort       smallint        not null,      -- Database server sort order
  server_code_page  smallint        not null,      -- Database server code page
  striped_backup    smallint        not null,      -- Striped backup(1), non-striped backup(0)
  stripe_count      smallint        not null,      -- Number of devices in the stripeset
  stripe_set_name   varchar(30)     null,          -- Stripeset Name
  total_backup_size int             not null,      -- Total size of backup in bytes (not pages)
  current_sequence  char(14)        not null,      -- Currentsequence number
  new_sequence      char(14)        null           -- New sequence number
  )
END
go

/**************************************************************/
/* SYSBACKUPDETAIL                                            */
/**************************************************************/
IF NOT EXISTS(SELECT * FROM sysobjects WHERE id = object_id('dbo.sysbackupdetail') AND sysstat & 0xf = 1)
BEGIN
  PRINT ''
  PRINT 'Creating table sysbackupdetail...'

  CREATE TABLE sysbackupdetail
  (
  backup_id       int             not null,    -- Foreign key
  constraint      fk_backup_id    foreign key (backup_id)
  references      sysbackuphistory(backup_id), -- to sysbackuphistory
  device_type     smallint        not null,    -- Disk(2), Floppy(3), Tape(5) or Pipe(6)
  device_name     varchar(255)    not null,    -- Device or UNC Name
  backup_size     int             not null,    -- Size of backup on this device in bytes (not pages)
  volume_name     varchar(6)      null,        -- Volume label
  expiration_date datetime        null         -- Expiration date for the volume
  )
END
go

/**************************************************************/
/* SYSRESTOREHISTORY                                          */
/**************************************************************/
IF NOT EXISTS(SELECT * FROM sysobjects WHERE id = object_id('dbo.sysrestorehistory') AND sysstat & 0xf = 1)
BEGIN
  PRINT ''
  PRINT 'Creating table sysrestorehistory...'

  CREATE TABLE sysrestorehistory
  (
  restore_id           int           identity(1,1)  -- Primary key
  constraint           pk_restore_id primary key clustered (restore_id),    -- with clustered index
  id_number            smallint      not null,      -- Position of the backup on the volume
  backup_type          smallint      not null,      -- Database(1), Transaction(2), Table(3)
  source_database_name varchar(30)   not null,      -- Source (original) database name
  new_database_name    varchar(30)   not null,      -- New (destination) database name
  table_owner          varchar(30)   null,          -- Single Table Owner
  table_name           varchar(30)   null,          -- Table name for table backups
  operator_name        char(30)      not null,      -- Name of person who performed the backup
  backup_start         datetime      not null,      -- Date and time of the oldest committed transaction
  backup_finish        datetime      not null,      -- Date and time of the last committed transaction
  server_sort          smallint      not null,      -- Database server sort order
  server_code_page     smallint      not null,      -- Database server code page
  striped_backup       smallint      not null,      -- Striped backup(1), non-striped backup(0)
  stripe_Count         smallint      not null,      -- Number of devices in the stripeset
  stripe_set_name      varchar(30)   null,          -- Stripeset Name
  total_backup_size    int           not null,      -- Total size of backup in bytes (not pages)
  current_sequence     char(14)      not null,      -- Currentsequence number
  new_sequence         char(14)      null,          -- New sequence number
  restore_date         datetime      not null       -- Date/time restore performed
  )
END
go

/**************************************************************/
/* SYSRESTOREDETAIL                                           */
/**************************************************************/
IF NOT EXISTS(SELECT * FROM sysobjects WHERE id = object_id('dbo.sysrestoredetail') AND sysstat & 0xf = 1)
BEGIN
  PRINT ''
  PRINT 'Creating table sysrestoredetail...'

  CREATE TABLE sysrestoredetail
  (
  restore_id      int                            -- Foreign key
  constraint      fk_restore_id   foreign key (restore_id)
  references      sysrestorehistory(restore_id), -- to sysrestorehistory
  device_type     smallint        not null,      -- Disk(2), Floppy(3), Tape(5) or Pipe(6)
  device_name     varchar(255)    not null,      -- Device or UNC Name
  backup_size     int             not null,      -- Size of backup on this device in bytes (not pages)
  volume_name     varchar(6)      null,          -- Volume label
  expiration_date datetime        null           -- Expiration date for the volume
  )
END
go

/**************************************************************/
/* SYSVOLUMELABEL                                             */
/**************************************************************/
IF NOT EXISTS(SELECT * FROM sysobjects WHERE id = object_id('dbo.sysvolumelabel') AND sysstat & 0xf = 3)
BEGIN
  PRINT ''
  PRINT 'Creating table sysvolumelabel...'

  EXEC("CREATE TABLE sysvolumelabel
        (
        label_prefix   char(2)  not null,
        label_counter  int      not null
        )")

  EXEC("INSERT INTO sysvolumelabel (label_prefix, label_counter) VALUES ('SS', 0)")
END
go


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

/**************************************************************/
/* SP_GET_VOLUME_LABEL (a 'master' database proc)             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_get_volume_label...'
go

USE master
go
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
PRINT 'The RECONFIGURE command was run.'
go

IF (EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_get_volume_label' AND sysstat & 0xf = 4))
  DROP PROC sp_get_volume_label
go

CREATE PROCEDURE sp_get_volume_label
AS
BEGIN
  DECLARE @volume_counter int
  DECLARE @volume_prefix  char(2)
  DECLARE @volume_label   char(6)
  DECLARE @junk_counter   varchar(4)
  DECLARE @junk_string    char(6)

  BEGIN TRANSACTION
    UPDATE msdb.dbo.sysvolumelabel
    SET @volume_counter = label_counter = label_counter + 1,
    @volume_prefix = label_prefix = label_prefix
  COMMIT TRANSACTION

  IF (@volume_counter > 9999)
    UPDATE msdb.dbo.sysvolumelabel
    SET @volume_counter = label_counter = 1

  IF (datalength(@volume_prefix) <> 2)
    UPDATE msdb.dbo.sysvolumelabel
    SET @volume_prefix = label_prefix = 'DB'

  SELECT @junk_counter = convert(char,@volume_counter )
  SELECT @junk_string = upper(@volume_prefix) + '0000'
  SELECT @volume_label = stuff(@junk_string,(7-datalength(@junk_counter)),datalength(@junk_counter), @junk_counter)
  SELECT 'VolumeLabel' = @volume_label, 'Junk_string' = @junk_string, 'junk_counter'= @junk_counter, 'volume_counter'=@volume_counter
END
go

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
PRINT 'The RECONFIGURE command was run.'
go

USE msdb
go


/**************************************************************/
/* SP_SYSBACKUPHISTORY                                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_sysbackuphistory...'
go

IF (EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_sysbackuphistory' AND sysstat & 0xf = 4))
  DROP PROC sp_sysbackuphistory
go

-- Stored procedure to return backup history so that the most recent
-- backups appear at the top and the oldest appear at the bottom

CREATE PROCEDURE sp_sysbackuphistory (@db char(30) = null, @owner char(30) = null, @tablename char(30) = null )
AS

BEGIN
  DECLARE @sm int

  SELECT @sm = 0

  IF (@db is null)
  BEGIN
    raiserror(4318,16,1)
    RETURN 1
  END

  IF (@tablename IS NULL AND @owner IS NOT NULL) OR
     (@tablename IS NOT NULL AND @owner IS NULL)
  BEGIN
    raiserror(4319,16,1)
    RETURN 1
  END

  IF (@tablename is null) AND (@owner is null)
  BEGIN
    SELECT 'display'=1, backup_id, id_number, backup_type, database_name, table_owner,
           table_name, operator_name, backup_start, backup_finish, server_sort, server_code_page, striped_backup, stripe_count, stripe_set_name,
           total_backup_size, current_sequence, new_sequence
    INTO #tempHistory
    FROM sysbackuphistory
    WHERE database_name = @db
      AND (backup_type = 1 OR backup_type = 2)
    ORDER BY backup_finish ASC

    UPDATE #tempHistory
    SET @sm = display = CASE backup_type
                        WHEN 1 THEN @sm + 1
                        ELSE @sm
                        END

    SELECT backup_id, backup_type, backup_start, backup_finish, database_name,
           id_number, table_owner, table_name, operator_name,
           server_sort, server_code_page, striped_backup, stripe_count,
           stripe_set_name, total_backup_size, current_sequence, new_sequence
    FROM #tempHistory
    ORDER BY display DESC

    DROP TABLE #tempHistory
  END

  ELSE

  BEGIN
    SELECT backup_id, backup_type, backup_start, backup_finish, database_name,
           id_number, table_owner, table_name, operator_name,
           server_sort, server_code_page, striped_backup, stripe_count,
           stripe_set_name, total_backup_size, current_sequence, new_sequence
    FROM sysbackuphistory
    WHERE (database_name = @db  AND backup_type  = 1 )
      OR (database_name = @db  and backup_type = 3
        AND table_name = @tablename  AND table_owner = @owner)
    ORDER BY backup_finish DESC
  END
END
go




/**************************************************************/
/* CHECK IF ALL OBJECTS GOT CREATED AND SET THE CATEGORY BIT  */
/**************************************************************/
PRINT ''
PRINT 'Checking objects created by instmsdb.sql...'
go
EXEC sp_check_objects 'msdb'
PRINT ''
go

/* Ensure that 'allow updates' is turned off */
EXECUTE sp_configure 'allow updates', 0
go
reconfigure with override
PRINT 'The RECONFIGURE command was run.'
go


/**************************************************************/
/* INSTALL OBJECT PERMISSIONS                                 */
/**************************************************************/

EXEC('USE master GRANT EXEC ON sp_schedulersignal TO public USE msdb')
EXEC('USE master GRANT EXEC ON xp_enumqueuedtasks TO public USE msdb')
EXEC('GRANT EXEC ON sp_verifytaskdate TO public')
EXEC('GRANT EXEC ON sp_verifytasktime TO public')
EXEC('GRANT EXEC ON sp_verifytasksched TO public')
EXEC('GRANT EXEC ON sp_verifytask TO public')
EXEC('GRANT EXEC ON sp_verifytaskid TO public')
EXEC('GRANT EXEC ON sp_addtask TO public')
EXEC('GRANT EXEC ON sp_updatetask TO public')
EXEC('GRANT EXEC ON sp_droptask TO public')
EXEC('GRANT EXEC ON sp_reassigntask TO public')
EXEC('GRANT EXEC ON sp_uniquetaskname TO public')
EXEC('GRANT EXEC ON sp_helptask TO public')
EXEC('GRANT EXEC ON sp_helphistory TO public')
EXEC('GRANT EXEC ON sp_purgehistory TO public')
EXEC('GRANT EXEC ON sp_schedulerrefresh TO public')
EXEC('GRANT EXEC ON sp_helpalert TO public')
EXEC('GRANT EXEC ON sp_helpoperator TO public')
EXEC('GRANT EXEC ON sp_helpnotification TO public')
EXEC('GRANT EXEC ON sp_MStaskparameters TO public')
EXEC('GRANT ALL ON MSWork TO public')
EXEC('GRANT SELECT ON systasks_view TO public')
EXEC('GRANT SELECT ON syshistory TO public')
EXEC('GRANT SELECT ON sysalerts TO public')
EXEC('GRANT SELECT ON sysnotifications TO public')
EXEC('GRANT SELECT ON sysoperators TO public')
EXEC('REVOKE INSERT, UPDATE, DELETE, SELECT ON systasks FROM public')
EXEC('REVOKE INSERT, UPDATE, DELETE ON syshistory FROM public')
EXEC('REVOKE INSERT, UPDATE, DELETE ON sysalerts FROM public')
EXEC('REVOKE INSERT, UPDATE, DELETE ON sysnotifications FROM public')
EXEC('REVOKE INSERT, UPDATE, DELETE ON sysoperators FROM public')
go


/**************************************************************/
/* INSTALL DEFAULT ALERTS                                     */
/**************************************************************/
PRINT ''
PRINT 'Installing default alerts...'
go

EXEC sp_altermessage 1105, with_log, true
go
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Full msdb'
                 OR (severity = null AND
                     message_id = 1105 AND
                     database_name = 'msdb' AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Full msdb',
  @message_id = 1105,
  @severity = 0,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = 'msdb',
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Full tempdb'
                 OR (severity = null AND
                     message_id = 1105 AND
                     database_name = 'tempdb' AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Full tempdb',
  @message_id = 1105,
  @severity = 0,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = 'tempdb',
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go

IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Sev. 19 Errors'
                 OR (severity = 19 AND
                     message_id = null AND
                     database_name = null AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Sev. 19 Errors',
  @message_id = 0,
  @severity = 19,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = null,
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Sev. 20 Errors'
                 OR (severity = 20 AND
                     message_id = null AND
                     database_name = null AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Sev. 20 Errors',
  @message_id = 0,
  @severity = 20,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = null,
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Sev. 21 Errors'
                 OR (severity = 21 AND
                     message_id = null AND
                     database_name = null AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Sev. 21 Errors',
  @message_id = 0,
  @severity = 21,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = null,
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Sev. 22 Errors'
                 OR (severity = 22 AND
                     message_id = null AND
                     database_name = null AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Sev. 22 Errors',
  @message_id = 0,
  @severity = 22,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = null,
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Sev. 23 Errors'
                 OR (severity = 23 AND
                     message_id = null AND
                     database_name = null AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Sev. 23 Errors',
  @message_id = 0,
  @severity = 23,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = null,
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Sev. 24 Errors'
                 OR (severity = 24 AND
                     message_id = null AND
                     database_name = null AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Sev. 24 Errors',
  @message_id = 0,
  @severity = 24,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = null,
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysalerts
              WHERE name = 'Demo: Sev. 25 Errors'
                 OR (severity = 25 AND
                     message_id = null AND
                     database_name = null AND
                     event_description_keyword = null))
BEGIN
  EXEC msdb.dbo.sp_addalert
  @name = 'Demo: Sev. 25 Errors',
  @message_id = 0,
  @severity = 25,
  @enabled = 1,
  @delay_between_responses = 10,
  @database_name = null,
  @notification_message = null,
  @task_name = null,
  @event_description_keyword = null,
  @include_event_description_in = 'email'
END
go

PRINT ''
PRINT '----------------------------------------------'
PRINT 'INSTMSDB.SQL created all objects successfully.'
PRINT '----------------------------------------------'
go

DUMP TRANSACTION msdb WITH NO_LOG
go

CHECKPOINT
go

/* End of INSTMSDB.SQL */