/**********************************************************************/
/* 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 not null)
	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

/* Check that we're in msdb... */
if (db_name() <> 'msdb')
  raiserror('A problem was encountered accessing msdb. INSTMSDB.SQL terminating.', 20, 127) with log
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


/**************************************************************/
/* RECORD THE CURRENT DATE/TIME FOR LATER USE WHEN SETTING    */
/* THE 'SYSTEM-OBJECT' CATEGORY BIT                           */
/**************************************************************/
EXEC sp_MS_upd_sysobj_category 1
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 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
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_stoptask' and sysstat & 0xf = 4)
	drop proc sp_stoptask
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     NOT NULL,
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...'

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

  CREATE UNIQUE CLUSTERED INDEX ByName ON dbo.sysalerts(name)
  CREATE UNIQUE INDEX ByID ON dbo.sysalerts(id)
end
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

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

  EXEC ('CREATE TABLE sysoperators
         (
         id                        int             IDENTITY,
         name                      varchar(50)     NOT NULL,
         enabled                   tinyint         NOT NULL,
         email_address             varchar(100)    NULL,
         last_email_date           int             NULL,
         last_email_time           int             NULL,
         pager_address             varchar(100)    NULL,
         last_pager_date           int             NULL,
         last_pager_time           int             NULL,
         weekday_pager_start_time  int             NOT NULL,
         weekday_pager_end_time    int             NOT NULL,
         saturday_pager_start_time int             NOT NULL,
         saturday_pager_end_time   int             NOT NULL,
         sunday_pager_start_time   int             NOT NULL,
         sunday_pager_end_time     int             NOT NULL,
         pager_days                tinyint         NOT NULL
         )')

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

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

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

  EXEC ('CREATE TABLE systasks
         (
         id                     int           IDENTITY, /* task id */
         name                   varchar(100)  NOT NULL, /* task name:  Long enough for server_database_publication_NNNNNNN. */
         subsystem              varchar(30)   NOT NULL, /* 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      NOT NULL, /* 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       NOT NULL, /* invocable if nonzero (boolean; tinyint for expandability) */
         freqtype               int           NOT NULL, /* main frequency type (day, week, month, monthrelative) */
         freqinterval           int           NOT NULL, /* see docs */
         freqsubtype            int           NOT NULL, /* for daily type; see docs */
         freqsubinterval        int           NOT NULL, /* for daily type; see docs */
         freqrelativeinterval   int           NOT NULL, /* see docs */
         freqrecurrencefactor   int           NOT NULL, /* see docs */
         activestartdate        int           NOT NULL, /* date for task to begin being scheduled */
         activeenddate          int           NOT NULL, /* date for task to stop being scheduled */
         activestarttimeofday   int           NOT NULL, /* time of day for task to begin being scheduled */
         activeendtimeofday     int           NOT NULL, /* time of day for task to stop being scheduled */
         lastrundate            int           NOT NULL, /* last day this task was run (yyyymmdd) */
         lastruntime            int           NOT NULL, /* time of last day this task was run (hhmmss) */
         nextrundate            int           NOT NULL, /* next day to run task */
         nextruntime            int           NOT NULL, /* time of next day to run task */
         runpriority            int           NOT NULL, /* nt execution priority for this task */
         emailoperatorid        int           NULL,     /* id of operator to email on emailcompletionlevel */
         retryattempts          int           NOT NULL, /* number of retries to attempt on failure */
         retrydelay             int           NOT NULL, /* time in seconds to delay between retries */
         datecreated            datetime      NOT NULL, /* date and time task was created */
         datemodified           datetime      NOT NULL, /* date and time task was last modified */
         command                varchar(255)  NULL,     /* command to be passed to executive subsystem */
         lastruncompletionlevel int           NOT NULL, /* completion level of last run (succeed, fail, running, abort, skipped) */
         lastrunduration        int           NOT NULL, /* duration of last run (hhmmss) */
         lastrunretries         int           NOT NULL, /* number of retries attempted for last run of this task */
         loghistcompletionlevel int           NOT NULL, /* completion level (succeed/fail/always) for which history records are logged */
         emailcompletionlevel   int           NOT NULL, /* 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) */
         parameters             text          NULL,     /* additional command parameters */
         cmdexecsuccesscode     int           NULL      /* process exit code returned when task is successful (CmdExec tasks only) */
		 )')

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


/************************************************************************/
/* Systasks upgrade (add 'parameters' and/or 'cmdexecsuccesscode'       */ 
/* column(s) if not already present)                                    */
/************************************************************************/
SET NOCOUNT ON
IF ((SELECT count(*)
     FROM msdb.dbo.syscolumns
     WHERE id = object_id('dbo.systasks')
     AND name in ('parameters', 'cmdexecsuccesscode')) < 2)
BEGIN
  PRINT ''
  PRINT 'Upgrading systasks definition...'
END
go

IF NOT EXISTS (SELECT *
               FROM msdb.dbo.syscolumns
               WHERE id = object_id('dbo.systasks')
                 AND name = 'parameters')
BEGIN
  ALTER TABLE msdb.dbo.systasks ADD parameters text NULL
END
go

IF NOT EXISTS (SELECT *
               FROM msdb.dbo.syscolumns
               WHERE id = object_id('dbo.systasks')
                 AND name = 'cmdexecsuccesscode')
BEGIN
  ALTER TABLE msdb.dbo.systasks ADD cmdexecsuccesscode int NULL
END
go


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


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

  EXEC ('CREATE TABLE syshistory
         (
         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             NOT NULL,   /* date of task run or alert (yyyymmdd) */
         runtime           int             NOT NULL,   /* time of task run or alert (hhmmss) */
         runduration       int             NOT NULL,   /* duration of task run (hhmmss) */
         reviewstatus      varchar(32)     NULL,       /* tbd */
         emailoperatorid   int             NULL,       /* id of operator emailed, if any */
         retries           int             NOT NULL,   /* retries attempted on this run */
         comments          varchar(255)    NULL,       /* tbd (subsystem entry) */
         timesskipped      int             NOT NULL    /* if nonzero, task skipped this many times due to down scheduler */
         )')

  CREATE UNIQUE CLUSTERED INDEX ucsyshistory ON syshistory(id)
  CREATE NONCLUSTERED INDEX uncsyshistory ON syshistory(taskid)
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 @max_rows int
  declare @current_rows int
  declare @current_rows_per_task int
  declare @limit_syshistory_rows int
  declare @taskid int
  declare @max_rows_per_task int
  declare @rows_to_delete varchar(10)

  select @max_rows_per_task = 0

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

  /* We only handle insertions one-at-a-time, so quit if we have more than one row */
  if ((select count(*) from inserted) > 1)
    return

  /* Get the task id for the history row being inserted */
  select @taskid = taskid
  from inserted

  /* Get the global maximum number of rows to keep from the registry */
  execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                'SyshistoryMaxRows',
                                @max_rows output

  /* Check that max_rows is > 1 */
  if (isnull(@max_rows, 0) < 2)
  begin
    select @max_rows = 1000
    execute master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                                   'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                   'SyshistoryMaxRows',
                                   'REG_DWORD',
                                    @max_rows
  end

  /* Get the per-task maximum number of rows to keep from the registry */
  execute master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                'TaskHistoryMaxRows',
                                @max_rows_per_task output

  /* Test that max_rows_per_task <= max_rows */
  if ((@max_rows_per_task > @max_rows) or (@max_rows_per_task < 1))
  begin
    select @max_rows_per_task = @max_rows
    execute master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
                                   'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                   'TaskHistoryMaxRows',
                                   'REG_DWORD',
                                    @max_rows_per_task
  end

  /* Get the number of rows in syshistory for the task in question (requires NC index on taskid) */
  select @current_rows_per_task = count(*)
  from msdb.dbo.syshistory
  where taskid = @taskid

  if (@max_rows_per_task > 0)
  begin
    /*
    ** Delete the oldest history row(s) for the task being inserted if the new row has
    ** pushed us over the per-task row limit
    */
    if (@current_rows_per_task > @max_rows_per_task)
    begin
      declare @task_id_as_char varchar(10)
      declare @max_id_as_char varchar(10)
      select @task_id_as_char = convert(varchar(10), @taskid)
      select @max_id_as_char = convert(varchar(10), max(id)) from msdb.dbo.syshistory
      select @rows_to_delete = convert(varchar, @current_rows_per_task - @max_rows_per_task)
      exec ('set rowcount ' + @rows_to_delete + ' delete from msdb.dbo.syshistory where taskid = ' + @task_id_as_char + ' and id < ' + @max_id_as_char)
    end
  end

  /* Get the number of rows in syshistory */
  select @current_rows = count(*)
  from msdb.dbo.syshistory

  /*
  ** Delete the oldest history row(s) if inserting the new row has pushed us over the
  ** global MaxSyshistoryRows limit
  ** NOTE: This logic depends on the table being clustered on id
  */
  if (@current_rows > @max_rows)
  begin
    select @rows_to_delete = convert(varchar, @current_rows - @max_rows)
    exec ('set rowcount ' + @rows_to_delete + ' delete from msdb.dbo.syshistory')
  end
end
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(50),
 @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)
	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(50) = 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,		
 @cmdexecsuccesscode    int = 0
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,
        cmdexecsuccesscode
	)
	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,
        @cmdexecsuccesscode
	)
	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(50) = 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,
 @cmdexecsuccesscode    int = 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(50),
			@tretryattempts			int,
			@tretrydelay			int,
			@tcommand				varchar(255),
			@tloghistcompletionlevel int,
			@temailcompletionlevel 	int,
			@tdescription			varchar(255),
			@townerloginid			int,
			@ttagadditionalinfo		varchar(96),
			@ttagobjectid			int,
			@ttagobjecttype			int,
	        @tcmdexecsuccesscode    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,
            @tcmdexecsuccesscode    = cmdexecsuccesscode
		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.sysoperators 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
    if (@cmdexecsuccesscode is not null) select @tcmdexecsuccesscode = @cmdexecsuccesscode

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

    /* Reset the temporary emailoperatorid since we are going to try to set it again below */
    select @temailoperatorid = 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,
		 @temailoperatorname, @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,
			cmdexecsuccesscode = @tcmdexecsuccesscode
		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,
			t.cmdexecsuccesscode
		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 (cmdexecsuccesscode    ) 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  ) or
	 (inserted.cmdexecsuccesscode     <> deleted.cmdexecsuccesscode    ))
	
	)  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,
cmdexecsuccesscode
from msdb.dbo.systasks_view 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
      RAISERROR(14222, -1, -1, @taskname)
      RETURN(1)
    END
  END
  ELSE
  /* Is the taskid valid? */
  BEGIN
    SELECT @taskname = name
    FROM msdb.dbo.systasks
    WHERE id = @taskid
    IF (@taskname = NULL)
    BEGIN
      RAISERROR(14223, -1, -1, @taskid)
      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


/**************************************************************/
/* SP_STOPTASK                                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_stoptask...'
go

CREATE PROCEDURE sp_stoptask
@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_stoptask <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
      RAISERROR(14222, -1, -1, @taskname)
      RETURN(1)
    END
  END
  ELSE
  /* Is the taskid valid? */
  BEGIN
    SELECT @taskname = name
    FROM msdb.dbo.systasks
    WHERE id = @taskid
    IF (@taskname = NULL)
    BEGIN
      RAISERROR(14223, -1, -1, @taskid)
      RETURN(1)
    END
  END

  /* Stop (cancel) the task */
  EXECUTE @retval = sp_schedulersignal 'R', @taskid, 'C'
  IF (@retval = 0)
  BEGIN
    SELECT @msg = 'Task #' + convert(varchar(10), @taskid) + ' requested to stop 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) null)
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
		/* Make sure that the tasks target database still exists.  If not, change it to 'master'. */
		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
		/* Make sure that the tasks username is correct for @ownerloginid in @databasename */
		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



/**************************************************************/
/**                                                          **/
/**          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
  DECLARE @NonAlertableErrors varchar(255)
  
  /* 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 ? */
  CREATE TABLE #TempRetVal (RetVal int)
  IF (@@version LIKE '%6.50.%')
  BEGIN
    EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
                                  'SOFTWARE\Microsoft\MSSQLServer\SQLExecutive',
                                  'NonAlertableErrors',
                                  @NonAlertableErrors output
    IF (isnull(@NonAlertableErrors, 'NULL') != 'NULL')
    BEGIN
      DECLARE @message_is_as_char varchar(128)
	  SELECT @message_is_as_char = convert(varchar(128), @message_id)
	  INSERT INTO #TempRetVal 
      EXEC ('IF (' + @message_is_as_char + ' in (' + @NonAlertableErrors + ')) SELECT 1')
    END
  END
  ELSE
  BEGIN
    IF (@message_id IN (1204, 4002))
      INSERT INTO #TempRetVal SELECT 1
  END
  IF EXISTS (SELECT * FROM #TempRetVal)
  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

  /* Remove leading and trailing spaces from the name (the UI already does this, so this */
  /* step is just to protect the command line user)                                      */
  SELECT @name = ltrim(rtrim(@name))

  /* 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 = isnull(@message_id, 0)) OR (severity = isnull(@severity, 0)))
    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

  /* Remove leading and trailing spaces from the [new] name (the UI already does this, so */
  /* this step is just to protect the command line user)                                  */
  SELECT @new_name = ltrim(rtrim(@new_name))

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

  CREATE TABLE #TempAlertID (AlertID int NOT NULL)
  INSERT INTO #TempAlertID
    SELECT id 
    FROM msdb.dbo.sysalerts 
    WHERE name LIKE @having_name_like

  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.id IN (SELECT AlertID FROM #TempAlertID))
        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 NULL)')
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             not null       -- 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_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


/**************************************************************/
/* SET THE 'SYSTEM-OBJECT' CATEGORY BIT                       */
/**************************************************************/
EXEC sp_MS_upd_sysobj_category 2
go



/**************************************************************/
/**                                                          **/
/**               M A S T E R  D A T A B A S E               **/
/**             S T O R E D  P R O C E D U R E S             **/
/**                                                          **/
/**************************************************************/

PRINT 'Creating ''master'' database stored procedures...'

USE master
go

/**************************************************************/
/* SET THE 'SYSTEM-OBJECT' CATEGORY BIT                       */
/**************************************************************/
EXEC sp_MS_upd_sysobj_category 1
go

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

/**************************************************************/
/* SP_SCHEDULERSIGNAL                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_schedulersignal...'
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, C (required for OpType R only). */
)
AS
BEGIN
  DECLARE @RetVal int
  SELECT @RetVal = 0 /* Success */

  /* Make sure that we're dealing only with uppercase characters */
  SELECT @OpType = upper(@OpType)
  IF (@TriggerType <> NULL)
  BEGIN
    SELECT @TriggerType = upper(@TriggerType)   
  END

  /* Parameter Validation */
  IF (charindex(@OpType, 'ROITAGDE') = 0)
  BEGIN
    RAISERROR (14249, -1, -1, 'R, O, I, T, A, G, D or E')
    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, 'IUDC') = 0))
  BEGIN
    RAISERROR (14251, -1, -1, 'I, U, D or C')
    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


/**************************************************************/
/* SP_GET_VOLUME_LABEL (a 'master' database proc)             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_get_volume_label...'
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


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

PRINT ''
PRINT 'Creating procedure sp_sysbackuphistory_limiter...'
go
create procedure sp_sysbackuphistory_limiter
(
@max_rows int=0,
@max_rows_per_db int = 0
)

as
begin
  declare @dbname varchar(31)
  declare @numrows int
  declare @current_row int
  declare @backupid int

  declare @rows_to_delete int


  if (@max_rows_per_db > 0)
  begin 
    /* Get the number of rows in syshistory for the task in question (requires NC index on taskid) */
    DECLARE rows_cursor INSENSITIVE CURSOR for 
    select database_name,count(*)
    from msdb.dbo.sysbackuphistory
    group by database_name for READ ONLY 
    open rows_cursor
    Fetch NEXT FROM rows_cursor into @dbname,@numrows
    while (@@FETCH_STATUS =0)
    begin
 
      /*
      ** Delete the oldest history row(s) for the task being inserted if the new row has
      ** pushed us over the per-task row limit
      */
      if (@numrows > @max_rows_per_db)
      begin
 
        select @rows_to_delete = @numrows - @max_rows_per_db

		DECLARE history_cursor INSENSITIVE CURSOR for 
  			select backup_id from msdb.dbo.sysbackuphistory where database_name=@dbname

  		open history_cursor
  		Fetch NEXT FROM history_cursor into @backupid
			select @current_row=0
  		while (@@FETCH_STATUS =0 AND @current_row<@rows_to_delete)
  		begin
		  delete from msdb.dbo.sysbackupdetail where backup_id=@backupid
		  delete from msdb.dbo.sysbackuphistory where backup_id=@backupid
	      Fetch NEXT FROM history_cursor into @backupid
		  select @current_row=@current_row+1
      	end
		deallocate history_cursor
	  end
	  Fetch NEXT FROM rows_cursor into @dbname,@numrows
	end
    deallocate rows_cursor
  end
  if (@max_rows>0)
  begin
	select @numrows = count(*) from msdb.dbo.sysbackuphistory
	if (@numrows > @max_rows)
	begin
	    select @rows_to_delete= @numrows-@max_rows
		DECLARE max_cursor INSENSITIVE CURSOR for 
  			select backup_id from msdb.dbo.sysbackuphistory

  		open max_cursor
  		Fetch NEXT FROM max_cursor into @backupid
		select @current_row=0
  		while (@@FETCH_STATUS =0 AND @current_row<@rows_to_delete)
  		begin
		  delete from msdb.dbo.sysbackupdetail where backup_id=@backupid
		  delete from msdb.dbo.sysbackuphistory where backup_id=@backupid
	      Fetch NEXT FROM max_cursor into @backupid
		  select @current_row=@current_row+1
      	end
		deallocate max_cursor

    end
  end
end
go


/**************************************************************/
/* SET THE 'SYSTEM-OBJECT' CATEGORY BIT                       */
/**************************************************************/
EXEC sp_MS_upd_sysobj_category 2
go

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

USE msdb
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_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 EXEC ON sp_runtask TO public')
EXEC('GRANT EXEC ON sp_stoptask 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('GRANT SELECT ON sysbackuphistory TO public')
EXEC('GRANT SELECT ON sysbackupdetail TO public')
EXEC('GRANT SELECT ON sysrestorehistory TO public')
EXEC('GRANT SELECT ON sysrestoredetail TO public')
EXEC('GRANT EXEC ON sp_sysbackuphistory TO public')
EXEC('USE master GRANT EXEC ON sp_get_volume_label TO public USE msdb')
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 */
