  CREATE TABLE backupmediaset
  (
  media_set_id       INT IDENTITY     NOT NULL PRIMARY KEY,
  media_uuid         UNIQUEIDENTIFIER NULL,  -- Null if this media set only one media family
  media_family_count TINYINT          NULL,  -- Number of media families in the media set
  name               NVARCHAR(128)    NULL,
  description        NVARCHAR(255)    NULL,
  software_name      NVARCHAR(128)    NULL,
  software_vendor_id INT              NULL,
  MTF_major_version  TINYINT          NULL,
  mirror_count       TINYINT          NULL,   -- number of mirror plexes
  is_password_protected BIT           NULL,
  is_compressed      BIT              NULL    -- 1 if backup compression was used
  )

  CREATE INDEX backupmediasetuuid ON backupmediaset (media_uuid)
END
ELSE
BEGIN
  IF EXISTS (
    select * from msdb.dbo.syscolumns where name='password_protected' and id =
        (select id from msdb.dbo.sysobjects where name='backupmediaset'))

    ALTER TABLE backupmediaset DROP COLUMN password_protected

  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='is_password_protected' and id =
        (select id from msdb.dbo.sysobjects where name='backupmediaset'))

    ALTER TABLE backupmediaset ADD is_password_protected BIT NULL

  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='is_compressed' and id =
        (select id from msdb.dbo.sysobjects where name='backupmediaset'))

    ALTER TABLE backupmediaset ADD is_compressed BIT NULL

  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='mirror_count' and id =
        (select id from msdb.dbo.sysobjects where name='backupmediaset'))

    ALTER TABLE backupmediaset ADD mirror_count TINYINT NULL

END
go

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

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

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

  CREATE INDEX backupmediafamilyuuid ON backupmediafamily (media_family_id)
END
ELSE
BEGIN
  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='mirror' and id =
        (select id from msdb.dbo.sysobjects where name='backupmediafamily'))
  BEGIN
    begin tran

    -- remove any old constraint, not involving mirror
   declare @pkName sysname
   DECLARE @sql NVARCHAR(4000)
   select @pkName=i.name
      from
         sys.indexes i,
         sys.all_objects o
      where
         o.object_id = object_id ('backupmediafamily')
         and o.object_id = i.object_id
         and i.is_primary_key = 1
   IF (@pkName IS NOT NULL)
   begin
      select @sql = N'ALTER TABLE backupmediafamily DROP CONSTRAINT ' + @pkName
      EXEC (@sql)
   end

   ALTER TABLE backupmediafamily ADD mirror TINYINT DEFAULT 0 NOT NULL

   ALTER TABLE backupmediafamily ADD CONSTRAINT backupmediafamily_PK
      PRIMARY KEY (media_set_id, family_sequence_number, mirror)

   commit    
  END
END
go

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

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

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

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

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

  -- From CONFIG_INFO...
  first_lsn              NUMERIC(25,0)    NULL,
  last_lsn               NUMERIC(25,0)    NULL,
  checkpoint_lsn         NUMERIC(25,0)    NULL,
  database_backup_lsn    NUMERIC(25,0)    NULL,
  database_creation_date DATETIME         NULL,
  backup_start_date      DATETIME         NULL,
  backup_finish_date     DATETIME         NULL,
  type                   CHAR(1)          NULL,
  sort_order             SMALLINT         NULL,
  code_page              SMALLINT         NULL,
  compatibility_level    TINYINT          NULL,
  database_version       INT              NULL,
  backup_size            NUMERIC(20,0)    NULL,
  database_name          NVARCHAR(128)    NULL,
  server_name            NVARCHAR(128)    NULL,
  machine_name           NVARCHAR(128)    NULL,
  flags                  INT              NULL,
  unicode_locale         INT              NULL,
  unicode_compare_style  INT              NULL,
  collation_name         NVARCHAR(128)    NULL,
  is_password_protected  BIT              NULL,
  recovery_model         NVARCHAR(60)     NULL,
  has_bulk_logged_data   BIT              NULL,
  is_snapshot            BIT              NULL,
  is_readonly            BIT              NULL,
  is_single_user         BIT              NULL,
  has_backup_checksums   BIT              NULL,
  is_damaged             BIT              NULL,
  begins_log_chain       BIT              NULL,
  has_incomplete_metadata BIT             NULL,
  is_force_offline       BIT              NULL,
  is_copy_only           BIT              NULL,
  first_recovery_fork_guid UNIQUEIDENTIFIER NULL,
  last_recovery_fork_guid UNIQUEIDENTIFIER NULL,
  fork_point_lsn         NUMERIC(25,0)    NULL,
  database_guid          UNIQUEIDENTIFIER NULL,
  family_guid            UNIQUEIDENTIFIER NULL,
  differential_base_lsn  NUMERIC(25,0)    NULL,
  differential_base_guid UNIQUEIDENTIFIER NULL,
  compressed_backup_size NUMERIC(20,0)    NULL
  )

  CREATE INDEX backupsetuuid ON backupset (backup_set_uuid)
  CREATE INDEX backupsetDate ON backupset (backup_finish_date) -- helps sp_delete_backuphistory
END
ELSE
BEGIN
  IF EXISTS (
    select * from msdb.dbo.syscolumns where name='password_protected' and id =
        (select id from msdb.dbo.sysobjects where name='backupset'))

    ALTER TABLE backupset DROP COLUMN password_protected

  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='flags' and id =
        (select id from msdb.dbo.sysobjects where name='backupset'))

    ALTER TABLE backupset ADD flags INT NULL

  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='collation_name' and id =
        (select id from msdb.dbo.sysobjects where name='backupset'))

    ALTER TABLE backupset ADD
      unicode_locale         INT              NULL,
      unicode_compare_style  INT              NULL,
      collation_name         NVARCHAR(128)    NULL

  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='is_password_protected' and id =
        (select id from msdb.dbo.sysobjects where name='backupset'))

    ALTER TABLE backupset ADD is_password_protected BIT NULL

  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='compressed_backup_size' and id =
        (select id from msdb.dbo.sysobjects where name='backupset'))

    ALTER TABLE backupset ADD compressed_backup_size NUMERIC(20,0) NULL

  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='recovery_model' and id =
        (select id from msdb.dbo.sysobjects where name='backupset'))
   ALTER TABLE backupset ADD
      recovery_model         NVARCHAR(60)     NULL,
      has_bulk_logged_data   BIT              NULL,
      is_snapshot            BIT              NULL,
      is_readonly            BIT              NULL,
      is_single_user         BIT              NULL,
      has_backup_checksums   BIT              NULL,
      is_damaged             BIT              NULL,
      begins_log_chain       BIT              NULL,
      has_incomplete_metadata BIT             NULL,
      is_force_offline       BIT              NULL,
      is_copy_only           BIT              NULL,
      first_recovery_fork_guid UNIQUEIDENTIFIER NULL,
      last_recovery_fork_guid UNIQUEIDENTIFIER NULL,
      fork_point_lsn         NUMERIC(25,0)    NULL,
      database_guid          UNIQUEIDENTIFIER NULL,
      family_guid            UNIQUEIDENTIFIER NULL,
      differential_base_lsn  NUMERIC(25,0)    NULL,
      differential_base_guid UNIQUEIDENTIFIER NULL

  IF NOT EXISTS (
      SELECT *
      FROM msdb.sys.indexes
      WHERE (name = 'backupsetDate'))
    CREATE INDEX backupsetDate ON backupset (backup_finish_date)

END
go

/**************************************************************/
-- BACKUPFILE/FILEGROUP 
-- One row per file/filegroup backed up
/**************************************************************/

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

  CREATE TABLE backupfilegroup
  (
  backup_set_id          INT           NOT NULL REFERENCES backupset(backup_set_id),
  name                   NVARCHAR(128) NOT NULL,
  filegroup_id           INT           NOT NULL,
  filegroup_guid         UNIQUEIDENTIFIER NULL,   
  type                   CHAR(2)       NOT NULL,
  type_desc              NVARCHAR(60)  NOT NULL,
  is_default             BIT           NOT NULL,
  is_readonly            BIT           NOT NULL,
  log_filegroup_guid     UNIQUEIDENTIFIER NULL
  PRIMARY KEY (backup_set_id, filegroup_id)
  )
END
go

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

  CREATE TABLE backupfile
  (
  backup_set_id          INT           NOT NULL REFERENCES backupset(backup_set_id),
  first_family_number    TINYINT       NULL,     -- Family number & media number of he first media
  first_media_number     SMALLINT      NULL,     -- containing this file
  filegroup_name         NVARCHAR(128) NULL,
  page_size              INT           NULL,
  file_number            NUMERIC(10,0) NOT NULL,
  backed_up_page_count   NUMERIC(10,0) NULL,
  file_type              CHAR(1)       NULL,     -- database or log
  source_file_block_size NUMERIC(10,0) NULL,
  file_size              NUMERIC(20,0) NULL,
  logical_name           NVARCHAR(128) NULL,
  physical_drive         NVARCHAR(260) NULL,     -- Drive or partition name
  physical_name          NVARCHAR(260) NULL,     -- Remainder of physical (OS) filename
  state                  TINYINT       NULL,
  state_desc             NVARCHAR(64)  NULL,
  create_lsn             NUMERIC(25,0) NULL,
  drop_lsn               NUMERIC(25,0) NULL,
  file_guid              UNIQUEIDENTIFIER NULL,
  read_only_lsn          NUMERIC(25,0) NULL,
  read_write_lsn         NUMERIC(25,0) NULL,
  differential_base_lsn  NUMERIC(25,0) NULL,
  differential_base_guid UNIQUEIDENTIFIER NULL,
  backup_size            NUMERIC(20,0) NULL,
  filegroup_guid         UNIQUEIDENTIFIER NULL,
  is_readonly            BIT NULL,
  is_present             BIT NULL
  PRIMARY KEY (backup_set_id, file_number)
  )
END
ELSE
BEGIN
  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='state' and id =
        (select id from msdb.dbo.sysobjects where name='backupfile'))
  BEGIN
    -- we want NVARCHAR instead of VARCHAR
   ALTER TABLE backupfile ALTER COLUMN
      physical_drive         NVARCHAR(260) NULL
   ALTER TABLE backupfile ALTER COLUMN
      physical_name          NVARCHAR(260) NULL

    ALTER TABLE backupfile ADD
     state                  TINYINT       NULL,
     state_desc             NVARCHAR(64)  NULL,
     create_lsn             NUMERIC(25,0) NULL,
     drop_lsn               NUMERIC(25,0) NULL,
     file_guid              UNIQUEIDENTIFIER NULL,
     read_only_lsn          NUMERIC(25,0) NULL,
     read_write_lsn         NUMERIC(25,0) NULL,
     differential_base_lsn  NUMERIC(25,0) NULL,
     differential_base_guid UNIQUEIDENTIFIER NULL,
     backup_size            NUMERIC(20,0) NULL,
     filegroup_guid         UNIQUEIDENTIFIER NULL,
     is_readonly            BIT NULL,
     is_present             BIT NULL
  END
END
go

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

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

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

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

  CREATE INDEX restorehistorybackupset ON restorehistory (backup_set_id)
END

IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.syscolumns
                WHERE (name in ('stop_at_mark_name', 'stop_before'))
                AND (id = (SELECT id
                          FROM msdb.dbo.sysobjects
                          WHERE (name = 'restorehistory')))))
BEGIN
  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='stop_before' and id =
        (select id from msdb.dbo.sysobjects where name='restorehistory'))
  BEGIN
    PRINT ''
    PRINT 'Adding columns to table restorehistory...'

    ALTER TABLE restorehistory
      ADD
      stop_at_mark_name       NVARCHAR(128) NULL,
      stop_before             BIT           NULL
  END
END
go

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

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

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

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

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

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

/**************************************************************/
/* LOGMARKHISTORY - One row per log mark generated            */
/**************************************************************/

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

  CREATE TABLE logmarkhistory
  (
  database_name     NVARCHAR(128)   NOT NULL,
  mark_name         NVARCHAR(128)   NOT NULL,
  description       NVARCHAR(255)   NULL,
  user_name         NVARCHAR(128)   NOT NULL,
  lsn               NUMERIC(25,0)   NOT NULL,
  mark_time         DATETIME        NOT NULL
  )

  CREATE INDEX logmarkhistory1 ON logmarkhistory (database_name, mark_name)

  CREATE INDEX logmarkhistory2 ON logmarkhistory (database_name, lsn)
END
go

IF (EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'trig_backupset_delete')
                  AND (OBJECTPROPERTY(id, 'IsTrigger') != 0)))
BEGIN
  DROP TRIGGER trig_backupset_delete
END
go

CREATE TRIGGER trig_backupset_delete ON msdb.dbo.backupset FOR DELETE AS
BEGIN
  DELETE FROM msdb.dbo.logmarkhistory from deleted
  WHERE (msdb.dbo.logmarkhistory.database_name = deleted.database_name)
    AND (msdb.dbo.logmarkhistory.lsn >= deleted.first_lsn)
    AND (msdb.dbo.logmarkhistory.lsn < deleted.last_lsn)
END
go

/**************************************************************/
/* suspect_pages */
/**************************************************************/

IF (EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'badpagehistory')))
   DROP TABLE badpagehistory
go               
IF (EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = 'suspect_page_table')))
   DROP TABLE suspect_page_table
go               

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

  CREATE TABLE suspect_pages
  (
  database_id           INT          NOT NULL,
  file_id               INT          NOT NULL,
  page_id               bigint       NOT NULL, -- we only use unsigned 32bits
  event_type            INT          NOT NULL,
  error_count           INT          NOT NULL,
  last_update_date      DATETIME     NOT NULL DEFAULT GETDATE()
  )
END
go


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

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

-- Create the SQLAgentReaderRole role
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysusers
            WHERE (name = N'SQLAgentReaderRole')
              AND (issqlrole = 1)))
BEGIN
  -- If there are no members in the role, then drop and re-create it
  IF ((SELECT COUNT(*)
       FROM msdb.dbo.sysusers   su,
            msdb.dbo.sysmembers sm
       WHERE (su.uid = sm.groupuid)
         AND (su.name = N'SQLAgentReaderRole')
         AND (su.issqlrole = 1)) = 0)
  BEGIN
    EXECUTE msdb.dbo.sp_droprole @rolename = N'SQLAgentReaderRole'
    EXECUTE msdb.dbo.sp_addrole @rolename = N'SQLAgentReaderRole'
  END
END
ELSE
  EXECUTE msdb.dbo.sp_addrole @rolename = N'SQLAgentReaderRole'  

-- Create the SQLAgentOperatorRole role
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysusers
            WHERE (name = N'SQLAgentOperatorRole')
              AND (issqlrole = 1)))
BEGIN
  -- If there are no members in the role, then drop and re-create it
  IF ((SELECT COUNT(*)
       FROM msdb.dbo.sysusers   su,
            msdb.dbo.sysmembers sm
       WHERE (su.uid = sm.groupuid)
         AND (su.name = N'SQLAgentOperatorRole')
         AND (su.issqlrole = 1)) = 0)
  BEGIN
    EXECUTE msdb.dbo.sp_droprole @rolename = N'SQLAgentOperatorRole'
    EXECUTE msdb.dbo.sp_addrole @rolename = N'SQLAgentOperatorRole'
  END
END
ELSE
  EXECUTE msdb.dbo.sp_addrole @rolename = N'SQLAgentOperatorRole'  


-- Add roles to each other.
-- SQLAgentReaderRole is also SQLAgentUserRole
-- SQLAgentOperatorRole is also SQLAgentReaderRole and SQLAgentUserRole

EXECUTE sp_addrolemember @rolename = 'SQLAgentUserRole' , 
                   @membername = 'SQLAgentReaderRole' 

EXECUTE sp_addrolemember @rolename = 'SQLAgentReaderRole' , 
                   @membername = 'SQLAgentOperatorRole' 
go

GRANT EXECUTE ON sp_notify_operator          TO SQLAgentUserRole

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

GRANT EXECUTE ON sp_help_jobhistory  TO SQLAgentUserRole
GRANT EXECUTE ON sp_help_jobhistory_full TO SQLAgentUserRole
GRANT EXECUTE ON sp_help_jobhistory_summary TO SQLAgentUserRole
GRANT EXECUTE ON sp_help_jobhistory_sem TO SQLAgentUserRole

GRANT EXECUTE ON sp_purge_jobhistory  TO SQLAgentOperatorRole

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

GRANT EXECUTE ON sp_help_jobsteplog      TO SQLAgentUserRole
GRANT EXECUTE ON sp_delete_jobsteplog    TO SQLAgentUserRole

--Schedule related SP's
GRANT EXECUTE ON sp_add_schedule       TO SQLAgentUserRole
GRANT EXECUTE ON sp_update_schedule       TO SQLAgentUserRole
GRANT EXECUTE ON sp_delete_schedule       TO SQLAgentUserRole
GRANT EXECUTE ON sp_attach_schedule       TO SQLAgentUserRole
GRANT EXECUTE ON sp_detach_schedule       TO SQLAgentUserRole
GRANT EXECUTE ON sp_help_schedule         TO SQLAgentUserRole
GRANT EXECUTE ON sp_help_jobcount         TO SQLAgentUserRole
GRANT EXECUTE ON sp_help_jobs_in_schedule TO SQLAgentUserRole

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

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

--alert spocs
GRANT EXECUTE ON sp_help_alert              TO SQLAgentOperatorRole

--proxy sprocs
GRANT EXECUTE ON sp_help_proxy           TO SQLAgentUserRole
GRANT EXECUTE ON sp_enum_login_for_proxy TO SQLAgentOperatorRole

--other
GRANT EXECUTE ON sp_help_jobserver        TO SQLAgentUserRole
GRANT EXECUTE ON sp_help_targetserver    TO SQLAgentOperatorRole
GRANT EXECUTE ON sp_help_notification    TO SQLAgentOperatorRole

GRANT EXECUTE ON sp_check_for_owned_jobs     TO SQLAgentUserRole
GRANT EXECUTE ON sp_check_for_owned_jobsteps TO SQLAgentUserRole
GRANT EXECUTE ON sp_get_jobstep_db_username  TO SQLAgentUserRole
GRANT EXECUTE ON sp_get_job_alerts           TO SQLAgentUserRole

GRANT EXECUTE ON sp_uniquetaskname TO SQLAgentUserRole
GRANT EXECUTE ON sp_addtask        TO SQLAgentUserRole
GRANT EXECUTE ON sp_droptask       TO SQLAgentUserRole

GRANT SELECT ON sysjobs_view                    TO SQLAgentUserRole
GRANT SELECT ON sysschedules_localserver_view   TO SQLAgentUserRole

GRANT SELECT  ON sysnotifications        TO SQLAgentOperatorRole
GRANT SELECT  ON sysoperators            TO SQLAgentOperatorRole
GRANT SELECT  ON sysalerts               TO SQLAgentOperatorRole

REVOKE ALL ON systargetservers                 FROM PUBLIC
REVOKE ALL ON systargetservers_view            FROM PUBLIC
REVOKE ALL ON systargetservergroups            FROM PUBLIC
REVOKE ALL ON systargetservergroupmembers      FROM PUBLIC
REVOKE INSERT, UPDATE, DELETE ON syscategories FROM PUBLIC
REVOKE ALL ON sysalerts                        FROM PUBLIC
REVOKE ALL ON sysoperators                     FROM PUBLIC
REVOKE ALL ON sysnotifications                 FROM PUBLIC

REVOKE ALL ON systargetservers                 FROM SQLAgentUserRole
REVOKE ALL ON systargetservers_view            FROM SQLAgentUserRole
REVOKE ALL ON systargetservergroups            FROM SQLAgentUserRole
REVOKE ALL ON systargetservergroupmembers      FROM SQLAgentUserRole
REVOKE INSERT, UPDATE, DELETE ON syscategories FROM SQLAgentUserRole

REVOKE ALL ON sysalerts                        FROM SQLAgentUserRole
REVOKE ALL ON sysoperators                     FROM SQLAgentUserRole
REVOKE ALL ON sysnotifications                 FROM SQLAgentUserRole

--DENY TargetServerRole permission that would allow modifying of jobs
DENY ALL ON sp_add_jobserver     TO TargetServersRole
DENY ALL ON sp_delete_jobserver  TO TargetServersRole

DENY ALL ON sp_add_jobstep    TO TargetServersRole
DENY ALL ON sp_update_jobstep TO TargetServersRole
DENY ALL ON sp_delete_jobstep TO TargetServersRole

DENY ALL ON sp_add_jobschedule    TO TargetServersRole
DENY ALL ON sp_update_jobschedule TO TargetServersRole
DENY ALL ON sp_delete_jobschedule TO TargetServersRole

DENY ALL ON sp_add_job    TO TargetServersRole
DENY ALL ON sp_update_job TO TargetServersRole
DENY ALL ON sp_delete_job TO TargetServersRole
DENY ALL ON sp_start_job  TO TargetServersRole
DENY ALL ON sp_stop_job   TO TargetServersRole
DENY ALL ON sp_post_msx_operation       TO TargetServersRole

DENY ALL ON sp_addtask        TO TargetServersRole
DENY ALL ON sp_droptask       TO TargetServersRole


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

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

GRANT EXECUTE                ON sp_help_jobactivity           TO SQLAgentUserRole
GRANT EXECUTE                ON sp_help_operator              TO SQLAgentUserRole
                                                    
go

USE msdb                                                  
go                                                                          
                                                          
/**************************************************************/         
/**************************************************************/         
/* BEGIN DTS                                                  */         
/**************************************************************/         
/**************************************************************/

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

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

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

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

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


/***************************************************************/
/* Create SSIS roles										   */
/***************************************************************/
if not exists (select * from dbo.sysusers where [name] = N'db_ssisadmin' and [issqlrole] = 1)
BEGIN
EXEC sp_addrole N'db_ssisadmin' 
END
GO

if not exists (select * from dbo.sysusers where [name] = N'db_ssisltduser' and [issqlrole] = 1)
BEGIN
EXEC sp_addrole N'db_ssisltduser'
END
GO

if not exists (select * from dbo.sysusers where [name] = N'db_ssisoperator' and [issqlrole] = 1)
BEGIN
EXEC sp_addrole N'db_ssisoperator'
END
GO


/**************************************************************/
/* SP_MAKE_DTSPACKAGENAME                                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_get_dtsversion...'
go
IF OBJECT_ID(N'sp_get_dtsversion') IS NOT NULL
  DROP PROCEDURE sp_get_dtsversion
go
CREATE PROCEDURE sp_get_dtsversion
AS
  /* Values for this are same as @@microsoftversion */
  /* @@microsoftversion format is 0xaaiibbbb (aa = major, ii = minor, bb[bb] = build #) */
  DECLARE @i INT
  select @i = 0x08000000   /* Must be in hex! */

  /* Select the numeric value, and a conversion to make it readable */
  select N'Microsoft SQLDTS Scripts' = @i, N'Version' = convert(binary(4), @i)
GO
GRANT  EXECUTE  ON sp_get_dtsversion	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_get_dtsversion	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_get_dtsversion	TO [db_ssisoperator]
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_reassign_dtspackageowner	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_reassign_dtspackageowner	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_reassign_dtspackageowner	TO [db_ssisoperator]
GO

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

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

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

  RETURN 0    -- SUCCESS
go
GRANT  EXECUTE  ON sp_get_dtspackage	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_get_dtspackage	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_get_dtspackage	TO [db_ssisoperator]
go

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

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

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

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

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

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

  DO_RECURSE:
  DECLARE @packagesfound INT
  SELECT @packagesfound = 0

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

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

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

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

GRANT  EXECUTE  ON sp_enum_dtspackages	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_enum_dtspackages	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_enum_dtspackages	TO [db_ssisoperator]
go

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

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

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

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

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

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

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

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

  --// We now have a unique category.

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

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

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

  DELETE sysdtspackages FROM sysdtspackages INNER JOIN @recurse r ON sysdtspackages.categoryid = r.id
  DELETE sysdtscategories FROM sysdtscategories INNER JOIN @recurse r ON sysdtscategories.id = r.id

  RETURN(0) -- SUCCESS
go

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

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

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

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

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

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

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

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

  DO_RECURSE:

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

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

/**************************************************************/
/* Drop Beta1 DTS Logging objects                             */
/**************************************************************/

if OBJECT_ID('sysdtspackagestepslog') IS NOT NULL
BEGIN
   PRINT ''
   PRINT 'Dropping Beta1 logging tables and stored procedures...'
   DROP TABLE sysdtspackagestepslog
   IF OBJECT_ID('sysdtspackagelog') IS NOT NULL
      DROP TABLE sysdtspackagelog
   IF OBJECT_ID('sp_log_dtspackage') IS NOT NULL
      DROP PROCEDURE sp_log_dtspackage
   IF OBJECT_ID('sp_log_dtspackagesteps') IS NOT NULL
      DROP PROCEDURE sp_log_dtspackagesteps
END

/**************************************************************/
/* SYSDTSPACKAGELOG                                           */
/**************************************************************/
if OBJECT_ID('sysdtspackagelog') IS NULL
BEGIN
  PRINT ''
  PRINT 'Creating table sysdtspackagelog...'
  CREATE TABLE sysdtspackagelog
  (
    name          sysname        NOT NULL,
    description            NVARCHAR(1000)    NULL,
    id               UNIQUEIDENTIFIER  NOT NULL,
    versionid           UNIQUEIDENTIFIER  NOT NULL,
    lineagefull            UNIQUEIDENTIFIER  NOT NULL PRIMARY KEY,
    lineageshort        INT         NOT NULL,
    starttime           DATETIME    NOT NULL,
    endtime          DATETIME    NULL,
    elapsedtime            double precision  NULL,
    computer            sysname        NOT NULL,
    operator            sysname        NOT NULL,
    logdate          datetime    NOT NULL DEFAULT GETDATE(),
    errorcode           INT         NULL,
    errordescription       NVARCHAR(2000)    NULL
  )
END

/**************************************************************/
/* SYSDTSSTEPLOG                                              */
/**************************************************************/
if OBJECT_ID('sysdtssteplog') IS NULL
BEGIN
  PRINT ''
  PRINT 'Creating table sysdtssteplog...'
  CREATE TABLE sysdtssteplog
  (
    stepexecutionid        BIGINT IDENTITY (1, 1)  NOT NULL PRIMARY KEY,
    lineagefull            UNIQUEIDENTIFIER  NOT NULL 
               REFERENCES sysdtspackagelog(lineagefull)
               ON DELETE CASCADE,
    stepname            sysname        NOT NULL,
    stepexecstatus         int            NULL,
    stepexecresult         int            NULL,
    starttime           DATETIME    NOT NULL,
    endtime          DATETIME    NULL,
    elapsedtime            double precision  NULL,
    errorcode           INT         NULL,
    errordescription       NVARCHAR(2000)    NULL,
    progresscount       BIGINT         NULL
  )
END ELSE BEGIN
  IF (NOT EXISTS (SELECT *
                  FROM msdb.dbo.syscolumns
                  WHERE name = N'stepexecresult' AND id = OBJECT_ID(N'sysdtssteplog')))
  BEGIN
    PRINT ''
    PRINT 'Altering table sysdtssteplog...'
    ALTER TABLE sysdtssteplog ADD stepexecresult INT NULL DEFAULT 0
  END
END

/**************************************************************/
/* SYSDTSTASKLOG                                              */
/**************************************************************/
if OBJECT_ID('sysdtstasklog') IS NULL
BEGIN
  PRINT ''
  PRINT 'Creating table sysdtstasklog...'
  CREATE TABLE sysdtstasklog
  (
    stepexecutionid        BIGINT         NOT NULL
               REFERENCES sysdtssteplog (stepexecutionid)
               ON DELETE CASCADE,
    sequenceid          INT         NOT NULL,
    errorcode           INT         NOT NULL,
    description            NVARCHAR(2000)    NULL,
    PRIMARY KEY            (stepexecutionid, sequenceid)
  )
END

/**************************************************************/
/* SP_LOG_DTSPACKAGE_BEGIN                                    */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_dtspackage_begin...'
GO
IF OBJECT_ID(N'sp_log_dtspackage_begin') IS NOT NULL
  DROP PROCEDURE sp_log_dtspackage_begin
GO
CREATE PROCEDURE sp_log_dtspackage_begin
  @name        sysname,
  @description    NVARCHAR(1000),
  @id       UNIQUEIDENTIFIER,
  @versionid      UNIQUEIDENTIFIER,
  @lineagefull    UNIQUEIDENTIFIER,
  @lineageshort      INT,
  @starttime      DATETIME,
  @computer    sysname,
  @operator    sysname
AS
  SET NOCOUNT ON

  INSERT sysdtspackagelog (
    name,
    description,
    id,
    versionid,
    lineagefull,
    lineageshort,
    starttime,
    computer,
    operator
  ) VALUES (
    @name,
    @description,
    @id,
    @versionid,
    @lineagefull,
    @lineageshort,
    @starttime,
    @computer,
    @operator
  )
  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_log_dtspackage_begin	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_log_dtspackage_begin	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_log_dtspackage_begin	TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_LOG_DTSPACKAGE_END                                      */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_dtspackage_end...'
GO
IF OBJECT_ID(N'sp_log_dtspackage_end') IS NOT NULL
  DROP PROCEDURE sp_log_dtspackage_end
GO
CREATE PROCEDURE sp_log_dtspackage_end
  @lineagefull    UNIQUEIDENTIFIER,
  @endtime     DATETIME,
  @elapsedtime    double precision,
  @errorcode      INT,
  @errordescription  NVARCHAR(2000)
AS
  SET NOCOUNT ON

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

  UPDATE sysdtspackagelog
    SET 
        endtime = @endtime,
        elapsedtime = @elapsedtime,
        errorcode = @errorcode,
        errordescription = @errordescription
    WHERE lineagefull = @lineagefull

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_log_dtspackage_end	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_log_dtspackage_end	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_log_dtspackage_end	TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_LOG_DTSSTEP_BEGIN                                       */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_dtsstep_begin...'
GO
IF OBJECT_ID(N'sp_log_dtsstep_begin') IS NOT NULL
  DROP PROCEDURE sp_log_dtsstep_begin
GO
CREATE PROCEDURE sp_log_dtsstep_begin
  @lineagefull    UNIQUEIDENTIFIER,
  @stepname    sysname,
  @starttime      DATETIME
AS
  SET NOCOUNT ON

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

  INSERT sysdtssteplog (
    lineagefull,
    stepname,
    starttime
  ) VALUES (
    @lineagefull,
    @stepname,
    @starttime
  )

  --// Return the @@identity for sp_log_dtstask and sp_logdtsstep_end
  SELECT @@IDENTITY

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_log_dtsstep_begin	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_log_dtsstep_begin	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_log_dtsstep_begin	TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_LOG_DTSSTEP_END                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_dtsstep_end...'
GO
IF OBJECT_ID(N'sp_log_dtsstep_end') IS NOT NULL
  DROP PROCEDURE sp_log_dtsstep_end
GO
CREATE PROCEDURE sp_log_dtsstep_end
  @stepexecutionid   BIGINT,
  @stepexecstatus int,
  @stepexecresult int,
  @endtime     DATETIME,
  @elapsedtime    double precision,
  @errorcode      INT,
  @errordescription  NVARCHAR(2000),
  @progresscount  BIGINT
AS
  SET NOCOUNT ON

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

  UPDATE sysdtssteplog
    SET 
        stepexecstatus = @stepexecstatus,
        stepexecresult = @stepexecresult,
        endtime = @endtime,
        elapsedtime = @elapsedtime,
        errorcode = @errorcode,
        errordescription = @errordescription,
        progresscount = @progresscount
    WHERE stepexecutionid = @stepexecutionid

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_log_dtsstep_end	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_log_dtsstep_end	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_log_dtsstep_end	TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_LOG_DTSTASK                                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_dtstask...'
GO
IF OBJECT_ID(N'sp_log_dtstask') IS NOT NULL
  DROP PROCEDURE sp_log_dtstask
GO
CREATE PROCEDURE sp_log_dtstask
  @stepexecutionid   BIGINT,
  @sequenceid     INT,
  @errorcode      INT,
  @description    NVARCHAR(2000)
AS
  SET NOCOUNT ON

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

  INSERT sysdtstasklog (
    stepexecutionid,
    sequenceid,
    errorcode,
    description
  ) VALUES (
    @stepexecutionid,
    @sequenceid,
    @errorcode,
    @description
  )

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_log_dtstask	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_log_dtstask	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_log_dtstask	TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_ENUM_DTSPACKAGELOG                                      */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_enum_dtspackagelog...'
GO
IF OBJECT_ID(N'sp_enum_dtspackagelog') IS NOT NULL
  DROP PROCEDURE sp_enum_dtspackagelog
GO
CREATE PROCEDURE sp_enum_dtspackagelog
  @name sysname,
  @flags INT = 0,                --// Bitmask:  0x01 == return only latest
  @id UNIQUEIDENTIFIER = NULL,      --// If non-NULL, use instead of @name.
  @versionid UNIQUEIDENTIFIER = NULL,  --// If non-NULL, use instead of @id or @name
  @lineagefull UNIQUEIDENTIFIER = NULL --// If non-NULL, use instead of @versionid or @id or @name
AS
  SET NOCOUNT ON

  --// This is used for realtime viewing of package logs, so don't error if no entries
  --// found, simply return an empty result set.
  SELECT
    p.name,
    p.description,
    p.id,
    p.versionid,
    p.lineagefull,
    p.lineageshort,
    p.starttime,
    p.endtime,
    p.elapsedtime,
    p.computer,
    p.operator,
    p.logdate,
    p.errorcode,
    p.errordescription
  FROM sysdtspackagelog p
  WHERE ((@lineagefull IS NULL OR p.lineagefull = @lineagefull)
      AND  (@versionid IS NULL OR p.versionid = @versionid)
      AND (@id IS NULL OR p.id = @id)
      AND (@name IS NULL OR p.name = @name))
    AND ((@flags & 0x01) = 0
      OR p.logdate = 
      (
        SELECT MAX(logdate) 
        FROM sysdtspackagelog d
        WHERE (d.id = p.id)
      )
     )
  ORDER BY logdate 

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_enum_dtspackagelog	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_enum_dtspackagelog	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_enum_dtspackagelog	TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_ENUM_DTSSTEPLOG                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_enum_dtssteplog...'
GO
IF OBJECT_ID(N'sp_enum_dtssteplog') IS NOT NULL
  DROP PROCEDURE sp_enum_dtssteplog
GO
CREATE PROCEDURE sp_enum_dtssteplog
  @lineagefull    UNIQUEIDENTIFIER = NULL,   -- all steps in this package execution
  @stepexecutionid   BIGINT = NULL
AS
  SET NOCOUNT ON

  --// This is used for realtime viewing of package logs, so don't error if no entries
  --// found, simply return an empty result set.
  --// This query must be restricted within a single package execution (lineage); it may
  --// be further restricted by stepexecutionid to a single step within that package execution.
  SELECT
    stepexecutionid,
    lineagefull,
    stepname,
    stepexecstatus,
    stepexecresult,
    starttime,
    endtime,
    elapsedtime,
    errorcode,
    errordescription,
    progresscount
  FROM sysdtssteplog
  WHERE (@lineagefull IS NULL OR lineagefull = @lineagefull)
    AND (@stepexecutionid IS NULL OR stepexecutionid = @stepexecutionid)
  ORDER BY stepexecutionid

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_enum_dtssteplog	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_enum_dtssteplog	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_enum_dtssteplog	TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_ENUM_DTSTASKLOG                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_enum_dtstasklog...'
GO
IF OBJECT_ID(N'sp_enum_dtstasklog') IS NOT NULL
  DROP PROCEDURE sp_enum_dtstasklog
GO
CREATE PROCEDURE sp_enum_dtstasklog
  @stepexecutionid   BIGINT,
  @sequenceid     INT = NULL
AS
  SET NOCOUNT ON

  --// This is used for realtime viewing of package logs, so don't error if no entries
  --// found, simply return an empty result set.
  --// This query must be restricted within a single step execution; it may
  --// be further restricted by stepexecutionid to a single record within that step execution.
  SELECT
    -- stepexecutionid,  -- this is always passed in so we don't need to return it.
    sequenceid,
    errorcode,
    description
  FROM sysdtstasklog
  WHERE (stepexecutionid IS NULL or stepexecutionid = @stepexecutionid)
    AND (@sequenceid IS NULL OR sequenceid = @sequenceid)
  ORDER BY sequenceid

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_enum_dtstasklog	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_enum_dtstasklog	TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_enum_dtstasklog	TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_DUMP_DTSLOG_ALL                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_dump_dtslog_all...'
GO
IF OBJECT_ID(N'sp_dump_dtslog_all') IS NOT NULL
  DROP PROCEDURE sp_dump_dtslog_all
GO
CREATE PROCEDURE sp_dump_dtslog_all
AS
  SET NOCOUNT ON

  --// sysadmin only.
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  DELETE sysdtspackagelog
  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_dump_dtslog_all		TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_dump_dtslog_all		TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_dump_dtslog_all		TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_DUMP_DTSPACKAGELOG                                      */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_dump_dtspackagelog...'
GO
IF OBJECT_ID(N'sp_dump_dtspackagelog') IS NOT NULL
  DROP PROCEDURE sp_dump_dtspackagelog
GO
CREATE PROCEDURE sp_dump_dtspackagelog
  @name sysname,
  @flags INT = 0,                --// Bitmask:  0x01 == preserve latest
  @id UNIQUEIDENTIFIER = NULL,      --// If non-NULL, use instead of @name.
  @versionid UNIQUEIDENTIFIER = NULL,  --// If non-NULL, use instead of @id or @name
  @lineagefull UNIQUEIDENTIFIER = NULL --// If non-NULL, use instead of @versionid or @id or @name
AS
  SET NOCOUNT ON

  --// sysadmin only.
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  --// Don't error if no entries found, as the desired result will be met.
  --// DELETE will CASCADE
  DELETE sysdtspackagelog
  FROM sysdtspackagelog p
  WHERE ((@lineagefull IS NULL OR p.lineagefull = @lineagefull)
      AND  (@versionid IS NULL OR p.versionid = @versionid)
      AND (@id IS NULL OR p.id = @id)
      AND (@name IS NULL OR p.name = @name))
    AND ((@flags & 0x01) = 0
      OR p.logdate < 
      (
        SELECT MAX(logdate) 
        FROM sysdtspackagelog d
        WHERE (d.id = p.id)
      )
     )

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_dump_dtspackagelog	TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_dump_dtspackagelog    TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_dump_dtspackagelog    TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_DUMP_DTSSTEPLOG                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_dump_dtssteplog...'
GO
IF OBJECT_ID(N'sp_dump_dtssteplog') IS NOT NULL
  DROP PROCEDURE sp_dump_dtssteplog
GO
CREATE PROCEDURE sp_dump_dtssteplog
  @lineagefull    UNIQUEIDENTIFIER = NULL,   -- all steps in this package execution
  @stepexecutionid   BIGINT = NULL
AS
  SET NOCOUNT ON

  --// sysadmin only.
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  --// Don't error if no entries found, as the desired result will be met.
  --// DELETE will CASCADE
  DELETE sysdtssteplog
  WHERE (@lineagefull IS NULL OR lineagefull = @lineagefull)
    AND (@stepexecutionid IS NULL OR stepexecutionid = @stepexecutionid)

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_dump_dtssteplog   TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_dump_dtssteplog   TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_dump_dtssteplog   TO [db_ssisoperator]
GO

/**************************************************************/
/* SP_DUMP_DTSTASKLOG                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_dump_dtstasklog...'
GO
IF OBJECT_ID(N'sp_dump_dtstasklog') IS NOT NULL
  DROP PROCEDURE sp_dump_dtstasklog
GO
CREATE PROCEDURE sp_dump_dtstasklog
  @stepexecutionid   BIGINT,
  @sequenceid     INT = NULL
AS
  SET NOCOUNT ON

  --// sysadmin only.
  IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
  BEGIN
    RAISERROR(15003, 16, 1, N'sysadmin')
    RETURN(1) -- Failure
  END

  --// Don't error if no entries found, as the desired result will be met.
  DELETE sysdtstasklog
  WHERE (stepexecutionid IS NULL or stepexecutionid = @stepexecutionid)
    AND (@sequenceid IS NULL OR sequenceid = @sequenceid)

  RETURN 0    -- SUCCESS
GO
GRANT  EXECUTE  ON sp_dump_dtstasklog   TO [db_ssisadmin]
GRANT  EXECUTE  ON sp_dump_dtstasklog   TO [db_ssisltduser]
GRANT  EXECUTE  ON sp_dump_dtstasklog   TO [db_ssisoperator]
GO


/**************************************************************/
/* SP_DTS_SECURE                                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_dts_secure ...'
GO
IF OBJECT_ID(N'sp_dts_secure') IS NOT NULL
	DROP PROCEDURE sp_dts_secure
GO
CREATE PROCEDURE sp_dts_secure
	@flag	INT
AS
	SET NOCOUNT ON

	--// sysadmin only.
	IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1)
	BEGIN
		RAISERROR(15003, 16, 1, N'sysadmin')
		RETURN(1) -- Failure
	END
	
	IF(@flag <> 0)
	BEGIN
		REVOKE EXECUTE ON sp_get_dtsversion				FROM PUBLIC
		REVOKE EXECUTE ON sp_make_dtspackagename		FROM PUBLIC
		REVOKE EXECUTE ON sp_add_dtspackage				FROM PUBLIC
		REVOKE EXECUTE ON sp_drop_dtspackage			FROM PUBLIC
		REVOKE EXECUTE ON sp_reassign_dtspackageowner	FROM PUBLIC
		REVOKE EXECUTE ON sp_get_dtspackage				FROM PUBLIC
		REVOKE EXECUTE ON sp_enum_dtspackages			FROM PUBLIC
		REVOKE EXECUTE ON sp_log_dtspackage_begin		FROM PUBLIC
		REVOKE EXECUTE ON sp_log_dtspackage_end			FROM PUBLIC
		REVOKE EXECUTE ON sp_log_dtsstep_begin			FROM PUBLIC
		REVOKE EXECUTE ON sp_log_dtsstep_end			FROM PUBLIC
		REVOKE EXECUTE ON sp_log_dtstask				FROM PUBLIC
		REVOKE EXECUTE ON sp_enum_dtspackagelog			FROM PUBLIC
		REVOKE EXECUTE ON sp_enum_dtssteplog			FROM PUBLIC
		REVOKE EXECUTE ON sp_enum_dtstasklog			FROM PUBLIC
		REVOKE EXECUTE ON sp_dump_dtslog_all			FROM PUBLIC
		REVOKE EXECUTE ON sp_dump_dtspackagelog			FROM PUBLIC
		REVOKE EXECUTE ON sp_dump_dtssteplog			FROM PUBLIC
		REVOKE EXECUTE ON sp_dump_dtstasklog			FROM PUBLIC
	END
	ELSE
	BEGIN
		GRANT EXECUTE ON sp_get_dtsversion				TO PUBLIC
		GRANT EXECUTE ON sp_make_dtspackagename			TO PUBLIC
		GRANT EXECUTE ON sp_add_dtspackage				TO PUBLIC
		GRANT EXECUTE ON sp_drop_dtspackage				TO PUBLIC
		GRANT EXECUTE ON sp_reassign_dtspackageowner	TO PUBLIC
		GRANT EXECUTE ON sp_get_dtspackage				TO PUBLIC
		GRANT EXECUTE ON sp_enum_dtspackages			TO PUBLIC
		GRANT EXECUTE ON sp_log_dtspackage_begin		TO PUBLIC
		GRANT EXECUTE ON sp_log_dtspackage_end			TO PUBLIC
		GRANT EXECUTE ON sp_log_dtsstep_begin			TO PUBLIC
		GRANT EXECUTE ON sp_log_dtsstep_end				TO PUBLIC
		GRANT EXECUTE ON sp_log_dtstask					TO PUBLIC
		GRANT EXECUTE ON sp_enum_dtspackagelog			TO PUBLIC
		GRANT EXECUTE ON sp_enum_dtssteplog				TO PUBLIC
		GRANT EXECUTE ON sp_enum_dtstasklog				TO PUBLIC
		GRANT EXECUTE ON sp_dump_dtslog_all				TO PUBLIC
		GRANT EXECUTE ON sp_dump_dtspackagelog			TO PUBLIC
		GRANT EXECUTE ON sp_dump_dtssteplog				TO PUBLIC
		GRANT EXECUTE ON sp_dump_dtstasklog				TO PUBLIC
	END
	
	RETURN 0
GO



/**************************************************************/
/*                                                            */
/*                  D A T A B A S E    M A I L                */
/*                                                            */
/**************************************************************/

/**************************************************************/
/*                                                            */
/*  Database Mail Tables                                      */
/*                                                            */
/**************************************************************/

----------------------------------------------------------------
-- Database Mail: general configuraiton tables
----------------------------------------------------------------

IF (OBJECT_ID(N'dbo.sysmail_profile', 'U') IS NULL)
BEGIN
   PRINT ''
   PRINT 'Creating table sysmail_profile...'
   
   CREATE TABLE dbo.sysmail_profile
   (
      profile_id int identity not null,
      name sysname not null,
      description nvarchar(256) null,
      last_mod_datetime datetime not null default getdate(),
      last_mod_user sysname not null default suser_sname()

      CONSTRAINT [SYSMAIL_PROFILE_IDMustBeUnique] PRIMARY KEY(profile_id),
      CONSTRAINT [SYSMAIL_PROFILE_NameMustBeUnique] UNIQUE (name)
   )
END
ELSE
BEGIN
  ALTER TABLE dbo.sysmail_profile ALTER COLUMN description nvarchar(256) null
END
go

IF (OBJECT_ID(N'dbo.sysmail_principalprofile', 'U') IS NULL)
BEGIN
   PRINT ''
   PRINT 'Creating table sysmail_principalprofile...'
   
   CREATE TABLE dbo.sysmail_principalprofile
   (
      profile_id int not null references sysmail_profile(profile_id) on delete cascade,
      principal_sid varbinary(85) not null, -- sys.database_principals.sid
      is_default bit not null default 0,
      last_mod_datetime datetime not null default getdate(),
      last_mod_user sysname not null default suser_sname()

      CONSTRAINT [SYSMAIL_PRINCIPALPROFILE_ProfilePrincipalMustBeUnique] PRIMARY KEY(profile_id,principal_sid),
   )
END
ELSE
BEGIN
   -- add principal_sid column
   IF NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name='principal_sid' and id =
        (SELECT OBJECT_ID(N'dbo.sysmail_principalprofile', 'U')))
   BEGIN
      ALTER TABLE dbo.sysmail_principalprofile ADD principal_sid varbinary(85) not null default 0xFFFF
   END
END
go

IF (OBJECT_ID(N'dbo.sysmail_account', 'U') IS NULL)
BEGIN
   PRINT ''
   PRINT 'Creating table sysmail_account...'
   
   CREATE TABLE dbo.sysmail_account
   (
      account_id int identity not null,
      name sysname not null,
      description nvarchar(256) null,
      email_address nvarchar(128) not null,
      display_name nvarchar(128) null,
      replyto_address nvarchar(128) null,      
      last_mod_datetime datetime not null default getdate(),
      last_mod_user sysname not null default suser_sname()

      CONSTRAINT [SYSMAIL_ACCOUNT_IDMustBeUnique] PRIMARY KEY(account_id),
      CONSTRAINT [SYSMAIL_ACCOUNT_NameMustBeUnique] UNIQUE (name)
   )
END
ELSE
BEGIN
  ALTER TABLE dbo.sysmail_account ALTER COLUMN description nvarchar(256) null
  ALTER TABLE dbo.sysmail_account ALTER COLUMN display_name nvarchar(128) null
  ALTER TABLE dbo.sysmail_account ALTER COLUMN replyto_address nvarchar(128) null
END
go

IF (OBJECT_ID(N'dbo.sysmail_profileaccount', 'U') IS NULL)
BEGIN
   PRINT ''
   PRINT 'Creating table sysmail_profileaccount...'

   CREATE TABLE dbo.sysmail_profileaccount
   (
      profile_id int not null,
      account_id int not null references sysmail_account(account_id) on delete cascade,
      sequence_number int null,
      last_mod_datetime datetime not null default getdate(),
      last_mod_user sysname not null default suser_sname()

      CONSTRAINT [SYSMAIL_ACCOUNT_ProfileAccountMustBeUnique] PRIMARY KEY(profile_id,account_id)
   )  
END
ELSE
BEGIN
  ALTER TABLE dbo.sysmail_profileaccount ALTER COLUMN sequence_number int null
END
go

IF (OBJECT_ID(N'dbo.sysmail_servertype', 'U') IS NULL)
BEGIN
   PRINT ''
   PRINT 'Creating table sysmail_servertype...'

   CREATE TABLE dbo.sysmail_servertype
   (
      servertype sysname not null,
      is_incoming bit not null default 0,
      is_outgoing bit not null default 1,
      last_mod_datetime datetime not null default getdate(),
      last_mod_user sysname not null default suser_sname()

      CONSTRAINT [SYSMAIL_SERVERTYPE_TypeMustBeUnique] PRIMARY KEY(servertype),
   )
END
go

IF (OBJECT_ID(N'dbo.sysmail_server', 'U') IS NULL)
BEGIN
   PRINT ''
   PRINT 'Creating table sysmail_server...'
   
   CREATE TABLE dbo.sysmail_server
   (
      account_id int not null references sysmail_account(account_id) on delete cascade,
      servertype sysname not null references sysmail_servertype(servertype),
      servername sysname not null,
      port int not null default 25,
      username nvarchar(128) null,
      credential_id int null,
      use_default_credentials bit not null default 0,
      enable_ssl bit not null default 0,
      flags int not null default 0,
      last_mod_datetime datetime not null default getdate(),
      last_mod_user sysname not null default suser_sname()

      CONSTRAINT [SYSMAIL_ACCOUNT_AccountServerTypeMustBeUnique] PRIMARY KEY(account_id,servertype)
   )
END
ELSE -- check if we need to add missing columns
BEGIN
   IF NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name='use_default_credentials' and id =
        (SELECT OBJECT_ID(N'dbo.sysmail_server', 'U')))
   BEGIN
      ALTER TABLE dbo.sysmail_server ADD use_default_credentials bit not null default 0
   END

   IF NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name='enable_ssl' and id =
        (SELECT OBJECT_ID(N'dbo.sysmail_server', 'U')))
   BEGIN
      ALTER TABLE dbo.sysmail_server ADD enable_ssl bit not null default 0
   END

   IF NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name='flags' and id =
        (SELECT OBJECT_ID(N'dbo.sysmail_server', 'U')))
   BEGIN
      ALTER TABLE dbo.sysmail_server ADD flags int not null default 0
   END
END
go

IF (OBJECT_ID(N'dbo.sysmail_configuration', 'U') IS NULL)
BEGIN
   PRINT ''
   PRINT 'Creating table sysmail_configuration...'

   CREATE TABLE dbo.sysmail_configuration
   (
      paramname nvarchar(256) not null,
      paramvalue nvarchar(256) null,
      description nvarchar(256) null,
      last_mod_datetime datetime not null default getdate(),
      last_mod_user sysname not null default suser_sname()

      CONSTRAINT [SYSMAIL_CONFIGURATION_ParamnameMustBeUnique] PRIMARY KEY(paramname)
   )
END
ELSE
BEGIN
  ALTER TABLE dbo.sysmail_configuration ALTER COLUMN paramvalue nvarchar(256) null
  ALTER TABLE dbo.sysmail_configuration ALTER COLUMN description nvarchar(256) null
END
go

-- populate default configuration settings
DECLARE @description NVARCHAR(256)

SELECT @description = FORMATMESSAGE(14642)
IF NOT EXISTS(SELECT * FROM dbo.sysmail_configuration WHERE paramname = N'DefaultAttachmentEncoding')
  INSERT INTO dbo.sysmail_configuration (paramname,paramvalue,description) VALUES (N'DefaultAttachmentEncoding', N'MIME', @description)
ELSE
  UPDATE dbo.sysmail_configuration SET description = @description WHERE paramname = N'DefaultAttachmentEncoding'

-- maximum size of an Database Mail atachement 1MB
SELECT @description = FORMATMESSAGE(14644)
IF NOT EXISTS(SELECT * FROM dbo.sysmail_configuration WHERE paramname = N'MaxFileSize')
  INSERT INTO dbo.sysmail_configuration (paramname,paramvalue,description) VALUES (N'MaxFileSize', N'1000000', @description)
ELSE
  UPDATE dbo.sysmail_configuration SET description = @description WHERE paramname = N'MaxFileSize'

SELECT @description = FORMATMESSAGE(14645)
IF NOT EXISTS(SELECT * FROM dbo.sysmail_configuration WHERE paramname = N'ProhibitedExtensions')
  INSERT INTO dbo.sysmail_configuration (paramname,paramvalue,description) VALUES (N'ProhibitedExtensions', N'exe,dll,vbs,js', @description)
ELSE
  UPDATE dbo.sysmail_configuration SET description = @description WHERE paramname = N'ProhibitedExtensions'

SELECT @description = FORMATMESSAGE(14646)
IF NOT EXISTS(SELECT * FROM dbo.sysmail_configuration WHERE paramname = N'AccountRetryAttempts')
  INSERT INTO dbo.sysmail_configuration (paramname,paramvalue,description) VALUES (N'AccountRetryAttempts', N'1', @description)
ELSE
  UPDATE dbo.sysmail_configuration SET description = @description WHERE paramname = N'AccountRetryAttempts'

SELECT @description = FORMATMESSAGE(14647)
IF NOT EXISTS(SELECT * FROM dbo.sysmail_configuration WHERE paramname = N'AccountRetryDelay')
  INSERT INTO dbo.sysmail_configuration (paramname,paramvalue,description) VALUES (N'AccountRetryDelay', N'60', @description)
ELSE
  UPDATE dbo.sysmail_configuration SET description = @description WHERE paramname = N'AccountRetryDelay'

SELECT @description = FORMATMESSAGE(14648)
IF NOT EXISTS(SELECT * FROM dbo.sysmail_configuration WHERE paramname = N'DatabaseMailExeMinimumLifeTime')
  INSERT INTO dbo.sysmail_configuration (paramname,paramvalue,description) VALUES (N'DatabaseMailExeMinimumLifeTime', N'600', @description)
ELSE
  UPDATE dbo.sysmail_configuration SET description = @description WHERE paramname = N'DatabaseMailExeMinimumLifeTime'

-- component logging level: normal - 1, extended - 2 (default), verbose - 3
SELECT @description = FORMATMESSAGE(14664)
IF NOT EXISTS(SELECT * FROM dbo.sysmail_configuration WHERE paramname = N'LoggingLevel')
  INSERT INTO dbo.sysmail_configuration (paramname,paramvalue,description) VALUES (N'LoggingLevel', N'2', @description)
ELSE
  UPDATE dbo.sysmail_configuration SET description = @description WHERE paramname = N'LoggingLevel'

go

----------------------------------------------------------------
-- Database Mail: mail host database specific tables
----------------------------------------------------------------

-----------------------------------------------------------
-- TABLE sysmail_mailitems      
-----------------------------------------------------------
-- sysmail_mailitems      : Contains one row for each mail sent using sp_send_dbmail.
--                          Contains mails that are waiting to be sent and the sent mail.
--                          sent_status determines its status
--
-- mailitem_id            : Id for the row. Auto-generated.
-- profile_id             : ID of profile to use to send the mail.
-- recipients             : People on the To list.
-- copy_recipients        : People on the Cc list.
-- blind_copy_recipients  : People on the Bcc list.
-- subject                : Subject of the email.
-- body                   : Body of the email.
-- body_format         : Body format. 0 (Text), 1(Html)                        
-- importance             : Importance of the email:
--                          0(Low), 1(Normal), 2(High).
-- sensitivity            : Sensitivity of the email:
--                          0(Normal), 1(Personal), 2(Private), 3(Confidential).
-- attachment_encoding    : Encoding to use for mail and attachments: 
--                          0(MIME), 1(UUEncode), 2(BINHEX), 3(S/MIME).
-- query                  : SQL query that was executed in this mail 
-- execute_query_database : The database to execute the query in  
-- attach_query_result_as_file : Option for attaching the query result 
--                               as a file instead of in the mail body
-- query_result_header    : Option for including query result column headers
-- query_result_width     : The query result overall width in characters
-- query_result_separator : The query result column separaror character
-- exclude_query_output   : Option for supressing query output being returned to
--                          the client that is sending the mail
-- append_query_error     : Option for appending query error messages to the mail item
-- send_request_date   : Date this mail item was created
-- send_request_user      : The user that created this mail item
-- sent_account_id        : The account_id that was used to send this mail item 
-- sent_status            : The current status of the mail item. 
--                        : 0(PendingSend), 1(SendSuccessful), 2(SendFailed), 3(AttemptingSendRetry)
-- sent_date              : Date the mail item was sent or failed to be sent
-- from_adress            : Optional override of the from header.
-- reply_to               : Optional setting of the reply-to header.
-----------------------------------------------------------
IF(OBJECT_ID('dbo.sysmail_mailitems', 'U') IS NULL)
BEGIN
  PRINT 'Creating TABLE sysmail_mailitems'

    CREATE TABLE sysmail_mailitems
    (
       mailitem_id                 INT                     IDENTITY(1,1) NOT NULL,
       profile_id                  INT                     NOT NULL,        
       recipients                  VARCHAR(MAX)    NULL, 
       copy_recipients             VARCHAR(MAX)    NULL,
       blind_copy_recipients       VARCHAR(MAX)    NULL,
       subject                     NVARCHAR(255)      NULL,
       from_address                VARCHAR(MAX)    NULL,
       reply_to                    VARCHAR(MAX)    NULL,
       body                        NVARCHAR(MAX)      NULL, 
       body_format                 VARCHAR(20)             NULL, 
       importance                  VARCHAR(6)              NULL,
       sensitivity                 VARCHAR(12)             NULL,
       file_attachments            NVARCHAR(MAX)      NULL,  
       attachment_encoding         VARCHAR(20)             NULL,
       query                       NVARCHAR(MAX)      NULL,
       execute_query_database      sysname                 NULL,  
       attach_query_result_as_file BIT                     NULL,
       query_result_header         BIT        NULL,
       query_result_width          INT                     NULL,  
       query_result_separator      CHAR(1)         NULL,
       exclude_query_output        BIT       NULL,
       append_query_error          BIT       NULL,
       send_request_date           DATETIME     NOT NULL DEFAULT GETDATE(),
       send_request_user           sysname              NOT NULL DEFAULT SUSER_SNAME(),
       sent_account_id             INT       NULL,
       sent_status                 TINYINT         NULL     DEFAULT 0,
       sent_date                   DATETIME     NULL,
       last_mod_date               DATETIME     NOT NULL DEFAULT GETDATE(),
       last_mod_user               sysname              NOT NULL DEFAULT SUSER_SNAME(),

       CONSTRAINT [sysmail_mailitems_id_MustBeUnique] PRIMARY KEY(mailitem_id),
       CONSTRAINT [sysmail_OutMailMustHaveAtleastOneRecipient]
                            CHECK (NOT (recipients IS NULL AND copy_recipients IS NULL AND blind_copy_recipients IS NULL)),
       CONSTRAINT [sysmail_OutMailRecipientCannotBeEmpty]           
                            CHECK (DATALENGTH(ISNULL(recipients, ''))      + 
            DATALENGTH(ISNULL(copy_recipients, '')) +  
            DATALENGTH(ISNULL(blind_copy_recipients, '')) <> 0),
       CONSTRAINT [sysmail_OutMailAttachmentEncodingMustBeValid]
             CHECK (attachment_encoding IN ('MIME', 'S/MIME', 'BINHEX', 'UUENCODE')),       
       CONSTRAINT [sysmail_OutMailImportanceMustBeValid]
             CHECK (importance IN ('LOW', 'NORMAL', 'HIGH')),
       CONSTRAINT [sysmail_OutMailSensitivityMustBeValid]           
             CHECK (sensitivity IN('NORMAL', 'PERSONAL', 'PRIVATE', 'CONFIDENTIAL'))
    )
END
ELSE
BEGIN
    -- handle schema upgrade for sysmail_mailitems table
    IF NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name='profile_id' and id =
        (SELECT OBJECT_ID(N'dbo.sysmail_mailitems', 'U')))
    BEGIN
       ALTER TABLE dbo.sysmail_mailitems ADD profile_id INT NOT NULL DEFAULT -1
    END

    IF NOT EXISTS (SELECT * FROM msdb.dbo.syscolumns WHERE name='from_address' and id =
        (SELECT OBJECT_ID(N'dbo.sysmail_mailitems', 'U')))
    BEGIN
       ALTER TABLE dbo.sysmail_mailitems ADD from_address VARCHAR(MAX) NULL
       ALTER TABLE dbo.sysmail_mailitems ADD reply_to VARCHAR(MAX) NULL
    END
END
GO

/**************************************************************/
/* sysmail_allitems                                               */
/**************************************************************/

PRINT ''
PRINT 'Creating view sysmail_allitems...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysmail_allitems')
              AND (type = 'V')))
  DROP VIEW sysmail_allitems
go

CREATE VIEW sysmail_allitems
AS
SELECT mailitem_id,
       profile_id,
       recipients,
       copy_recipients,
       blind_copy_recipients,
       subject,
       body,
       body_format,
       importance,
       sensitivity,
       file_attachments,
       attachment_encoding,
       query,
       execute_query_database,
       attach_query_result_as_file,
       query_result_header,
       query_result_width,
       query_result_separator,
       exclude_query_output,
       append_query_error,
       send_request_date,
       send_request_user,
       sent_account_id,
       CASE sent_status 
          WHEN 0 THEN 'unsent' 
          WHEN 1 THEN 'sent' 
          WHEN 3 THEN 'retrying' 
          ELSE 'failed' 
       END as sent_status,
       sent_date,
       last_mod_date,
       last_mod_user
FROM msdb.dbo.sysmail_mailitems
WHERE (send_request_user = SUSER_SNAME()) OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)

GO

/**************************************************************/
/* sysmail_sentitems                                               */
/**************************************************************/

PRINT ''
PRINT 'Creating view sysmail_sentitems...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysmail_sentitems')
              AND (type = 'V')))
  DROP VIEW sysmail_sentitems
go

CREATE VIEW sysmail_sentitems
AS
SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'sent'

go

/**************************************************************/
/* sysmail_unsentitems                                               */
/**************************************************************/

PRINT ''
PRINT 'Creating view sysmail_unsentitems...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysmail_unsentitems')
              AND (type = 'V')))
  DROP VIEW sysmail_unsentitems
go

CREATE VIEW sysmail_unsentitems
AS
SELECT * FROM msdb.dbo.sysmail_allitems WHERE (sent_status = 'unsent' OR sent_status = 'retrying')

go

/**************************************************************/
/* sysmail_faileditems                                               */
/**************************************************************/

PRINT ''
PRINT 'Creating view sysmail_faileditems...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysmail_faileditems')
              AND (type = 'V')))
  DROP VIEW sysmail_faileditems
go

CREATE VIEW sysmail_faileditems
AS
SELECT * FROM msdb.dbo.sysmail_allitems WHERE sent_status = 'failed'

go

-----------------------------------------------------------
-- procedure sysmail_delete_mailitems_sp
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sysmail_delete_mailitems_sp', 'P') IS NULL
    DROP PROCEDURE dbo.sysmail_delete_mailitems_sp
GO
-----
PRINT 'Creating sysmail_delete_mailitems_sp'
-----
GO
CREATE PROCEDURE sysmail_delete_mailitems_sp
   @sent_before DATETIME   = NULL, -- sent before
   @sent_status varchar(8)   = NULL -- sent status
AS
BEGIN

   SET @sent_status       = LTRIM(RTRIM(@sent_status))
   IF @sent_status           = '' SET @sent_status = NULL

   IF ( (@sent_status IS NOT NULL) AND
         (LOWER(@sent_status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ( 'unsent', 'sent', 'failed', 'retrying') ) )
   BEGIN
      RAISERROR(14266, -1, -1, '@sent_status', 'unsent, sent, failed, retrying')
      RETURN(1) -- Failure
   END

   IF ( @sent_before IS NULL AND @sent_status IS NULL )
   BEGIN
      RAISERROR(14608, -1, -1, '@sent_before', '@sent_status')  
      RETURN(1) -- Failure
   END

   DELETE FROM msdb.dbo.sysmail_allitems 
   WHERE 
        ((@sent_before IS NULL) OR ( send_request_date < @sent_before))
   AND ((@sent_status IS NULL) OR (sent_status = @sent_status))

   DECLARE @localmessage nvarchar(255)
    SET @localmessage = FORMATMESSAGE(14665, SUSER_SNAME(), @@ROWCOUNT)
    exec msdb.dbo.sysmail_logmailevent_sp @event_type=1, @description=@localmessage

END
GO

-----------------------------------------------------------
-- TABLE sysmail_attachments
-----------------------------------------------------------
-- sysmail_attachments  : Contains mail item attachments
--
-- attachment_id        : Id for the row. Auto-generated
-- mailitem_id          : Optional key to the mail items that this entry is a about
-- filename             : The filename of the attachment
-- filesize             : Size of the file
-- encoded_attachment   : The file data encoded in base64
----------------------------------------------------------------
IF (OBJECT_ID('dbo.sysmail_attachments', 'U') IS NULL)
BEGIN
  PRINT 'Creating TABLE sysmail_attachments'

    CREATE TABLE sysmail_attachments
    (
        attachment_id       INT   IDENTITY(1, 1) NOT NULL,
        mailitem_id      INT  NOT NULL CONSTRAINT 
         FK_sysmail_mailitems_mailitem_id 
                    FOREIGN KEY (mailitem_id) REFERENCES sysmail_mailitems(mailitem_id)  ON DELETE CASCADE,
        filename            NVARCHAR(260)       NOT NULL,
        filesize            INT                 NOT NULL,
        attachment          VARBINARY(MAX)      NULL,
        last_mod_date       DATETIME            NOT NULL DEFAULT GETDATE(),
        last_mod_user       sysname             NOT NULL DEFAULT SUSER_SNAME()
    )
END
ELSE
BEGIN
   BEGIN TRAN

   -- remove any old constraint
   declare @fkName sysname
   DECLARE @sql NVARCHAR(4000)
   SELECT @fkName = cstr.name
      FROM
         sys.tables AS tbl
      INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
      LEFT OUTER JOIN sys.indexes AS ki ON ki.index_id = cstr.key_index_id and ki.object_id = cstr.referenced_object_id
      INNER JOIN sys.tables rtbl ON rtbl.object_id = cstr.referenced_object_id
      WHERE
      tbl.name=N'sysmail_attachments' 

   IF (@fkName IS NOT NULL)
   BEGIN
      select @sql = N'ALTER TABLE sysmail_attachments DROP CONSTRAINT ' + @fkName
      EXEC (@sql)
   END
   
   ALTER TABLE sysmail_attachments ADD CONSTRAINT FK_sysmail_mailitems_mailitem_id 
                    FOREIGN KEY (mailitem_id) REFERENCES sysmail_mailitems(mailitem_id) ON DELETE CASCADE
   COMMIT
END
GO


/**************************************************************/
/* sysmail_mailattachments                                */
/**************************************************************/

PRINT ''
PRINT 'Creating view sysmail_mailattachments...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysmail_mailattachments')
              AND (type = 'V')))
  DROP VIEW sysmail_mailattachments
go

CREATE VIEW sysmail_mailattachments
AS
SELECT attachment_id,
       sa.mailitem_id,
       filename,
       filesize,
       attachment,
       sa.last_mod_date,
       sa.last_mod_user
  FROM msdb.dbo.sysmail_attachments sa
  JOIN msdb.dbo.sysmail_mailitems sm ON sa.mailitem_id = sm.mailitem_id
  WHERE (sm.send_request_user = SUSER_SNAME()) OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
GO

-----------------------------------------------------------
-- TABLE sysmail_send_retries
-----------------------------------------------------------
-- sysmail_send_retries : Contains send mail retry history
--
-- conversation_handle  : The conversation handle that initiated the retry
-- mailitem_id          : Optional key to the mail items that this entry is a about
-- send_attempts        : The current number of send attempts
-- last_send_attempt_date : date of the last send attempt
----------------------------------------------------------------
IF (OBJECT_ID('dbo.sysmail_send_retries', 'U') IS NULL)
BEGIN
    PRINT 'Creating TABLE sysmail_send_retries'
    CREATE TABLE sysmail_send_retries
    (
        conversation_handle     uniqueidentifier PRIMARY KEY NOT NULL,
        mailitem_id             INT              NOT NULL CONSTRAINT FK_mailitems_mailitem_id 
                    FOREIGN KEY (mailitem_id) REFERENCES sysmail_mailitems(mailitem_id) ON DELETE CASCADE,
        send_attempts           INT              NOT NULL DEFAULT 1,
        last_send_attempt_date  DATETIME         NOT NULL DEFAULT GETDATE()
    )
END
ELSE
BEGIN
   BEGIN TRAN

   -- remove any old constraint
   declare @fkName sysname
   DECLARE @sql NVARCHAR(4000)
   SELECT @fkName = cstr.name
      FROM
         sys.tables AS tbl
      INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
      LEFT OUTER JOIN sys.indexes AS ki ON ki.index_id = cstr.key_index_id and ki.object_id = cstr.referenced_object_id
      INNER JOIN sys.tables rtbl ON rtbl.object_id = cstr.referenced_object_id
      WHERE
      tbl.name=N'sysmail_send_retries' 

   IF (@fkName IS NOT NULL)
   BEGIN
      SET @sql = N'ALTER TABLE sysmail_send_retries DROP CONSTRAINT ' + @fkName
      EXECUTE (@sql)
   END

   ALTER TABLE sysmail_send_retries ADD CONSTRAINT FK_mailitems_mailitem_id 
                    FOREIGN KEY (mailitem_id) REFERENCES sysmail_mailitems(mailitem_id) ON DELETE CASCADE

   COMMIT
END
GO

-----------------------------------------------------------
-- TABLE sysmail_log
-----------------------------------------------------------
-- sysmail_log      : Contains error and event logging 
--
-- log_id           : Id for the row. Auto-generated.
-- event_type       : The event type for this record
--                    0(Success), 1(information), 2(Warning), 3(error)

-- log_date         : Create date of this log entry
-- description      : The text description of this entry
-- process_id       : The DatabaseMail (exe) process id that added this entry 
-- mailitem_id      : Optional key to the mail items that this entry is a about
-- account_id       : Optional account_id hat this entry is a about
----------------------------------------------------------------
IF (OBJECT_ID('dbo.sysmail_log', 'U') IS NULL)
BEGIN
  PRINT 'Creating TABLE sysmail_log'

    CREATE TABLE sysmail_log
    (
        log_id         INT             IDENTITY(1, 1) NOT NULL,
        event_type      INT             NOT NULL,
        log_date        DATETIME        NOT NULL    DEFAULT GETDATE(),
        description     NVARCHAR(max)   NULL,
        process_id      INT             NULL,
        mailitem_id     INT             NULL,
        account_id      INT             NULL,
        last_mod_date   DATETIME        NOT NULL DEFAULT GETDATE(),
        last_mod_user   sysname     NOT NULL DEFAULT SUSER_SNAME(),

        CONSTRAINT [sysmail_log_id_MustBeUnique] PRIMARY KEY(log_id),
    )
END
ELSE
BEGIN
   -- remove any old constraint
   declare @fkName sysname
   DECLARE @sql NVARCHAR(4000)
   SELECT @fkName = cstr.name
      FROM
         sys.tables AS tbl
      INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
      LEFT OUTER JOIN sys.indexes AS ki ON ki.index_id = cstr.key_index_id and ki.object_id = cstr.referenced_object_id
      INNER JOIN sys.tables rtbl ON rtbl.object_id = cstr.referenced_object_id
      WHERE
      tbl.name=N'sysmail_log' 

   IF (@fkName IS NOT NULL)
   begin
      select @sql = N'ALTER TABLE sysmail_log DROP CONSTRAINT ' + @fkName
      EXEC (@sql)
   end
END
GO

/**************************************************************/
/* sysmail_event_log                                         */
/**************************************************************/
use msdb
go

PRINT ''
PRINT 'Creating view sysmail_event_log...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysmail_event_log')
              AND (type = 'V')))
  DROP VIEW sysmail_event_log
go

CREATE VIEW sysmail_event_log
AS
SELECT log_id,
       CASE event_type 
          WHEN 0 THEN 'success' 
          WHEN 1 THEN 'information' 
          WHEN 2 THEN 'warning' 
          ELSE 'error' 
       END as event_type,
       log_date,
       description,
       process_id,
       sl.mailitem_id,
       account_id,
       sl.last_mod_date,
       sl.last_mod_user
FROM [dbo].[sysmail_log]  sl
WHERE (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) OR 
      (EXISTS ( SELECT mailitem_id FROM [dbo].[sysmail_allitems] ai WHERE sl.mailitem_id = ai.mailitem_id ))

GO

-----------------------------------------------------------
-- procedure sysmail_delete_log_sp
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sysmail_delete_log_sp', 'P') IS NULL
    DROP PROCEDURE dbo.sysmail_delete_log_sp
GO
-----
PRINT 'Creating sysmail_delete_log_sp'
-----
GO
CREATE PROCEDURE sysmail_delete_log_sp
   @logged_before DATETIME   = NULL, 
   @event_type varchar(15)   = NULL
AS
BEGIN

   SET @event_type       = LTRIM(RTRIM(@event_type))
   IF @event_type        = '' SET @event_type = NULL
   DECLARE @event_type_numeric INT

   IF ( (@event_type IS NOT NULL) AND
         (LOWER(@event_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ( 'success', 'warning', 'error', 'information' ) ) )
   BEGIN
        RAISERROR(14266, -1, -1, '@event_type', 'success, warning, error, information')
      RETURN(1) -- Failure
   END   
   
   IF ( @event_type IS NOT NULL)
   BEGIN
      SET @event_type_numeric = ( SELECT CASE 
                           WHEN @event_type = 'success' THEN 0
                           WHEN @event_type = 'information' THEN 1
                           WHEN @event_type = 'warning' THEN 2
                           ELSE 3 END 
                        )
   END
   ELSE
      SET @event_type_numeric = NULL

   DELETE FROM msdb.dbo.sysmail_log 
   WHERE 
        ((@logged_before IS NULL) OR ( log_date < @logged_before))
   AND ((@event_type_numeric IS NULL) OR (@event_type_numeric = event_type))
END
GO

-----------------------------------------------------------
-- sysmail_query_transfer : Table used to transfer data between a helper xp's and the calling sp's.
--                   Rows are created and deleted in the context of each call
--
-- uid              : guid for the row. Generated by the user  
-- text_data        : Attachment data in binary form
----------------------------------------------------------------
IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysmail_query_transfer')
                  AND (type = 'U')))
BEGIN
PRINT 'Creating TABLE sysmail_query_transfer'
CREATE TABLE sysmail_query_transfer
(
    uid             uniqueidentifier    NOT NULL PRIMARY KEY,
    text_data       NVARCHAR(max)       NULL,
    create_date     DATETIME            NOT NULL DEFAULT GETDATE()
)
END
GO

-----------------------------------------------------------
-- sysmail_attachments_transfer : Table used to transfer data between a helper xp's
--                        and the calling sp's. Rows are created and deleted 
--                        in the context of each call
--
-- uid              : guid for the row. Generated by the user  
-- filename         : Attachment file name
-- filesize         : Attachment file size in bytes
-- attachment       : Attachment data in binary form
----------------------------------------------------------------
IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sysmail_attachments_transfer')
                  AND (type = 'U')))
BEGIN
PRINT 'Creating TABLE sysmail_attachments_transfer'
CREATE TABLE sysmail_attachments_transfer
(
    transfer_id       INT                 IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    uid         uniqueidentifier    NOT NULL,
    filename        NVARCHAR(260)       NOT NULL,
    filesize        INT                 NOT NULL,
    attachment      VARBINARY(MAX)      NULL,
    create_date     DATETIME            NOT NULL DEFAULT GETDATE()
)
END
GO

/*************************************************************************/
/*                                                                       */
/*  Database Mail Triggers                                               */
/*                                                                       */
/*************************************************************************/

------------------------------------------------------------
-- Database Mail: triggers on general configuration tables
------------------------------------------------------------
PRINT ''
PRINT 'Creating trigger trig_sysmail_profile...'

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

CREATE TRIGGER trig_sysmail_profile
ON msdb.dbo.sysmail_profile
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_profile'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_profile 
      SET last_mod_datetime = getdate(),last_mod_user = suser_sname() 
      FROM sysmail_profile p, inserted i
      WHERE p.profile_id = i.profile_id
   END
END
go

PRINT ''
PRINT 'Creating trigger trig_principalprofile...'

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

CREATE TRIGGER trig_principalprofile
ON msdb.dbo.sysmail_principalprofile
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_principalprofile'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_principalprofile 
      SET last_mod_datetime = getdate(),last_mod_user = suser_sname() 
      FROM sysmail_principalprofile p, inserted i
      WHERE p.profile_id = i.profile_id and p.principal_sid = i.principal_sid
   END
END
go

PRINT ''
PRINT 'Creating trigger trig_sysmail_account...'

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

CREATE TRIGGER trig_sysmail_account
ON msdb.dbo.sysmail_account
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_account'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_account 
      SET last_mod_datetime = getdate(),last_mod_user = suser_sname() 
      FROM sysmail_account a, inserted i
      WHERE a.account_id = i.account_id
   END
END
go

PRINT ''
PRINT 'Creating trigger trig_sysmail_profileaccount...'

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

CREATE TRIGGER trig_sysmail_profileaccount
ON msdb.dbo.sysmail_profileaccount
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_profileaccount'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_profileaccount 
      SET last_mod_datetime = getdate(),last_mod_user = suser_sname() 
      FROM sysmail_profileaccount p, inserted i
      WHERE p.profile_id = i.profile_id and p.account_id = i.account_id
   END
END
go

PRINT ''
PRINT 'Creating trigger trig_sysmail_profile_delete...'

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

CREATE TRIGGER trig_sysmail_profile_delete
ON msdb.dbo.sysmail_profile
FOR DELETE
AS
BEGIN
   DELETE FROM msdb.dbo.sysmail_profileaccount
   WHERE profile_id IN (SELECT profile_id FROM deleted)
END
go

PRINT ''
PRINT 'Creating trigger trig_sysmail_servertype...'

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

CREATE TRIGGER trig_sysmail_servertype
ON msdb.dbo.sysmail_servertype
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_servertype'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_servertype 
      SET last_mod_datetime = getdate(),last_mod_user = suser_sname() 
      FROM sysmail_servertype s, inserted i
      where s.servertype = i.servertype
   END
END
go

SET NOCOUNT ON
IF NOT EXISTS(SELECT * FROM dbo.sysmail_servertype WHERE servertype = N'SMTP')
BEGIN
    INSERT INTO dbo.sysmail_servertype (servertype) VALUES (N'SMTP')
END
SET NOCOUNT OFF
go

PRINT ''
PRINT 'Creating trigger trig_sysmail_server...'

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

CREATE TRIGGER trig_sysmail_server
ON msdb.dbo.sysmail_server
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_server'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_server 
      SET last_mod_datetime = getdate(),last_mod_user = suser_sname() 
      FROM sysmail_server s, inserted i
      WHERE s.account_id = i.account_id and s.servertype = i.servertype
   END
END
go

PRINT ''
PRINT 'Creating trigger trig_sysmail_configuration...'

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

CREATE TRIGGER trig_sysmail_configuration
ON msdb.dbo.sysmail_configuration
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_configuration'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_configuration 
      SET last_mod_datetime = getdate(),last_mod_user = suser_sname() 
      FROM sysmail_configuration c, inserted i
      WHERE c.paramname = i.paramname
   END
END
go

-------------------------------------------------------------------------
-- Database Mail: triggers on general mail host database specific tables
-------------------------------------------------------------------------
IF (OBJECT_ID('dbo.trig_sysmail_mailitems', 'TR') IS NOT NULL)
    DROP TRIGGER dbo.trig_sysmail_mailitems
GO

CREATE TRIGGER trig_sysmail_mailitems
ON msdb.dbo.sysmail_mailitems
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_mailitems'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_mailitems 
      SET last_mod_date = GETDATE(), last_mod_user = SUSER_SNAME() 
      FROM sysmail_mailitems m, inserted i
      WHERE m.mailitem_id = i.mailitem_id
   END
END
GO

IF (OBJECT_ID('dbo.trig_sysmail_attachments', 'TR') IS NOT NULL)
    DROP TRIGGER dbo.trig_sysmail_attachments
GO

CREATE TRIGGER trig_sysmail_attachments
ON msdb.dbo.sysmail_attachments
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_attachments'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_attachments 
      SET last_mod_date = GETDATE(), last_mod_user = SUSER_SNAME() 
      FROM sysmail_attachments a, inserted i
      WHERE a.attachment_id = i.attachment_id
   END
END
GO

IF (OBJECT_ID('dbo.trig_sysmail_log', 'TR') IS NOT NULL)
    DROP TRIGGER dbo.trig_sysmail_log
GO

CREATE TRIGGER trig_sysmail_log
ON msdb.dbo.sysmail_log
FOR UPDATE
AS
BEGIN
   SET NOCOUNT ON  

   IF (TRIGGER_NESTLEVEL( OBJECT_ID('dbo.trig_sysmail_log'), 'AFTER' , 'DML' ) <= 1) 
   BEGIN  
      UPDATE msdb.dbo.sysmail_log 
      SET last_mod_date = GETDATE(), last_mod_user = SUSER_SNAME() 
      FROM sysmail_log l, inserted i
      WHERE l.log_id = i.log_id
   END
END
GO

/*********************************************************************************/
/*                                                                               */
/*  Database Mail Utility Functions                                              */
/*                                                                               */
/*********************************************************************************/
-----------------------------------------------------------
-- ConvertToInt : Converts a string to integer. Returns null
--                if the input string is not a valid int.
--
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.ConvertToInt', 'FN') IS NULL
    DROP FUNCTION dbo.ConvertToInt
GO

CREATE FUNCTION dbo.ConvertToInt(@string nvarchar(255), @maxValue int, @defValue int) RETURNS int
AS
BEGIN
    DECLARE @value bigint   
    SET @value = @defValue 
    SET @string = LTRIM(RTRIM(@string))

    -- Check if there is any character other than 0-9 in the string.
    IF ((@string IS NOT NULL AND @string <> N'') AND (@string NOT LIKE '%[^0-9]%'))
    BEGIN
        --INT's have a max of 10 digits
        IF(LEN(@string) <= 10)
        BEGIN
        -- Try converting to bigint. Return default if the value is bigger than @maxValue
        SET @value = CONVERT(bigint, @string)
        IF(@value > CONVERT(bigint, @maxValue))
            SET @value = @defValue
        END
    END

    RETURN CONVERT(int, @value)
END
GO

/*********************************************************************************/
/*                                                                               */
/*  Database Mail Stored Procedures                                              */
/*                                                                               */
/*********************************************************************************/

-------------------------------------------------------
-- Database Mail: configuration stored procedures
-------------------------------------------------------
PRINT ''
PRINT 'Creating procedure sysmail_verify_accountparams_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_verify_accountparams_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_verify_accountparams_sp
go

CREATE PROCEDURE dbo.sysmail_verify_accountparams_sp
   @use_default_credentials bit,
   @mailserver_type sysname      OUTPUT,  -- @mailserver_type must be provided. Usually SMTP
   @username      nvarchar(128)  OUTPUT, -- returns trimmed value, NULL if empty
   @password      nvarchar(128)  OUTPUT  -- returns trimmed value,  NULL if empty
AS
   SET @username = LTRIM(RTRIM(@username))
   SET @password = LTRIM(RTRIM(@password))
   SET @mailserver_type = LTRIM(RTRIM(@mailserver_type))

    IF(@username = N'')         SET @username = NULL
    IF(@password = N'')         SET @password = NULL
    IF(@mailserver_type = N'')  SET @mailserver_type = NULL

   IF(@mailserver_type IS NULL)
   BEGIN
      RAISERROR(14614, -1, -1, @mailserver_type)   
      RETURN (1)  
   END

   -- default credentials should supercede any explicit credentials passed in
   IF((@use_default_credentials = 1) AND (@username IS NOT NULL))
   BEGIN
      RAISERROR(14666, -1, -1)   
      RETURN (1)
   END  

   --If a password is specified then @username must be a non empty string
   IF((@password IS NOT NULL) AND (@username IS NULL))
   BEGIN
      RAISERROR(14615, -1, -1)   
      RETURN (1)
   END  

   RETURN(0) -- SUCCESS
go

PRINT ''
PRINT 'Creating procedure sysmail_verify_principal_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_verify_principal_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_verify_principal_sp
go

CREATE PROCEDURE dbo.sysmail_verify_principal_sp
   @principal_id int,
   @principal_name sysname,
   @allow_both_nulls bit,
   @principal_sid varbinary(85) OUTPUT
AS
   IF @allow_both_nulls = 0
   BEGIN
      -- at least one parameter must be supplied
      IF (@principal_id IS NULL AND @principal_name IS NULL)
      BEGIN
         RAISERROR(14604, -1, -1, 'principal')  
         RETURN(1)
      END
   END

   DECLARE @principalid int

   IF (@principal_id IS NOT NULL AND @principal_name IS NOT NULL) -- both parameters supplied
   BEGIN
     SELECT @principalid=principal_id FROM msdb.sys.database_principals 
            WHERE type in ('U','S','G') AND principal_id = @principal_id AND name = @principal_name

      IF (@principalid IS NULL)
      BEGIN
         RAISERROR(14605, -1, -1, 'principal')  
         RETURN(2)
      END
   END
   ELSE IF (@principal_id IS NOT NULL) -- use id
   BEGIN
     SELECT @principalid=principal_id FROM msdb.sys.database_principals 
            WHERE type in ('U','S','G') AND principal_id = @principal_id

      IF (@principalid IS NULL)
      BEGIN
         RAISERROR(14606, -1, -1, 'principal')
         RETURN(3)
      END      
   END
   ELSE IF (@principal_name IS NOT NULL)  -- use name
   BEGIN
     SELECT @principalid=principal_id FROM msdb.sys.database_principals 
            WHERE type in ('U','S','G') AND name = @principal_name

      IF (@principalid IS NULL)
      BEGIN
         RAISERROR(14607, -1, -1, 'principal')
         RETURN(4)
      END      
   END

   -- populate return variable
   SELECT @principal_sid = dbo.get_principal_sid(@principalid)

   RETURN(0) -- SUCCESS
go

PRINT ''
PRINT 'Creating procedure sysmail_verify_profile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_verify_profile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_verify_profile_sp
go

CREATE PROCEDURE dbo.sysmail_verify_profile_sp
   @profile_id int,
   @profile_name sysname,
   @allow_both_nulls bit,
   @allow_id_name_mismatch bit,
   @profileid int OUTPUT
AS
   IF @allow_both_nulls = 0
   BEGIN
      -- at least one parameter must be supplied
      IF (@profile_id IS NULL AND @profile_name IS NULL)
      BEGIN
         RAISERROR(14604, -1, -1, 'profile') 
         RETURN(1)
      END
   END
   
   IF ((@allow_id_name_mismatch = 0) AND (@profile_id IS NOT NULL AND @profile_name IS NOT NULL)) -- use both parameters
   BEGIN
      SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE profile_id=@profile_id AND name=@profile_name
      IF (@profileid IS NULL) -- id and name do not match
      BEGIN
         RAISERROR(14605, -1, -1, 'profile')
         RETURN(2)
      END      
   END
   ELSE IF (@profile_id IS NOT NULL) -- use id
   BEGIN
      SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE profile_id=@profile_id
      IF (@profileid IS NULL) -- id is invalid
      BEGIN
         RAISERROR(14606, -1, -1, 'profile')
         RETURN(3)
      END      
   END
   ELSE IF (@profile_name IS NOT NULL) -- use name
   BEGIN
      SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE name=@profile_name
      IF (@profileid IS NULL) -- name is invalid
      BEGIN
         RAISERROR(14607, -1, -1, 'profile')
         RETURN(4)
      END      
   END
   RETURN(0) -- SUCCESS
go

PRINT ''
PRINT 'Creating procedure sysmail_verify_account_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_verify_account_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_verify_account_sp
go

CREATE PROCEDURE dbo.sysmail_verify_account_sp
   @account_id int,
   @account_name sysname,
   @allow_both_nulls bit,
   @allow_id_name_mismatch bit,
   @accountid int OUTPUT
AS
   IF @allow_both_nulls = 0
   BEGIN
      -- at least one parameter must be supplied
      IF (@account_id IS NULL AND @account_name IS NULL)
      BEGIN
         RAISERROR(14604, -1, -1, 'account') 
         RETURN(1)
      END
   END
   
   IF ((@allow_id_name_mismatch = 0) AND (@account_id IS NOT NULL AND @account_name IS NOT NULL)) -- use both parameters
   BEGIN
      SELECT @accountid = account_id FROM msdb.dbo.sysmail_account WHERE account_id=@account_id AND name=@account_name
      IF (@accountid IS NULL) -- id and name do not match
      BEGIN
         RAISERROR(14605, -1, -1, 'account')
         RETURN(2)
      END      
   END
   ELSE IF (@account_id IS NOT NULL) -- use id
   BEGIN
      SELECT @accountid = account_id FROM msdb.dbo.sysmail_account WHERE account_id=@account_id
      IF (@accountid IS NULL) -- id is invalid
      BEGIN
         RAISERROR(14606, -1, -1, 'account')
         RETURN(3)
      END      
   END
   ELSE IF (@account_name IS NOT NULL) -- use name
   BEGIN
      SELECT @accountid = account_id FROM msdb.dbo.sysmail_account WHERE name=@account_name
      IF (@accountid IS NULL) -- name is invalid
      BEGIN
         RAISERROR(14607, -1, -1, 'account')
         RETURN(4)
      END      
   END
   RETURN(0) -- SUCCESS
go

PRINT ''
PRINT 'Creating procedure sysmail_add_profile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_add_profile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_add_profile_sp
go

CREATE PROCEDURE dbo.sysmail_add_profile_sp
   @profile_name sysname,
   @description nvarchar(256) = NULL,
   @profile_id int = NULL OUTPUT 
AS
   SET NOCOUNT ON

   -- insert new profile record, rely on primary key constraint to error out
   INSERT INTO msdb.dbo.sysmail_profile (name,description) VALUES (@profile_name, @description)
   
   -- fetch back profile_id
   SELECT @profile_id = profile_id FROM msdb.dbo.sysmail_profile WHERE name = @profile_name

   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_update_profile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_update_profile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_update_profile_sp
go

CREATE PROCEDURE dbo.sysmail_update_profile_sp
   @profile_id int = NULL, -- must provide either id or name
   @profile_name sysname = NULL,
   @description nvarchar(256) = NULL
AS
   SET NOCOUNT ON
  
   DECLARE @rc int
   DECLARE @profileid int
   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 0, 1, @profileid OUTPUT
   IF @rc <> 0
      RETURN(1)
   
   IF (@profile_name IS NOT NULL AND @description IS NOT NULL)
      UPDATE msdb.dbo.sysmail_profile 
      SET name=@profile_name, description = @description
      WHERE profile_id = @profileid
      
   ELSE IF (@profile_name IS NOT NULL)
      UPDATE msdb.dbo.sysmail_profile 
      SET name=@profile_name
      WHERE profile_id = @profileid

   ELSE IF (@description IS NOT NULL)
      UPDATE msdb.dbo.sysmail_profile 
      SET description = @description
      WHERE profile_id = @profileid
      
   ELSE
   BEGIN
      RAISERROR(14610, -1, -1)   
      RETURN(1)
   END

   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_delete_profile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_delete_profile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_delete_profile_sp
go

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo].[sysmail_delete_profile_sp]
   @profile_id int = NULL, -- must provide either id or name
   @profile_name sysname = NULL,
   @force_delete BIT = 1
AS
   SET NOCOUNT ON
  
   DECLARE @rc int
   DECLARE @profileid int
   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 0, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(1)

IF(EXISTS (select * from sysmail_unsentitems WHERE 
   sysmail_unsentitems.profile_id = @profileid) AND @force_delete <> 1)
BEGIN
    IF(@profile_name IS NULL)
    BEGIN
        select @profile_name = name from dbo.sysmail_profile WHERE profile_id = @profileid
    END
    RAISERROR(14668, -1, -1, @profile_name)
    RETURN (1)   
END

UPDATE [msdb].[dbo].[sysmail_mailitems]
SET [sent_status] = 2, [sent_date] = getdate()
WHERE profile_id = @profileid AND sent_status <> 1
     
   DELETE FROM msdb.dbo.sysmail_profile 
   WHERE profile_id = @profileid
   RETURN(0)
GO

PRINT ''
PRINT 'Creating procedure sysmail_help_profile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_help_profile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_help_profile_sp
go

CREATE PROCEDURE dbo.sysmail_help_profile_sp
   @profile_id int = NULL,
   @profile_name sysname = NULL
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @profileid int
   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 1, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(1)

   IF (@profileid IS NOT NULL)
      SELECT profile_id, name, description 
      FROM msdb.dbo.sysmail_profile 
      WHERE profile_id = @profileid
      
   ELSE -- don't filter the output
      SELECT profile_id, name, description      
      FROM msdb.dbo.sysmail_profile 

   RETURN(0)
go


PRINT ''
PRINT 'Creating procedure sysmail_create_user_credential_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_create_user_credential_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_create_user_credential_sp
go

CREATE PROCEDURE dbo.sysmail_create_user_credential_sp
   @username      nvarchar(128),
   @password      nvarchar(128),
   @credential_id int            OUTPUT
AS
   SET NOCOUNT ON
   DECLARE @rc int
   DECLARE @credential_name UNIQUEIDENTIFIER
   DECLARE @credential_name_as_str varchar(40)
   DECLARE @sql NVARCHAR(max)

   -- create a GUID as the name for the credential
   SET @credential_name = newid()
   SET @credential_name_as_str = convert(varchar(40), @credential_name)
   SET @sql = N'CREATE CREDENTIAL [' + @credential_name_as_str
            + N'] WITH IDENTITY = ' + QUOTENAME(@username, '''')
            + N', SECRET = ' + QUOTENAME(ISNULL(@password, N''), '''')

   EXEC @rc = sp_executesql @statement = @sql
   IF(@rc <> 0)
      RETURN @rc

   SELECT @credential_id = credential_id 
   FROM sys.credentials
   WHERE name = convert(sysname, @credential_name)
    IF(@credential_id IS NULL)
   BEGIN
      RAISERROR(14616, -1, -1, @credential_name_as_str)
      RETURN 1
   END

   RETURN(0)
go



PRINT ''
PRINT 'Creating procedure sysmail_alter_user_credential_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_alter_user_credential_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_alter_user_credential_sp
go

CREATE PROCEDURE dbo.sysmail_alter_user_credential_sp
   @credential_name sysname,
   @username      nvarchar(128),
   @password      nvarchar(128)
AS
   SET NOCOUNT ON
   DECLARE @rc int
   DECLARE @sql NVARCHAR(max)

   -- alter credential DDL
   SET @sql = N'ALTER CREDENTIAL ' + QUOTENAME(@credential_name)
         + N' WITH IDENTITY = ' + QUOTENAME(@username, '''')
         + N', SECRET = ' + QUOTENAME(ISNULL(@password, N''), '''')

   EXEC @rc = sp_executesql @statement = @sql
   IF(@rc <> 0)
      RETURN @rc

   RETURN(0)
go


PRINT ''
PRINT 'Creating procedure sysmail_drop_user_credential_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_drop_user_credential_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_drop_user_credential_sp
go

CREATE PROCEDURE dbo.sysmail_drop_user_credential_sp
   @credential_name sysname
AS
   SET NOCOUNT ON
   DECLARE @rc int
   DECLARE @sql NVARCHAR(max)

   -- Drop credential DDL
   SET @sql = N'DROP CREDENTIAL ' + QUOTENAME(@credential_name)

   EXEC @rc = sp_executesql @statement = @sql
   IF(@rc <> 0)
      RETURN @rc

   RETURN(0)
go

         
PRINT ''
PRINT 'Creating procedure sysmail_add_account_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_add_account_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_add_account_sp
go

CREATE PROCEDURE dbo.sysmail_add_account_sp
   @account_name sysname,
   @email_address nvarchar(128),
   @display_name nvarchar(128) = NULL,
   @replyto_address nvarchar(128) = NULL,
   @description nvarchar(256) = NULL,  
   @mailserver_name sysname = NULL, -- the following fields are part of server definition
   @mailserver_type sysname = N'SMTP',
   @port int = 25,
   @username nvarchar(128) = NULL,
   @password nvarchar(128) = NULL,
   @use_default_credentials bit = 0,
   @enable_ssl bit = 0,
   @account_id int = NULL OUTPUT
AS
   SET NOCOUNT ON
   DECLARE @rc int
   DECLARE @credential_id int

   EXEC @rc = msdb.dbo.sysmail_verify_accountparams_sp
            @use_default_credentials = @use_default_credentials,
            @mailserver_type = @mailserver_type OUTPUT, -- validates and returns trimmed value
            @username = @username OUTPUT, -- returns trimmed value, NULL if empty
            @password = @password OUTPUT  -- returns trimmed value, NULL if empty
   IF(@rc <> 0)
      RETURN (1)

   --transact this in case sysmail_create_user_credential_sp fails
   BEGIN TRANSACTION

   -- insert new account record, rely on primary key constraint to error out
   INSERT INTO msdb.dbo.sysmail_account (name,description,email_address,display_name,replyto_address) 
   VALUES (@account_name,@description,@email_address,@display_name,@replyto_address)
   IF (@@ERROR <> 0)
   BEGIN
      ROLLBACK TRANSACTION
      RETURN (2)
   END
   
   -- fetch back account_id
   SELECT @account_id = account_id FROM msdb.dbo.sysmail_account WHERE name = @account_name

   IF (@mailserver_name IS NULL) -- use local server as default
      SELECT @mailserver_name=@@SERVERNAME

   --create a credential in the credential store if a password needs to be stored
   IF(@username IS NOT NULL)
   BEGIN
      EXEC @rc = msdb.dbo.sysmail_create_user_credential_sp
                     @username,
                     @password,
                     @credential_id OUTPUT
      IF(@rc <> 0)
      BEGIN 
         ROLLBACK TRANSACTION
         RETURN (3)
      END
   END
   
   INSERT INTO msdb.dbo.sysmail_server (account_id,servertype,servername,port,username,credential_id,use_default_credentials,enable_ssl) 
   VALUES (@account_id,@mailserver_type,@mailserver_name,@port,@username,@credential_id,@use_default_credentials,@enable_ssl)
   IF (@@ERROR <> 0)
   BEGIN
      ROLLBACK TRANSACTION
      RETURN (4)
   END

   COMMIT TRANSACTION
   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_update_account_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_update_account_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_update_account_sp
go

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[sysmail_update_account_sp]    Script Date: 06/26/2006 10:45:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sysmail_update_account_sp]
   @account_id int = NULL, -- must provide either id or name
   @account_name sysname = NULL,
   @email_address nvarchar(128) = NULL,
   @display_name nvarchar(128) = NULL,
   @replyto_address nvarchar(128) = NULL,
   @description nvarchar(256) = NULL,
   @mailserver_name sysname = NULL,  
   @mailserver_type sysname = NULL,  
   @port int = NULL,
   @username sysname = NULL,
   @password sysname = NULL,
   @use_default_credentials bit = NULL,
   @enable_ssl bit = NULL
  -- WITH EXECUTE AS OWNER --Allows access to sys.credentials
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @accountid int
   DECLARE @credential_id int
   DECLARE @credential_name sysname

   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 0, 1, @accountid OUTPUT
   IF @rc <> 0
      RETURN(1)


   IF(@email_address IS NULL)
   BEGIN
   SELECT @email_address = email_address FROM msdb.dbo.sysmail_account WHERE account_id=@accountid
   END


   IF(@display_name IS NULL)
   BEGIN
   SELECT @display_name = display_name FROM msdb.dbo.sysmail_account WHERE account_id=@accountid
   END

   IF(@replyto_address IS NULL)
   BEGIN
   SELECT @replyto_address = replyto_address FROM msdb.dbo.sysmail_account WHERE account_id=@accountid
   END

   IF(@description IS NULL)
   BEGIN
   SELECT @description = description FROM msdb.dbo.sysmail_account WHERE account_id=@accountid
   END


   IF(@port IS NULL)
   BEGIN
   SELECT @port = port FROM msdb.dbo.sysmail_server WHERE account_id=@accountid
   END

   IF(@use_default_credentials IS NULL)
   BEGIN
   SELECT @use_default_credentials = use_default_credentials FROM msdb.dbo.sysmail_server WHERE account_id=@accountid
   END

   IF(@enable_ssl IS NULL)
   BEGIN
   SELECT @enable_ssl = enable_ssl FROM msdb.dbo.sysmail_server WHERE account_id=@accountid
   END

   IF(@mailserver_type IS NULL)
   BEGIN
   SELECT @mailserver_type = servertype FROM msdb.dbo.sysmail_server WHERE account_id=@accountid
   END

 
   EXEC @rc = msdb.dbo.sysmail_verify_accountparams_sp 
            @use_default_credentials = @use_default_credentials,
            @mailserver_type = @mailserver_type OUTPUT, -- validates and returns trimmed value
            @username = @username OUTPUT, -- returns trimmed value
            @password = @password OUTPUT  -- returns empty string if @username is given and @password is null 
   IF(@rc <> 0)
      RETURN (1)

   --transact this in case credential updates fail
   BEGIN TRAN
   -- update account table
   IF (@account_name IS NOT NULL)
      IF (@email_address IS NOT NULL)
         UPDATE msdb.dbo.sysmail_account
         SET name=@account_name, description=@description, email_address=@email_address, display_name=@display_name, replyto_address=@replyto_address
         WHERE account_id=@accountid
      ELSE
         UPDATE msdb.dbo.sysmail_account
         SET name=@account_name, description=@description, display_name=@display_name, replyto_address=@replyto_address
         WHERE account_id=@accountid

   ELSE
      IF (@email_address IS NOT NULL)
         UPDATE msdb.dbo.sysmail_account
         SET description=@description, email_address=@email_address, display_name=@display_name, replyto_address=@replyto_address
         WHERE account_id=@accountid
      ELSE
         UPDATE msdb.dbo.sysmail_account
         SET description=@description, display_name=@display_name, replyto_address=@replyto_address
         WHERE account_id=@accountid
      
   -- see if a credential has been stored for this account
   SELECT @credential_name = name,
         @credential_id = c.credential_id
   FROM sys.credentials as c
     JOIN msdb.dbo.sysmail_server as ms
       ON c.credential_id = ms.credential_id
   WHERE account_id = @accountid 
     AND servertype = @mailserver_type

   --update the credential store
   IF(@credential_name IS NOT NULL)
   BEGIN
      --Remove the unneed credential
      IF(@username IS NULL)
      BEGIN
         SET @credential_id = NULL
         EXEC @rc = msdb.dbo.sysmail_drop_user_credential_sp 
                        @credential_name = @credential_name
      END
      -- Update the credential
      ELSE
      BEGIN
         EXEC @rc = msdb.dbo.sysmail_alter_user_credential_sp
                        @credential_name = @credential_name,
                        @username = @username,
                        @password = @password
      END

      IF(@rc <> 0)
      BEGIN 
         ROLLBACK TRAN
         RETURN (1)
      END
   END
   -- create a new credential if one doesn't exist
   ELSE IF(@credential_name IS NULL AND @username IS NOT NULL)
   BEGIN
      EXEC @rc = msdb.dbo.sysmail_create_user_credential_sp
                     @username = @username,
                     @password = @password,
                     @credential_id = @credential_id  OUTPUT
      IF(@rc <> 0)
      BEGIN 
         ROLLBACK TRAN
         RETURN (1)
      END
   END

   -- update server table
   IF (@mailserver_name IS NOT NULL)
      UPDATE msdb.dbo.sysmail_server 
      SET servername=@mailserver_name, port=@port, username=@username, credential_id = @credential_id, use_default_credentials = @use_default_credentials, enable_ssl = @enable_ssl
      WHERE account_id=@accountid AND servertype=@mailserver_type
   
   ELSE
      UPDATE msdb.dbo.sysmail_server 
      SET port=@port, username=@username, credential_id = @credential_id, use_default_credentials = @use_default_credentials, enable_ssl = @enable_ssl
      WHERE account_id=@accountid AND servertype=@mailserver_type
      
   COMMIT TRAN
   RETURN(0)
go


PRINT ''
PRINT 'Creating procedure sysmail_delete_account_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_delete_account_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_delete_account_sp
go

CREATE PROCEDURE dbo.sysmail_delete_account_sp
   @account_id int = NULL, -- must provide either id or name
   @account_name sysname = NULL
AS
   SET NOCOUNT ON
  
   DECLARE @rc int
   DECLARE @accountid int
   DECLARE @credential_name sysname

   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 0, 0, @accountid OUTPUT
   IF @rc <> 0
      RETURN(1)

   -- Get all the credentials has been stored for this account
   DECLARE cur CURSOR FOR
      SELECT c.name
      FROM sys.credentials as c
      JOIN msdb.dbo.sysmail_server as ms
         ON c.credential_id = ms.credential_id
      WHERE account_id = @accountid

   OPEN cur
   FETCH NEXT FROM cur INTO @credential_name
   WHILE @@FETCH_STATUS = 0
   BEGIN
      -- drop the credential
      EXEC msdb.dbo.sysmail_drop_user_credential_sp @credential_name = @credential_name

      FETCH NEXT FROM cur INTO @credential_name
   END

   CLOSE cur
   DEALLOCATE cur
     
   DELETE FROM msdb.dbo.sysmail_account
   WHERE account_id = @accountid
   
   RETURN(0)
go


PRINT ''
PRINT 'Creating procedure sysmail_help_account_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_help_account_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_help_account_sp
go

CREATE PROCEDURE dbo.sysmail_help_account_sp
   @account_id int = NULL,
   @account_name sysname = NULL
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @accountid int
   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 1, 0, @accountid OUTPUT
   IF @rc <> 0
      RETURN(1)

   IF (@accountid IS NOT NULL)
      SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl 
      FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s
      WHERE a.account_id = s.account_id AND a.account_id = @accountid
      
   ELSE
      SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl
      FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s
      WHERE a.account_id = s.account_id

   RETURN(0)
go

-- Access to the complete account info. Required by databasemail.exe
PRINT ''
PRINT 'Creating procedure sysmail_help_admin_account_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_help_admin_account_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_help_admin_account_sp
go

CREATE PROCEDURE dbo.sysmail_help_admin_account_sp
   @account_id int
AS
   SET NOCOUNT ON

   DECLARE @rc         int,
      @acc_id         int,
      @name           sysname,
      @description    nvarchar(256),
      @email_address  nvarchar(128),
      @display_name   nvarchar(128),
      @replyto_address nvarchar(128),  
      @servertype     sysname,
      @servername     sysname,
      @port           int,
      @username       nvarchar(128),
      @passwordsize   int,  
      @cryptpassword  varbinary(1024),
      @credential_id  int,
      @use_default_credentials bit,
      @enable_ssl     bit

    SET @passwordsize = 0

   EXEC @rc = msdb.dbo.sysmail_verify_account_sp @account_id, NULL, 1, 0, NULL
   IF @rc <> 0
      RETURN(1)

   SELECT 
      @acc_id         = a.account_id,
      @name           = a.name, 
      @description    = a.description, 
      @email_address  = a.email_address, 
      @display_name   = a.display_name, 
      @replyto_address= a.replyto_address,
      @servertype     = s.servertype, 
      @servername     = s.servername, 
      @port           = s.port, 
      @username       = s.username,
      @credential_id  = s.credential_id,
      @use_default_credentials = s.use_default_credentials,
      @enable_ssl     = s.enable_ssl
   FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s
   WHERE (a.account_id = s.account_id) AND 
      (a.account_id = @account_id)
    
    --get the encrypted password if required 
    IF(@username IS NOT NULL)
    BEGIN
        DECLARE @cred TABLE([size] INT, blob VARBINARY(1024));

        INSERT @cred
        EXEC @rc = master.dbo.sp_PostAgentInfo @credential_id
        IF @rc <> 0
        BEGIN
          RETURN(1)
        END
        
        SELECT @passwordsize = [size], @cryptpassword = [blob] 
        FROM @cred
    END
    
    --All done return result
    SELECT
        @acc_id         as 'account_id',
        @name           as 'name',  
        @description    as 'description',
        @email_address  as 'email_address',
        @display_name   as 'display_name',
        @replyto_address as 'replyto_address',
        @servertype     as 'servertype',
        @servername     as 'servername',
        @port           as 'port',
        @username       as 'username',
        @passwordsize   as 'password_size',
        @cryptpassword  as 'password_crypt',
        @use_default_credentials as 'use_default_credentials',
        @enable_ssl     as 'enable_ssl'

   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_add_profileaccount_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_add_profileaccount_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_add_profileaccount_sp
go

CREATE PROCEDURE dbo.sysmail_add_profileaccount_sp
   @profile_id int = NULL, -- must provide id or name
   @profile_name sysname = NULL,
   @account_id int = NULL, -- must provide id or name
   @account_name sysname = NULL,
   @sequence_number int -- account with the lowest sequence number is picked as default
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @profileid int
   DECLARE @accountid int

   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 0, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(1)

   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 0, 0, @accountid OUTPUT
   IF @rc <> 0
      RETURN(2)

   -- insert new account record, rely on primary key constraint to error out
   INSERT INTO msdb.dbo.sysmail_profileaccount (profile_id,account_id,sequence_number)
   VALUES (@profileid,@accountid,@sequence_number)
   
   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_update_profileaccount_sp...'
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sysmail_update_profileaccount_sp')
            AND (uid  = SUSER_SID('sa'))
              AND (type = 'P')))
  DROP PROCEDURE dbo.sysmail_update_profileaccount_sp
go

CREATE PROCEDURE dbo.sysmail_update_profileaccount_sp
   @profile_id int = NULL, -- must provide id or name
   @profile_name sysname = NULL,
   @account_id int = NULL, -- must provide id or name
   @account_name sysname = NULL,
   @sequence_number int -- account with the lowest sequence number is picked as default
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @profileid int
   DECLARE @accountid int

   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 0, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(1)

   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 0, 0, @accountid OUTPUT
   IF @rc <> 0
      RETURN(2)

   IF (@sequence_number IS NULL)
   BEGIN
      RAISERROR(14611, -1, -1)   
      RETURN(3)
   END
   
   UPDATE msdb.dbo.sysmail_profileaccount
   SET sequence_number=@sequence_number
   WHERE profile_id=@profileid AND account_id=@accountid
   
   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_delete_profileaccount_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_delete_profileaccount_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_delete_profileaccount_sp
go

CREATE PROCEDURE dbo.sysmail_delete_profileaccount_sp
   @profile_id int = NULL, -- must provide id or name
   @profile_name sysname = NULL,
   @account_id int = NULL, -- must provide id or name
   @account_name sysname = NULL
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @profileid int
   DECLARE @accountid int

   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 1, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(1)

   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 1, 0, @accountid OUTPUT
   IF @rc <> 0
      RETURN(2)

   IF (@profileid IS NOT NULL AND @accountid IS NOT NULL) -- both parameters supplied for deletion
      DELETE FROM msdb.dbo.sysmail_profileaccount
      WHERE profile_id=@profileid AND account_id=@accountid

   ELSE IF (@profileid IS NOT NULL) -- profile id is supplied
      DELETE FROM msdb.dbo.sysmail_profileaccount
      WHERE profile_id=@profileid

   ELSE IF (@accountid IS NOT NULL) -- account id is supplied
      DELETE FROM msdb.dbo.sysmail_profileaccount
      WHERE account_id=@accountid

   ELSE -- no parameters are supplied for deletion
   BEGIN
      RAISERROR(14608, -1, -1, 'profile', 'account')  
      RETURN(3)   
   END

   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_help_profileaccount_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_help_profileaccount_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_help_profileaccount_sp
go

CREATE PROCEDURE dbo.sysmail_help_profileaccount_sp
   @profile_id int = NULL, -- must provide id or name
   @profile_name sysname = NULL,
   @account_id int = NULL, -- must provide id or name
   @account_name sysname = NULL
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @profileid int
   DECLARE @accountid int

   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 1, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(1)

   exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 1, 0, @accountid OUTPUT
   IF @rc <> 0
      RETURN(2)

   IF (@profileid IS NOT NULL AND @accountid IS NOT NULL)
      SELECT p.profile_id,profile_name=p.name,a.account_id,account_name=a.name,c.sequence_number
      FROM msdb.dbo.sysmail_profile p, msdb.dbo.sysmail_account a, msdb.dbo.sysmail_profileaccount c
      WHERE p.profile_id=c.profile_id AND a.account_id=c.account_id AND c.profile_id=@profileid AND c.account_id=@accountid
   
   ELSE IF (@profileid IS NOT NULL)
      SELECT p.profile_id,profile_name=p.name,a.account_id,account_name=a.name,c.sequence_number
      FROM msdb.dbo.sysmail_profile p, msdb.dbo.sysmail_account a, msdb.dbo.sysmail_profileaccount c
      WHERE p.profile_id=c.profile_id AND a.account_id=c.account_id AND c.profile_id=@profileid

   ELSE IF (@accountid IS NOT NULL)
      SELECT p.profile_id,profile_name=p.name,a.account_id,account_name=a.name,c.sequence_number
      FROM msdb.dbo.sysmail_profile p, msdb.dbo.sysmail_account a, msdb.dbo.sysmail_profileaccount c
      WHERE p.profile_id=c.profile_id AND a.account_id=c.account_id AND c.account_id=@accountid

   ELSE
      SELECT p.profile_id,profile_name=p.name,a.account_id,account_name=a.name,c.sequence_number
      FROM msdb.dbo.sysmail_profile p, msdb.dbo.sysmail_account a, msdb.dbo.sysmail_profileaccount c
      WHERE p.profile_id=c.profile_id AND a.account_id=c.account_id
      
   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_configure_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_configure_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_configure_sp
go

CREATE PROCEDURE dbo.sysmail_configure_sp
   @parameter_name nvarchar(256),
   @parameter_value nvarchar(256),
   @description nvarchar(256) = NULL
AS
   SET NOCOUNT ON
   
   IF (@description IS NOT NULL)
      UPDATE msdb.dbo.sysmail_configuration
      SET paramvalue=@parameter_value, description=@description
      WHERE paramname=@parameter_name
   ELSE
      UPDATE msdb.dbo.sysmail_configuration
      SET paramvalue=@parameter_value
      WHERE paramname=@parameter_name

   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_help_configure_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_help_configure_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_help_configure_sp
go

CREATE PROCEDURE dbo.sysmail_help_configure_sp
   @parameter_name nvarchar(256) = NULL
AS
   SET NOCOUNT ON

    SELECT paramname, paramvalue, description
    FROM msdb.dbo.sysmail_configuration
    WHERE paramname = ISNULL(@parameter_name, paramname)

    RETURN(0)
go


PRINT ''
PRINT 'Creating procedure sysmail_help_configure_value_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_help_configure_value_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_help_configure_value_sp
go

CREATE PROCEDURE dbo.sysmail_help_configure_value_sp
   @parameter_name nvarchar(256),
   @parameter_value nvarchar(256) OUTPUT
AS
   SET NOCOUNT ON
   SET @parameter_value = NULL
    
   IF (@parameter_name IS NULL)
   BEGIN
      RAISERROR(14618, 16, 1, '@parameter_name')
      RETURN(1)   
   END

    SELECT @parameter_value = paramvalue
    FROM msdb.dbo.sysmail_configuration
    WHERE paramname = @parameter_name

   RETURN(0)
go


PRINT ''
PRINT 'Creating procedure sysmail_add_principalprofile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_add_principalprofile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_add_principalprofile_sp
go

CREATE PROCEDURE dbo.sysmail_add_principalprofile_sp
   @principal_id int = NULL, -- must provide id or name
   @principal_name sysname = NULL,
   @profile_id int = NULL, -- must provide id or name
   @profile_name sysname = NULL,
   @is_default bit
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @principal_sid varbinary(85)
   DECLARE @profileid int

   IF (@principal_id IS NOT NULL AND @principal_id = 0) OR (@principal_name IS NOT NULL AND @principal_name = N'public')
   BEGIN
      IF (@principal_id IS NOT NULL AND @principal_id <> 0) OR (@principal_name IS NOT NULL AND @principal_name <> N'public')
      BEGIN
         RAISERROR(14605, -1, -1, 'principal')  -- id and name do not match
      END
      SET @principal_sid = 0x00 -- public
   END
   ELSE
   BEGIN
      exec @rc = msdb.dbo.sysmail_verify_principal_sp @principal_id, @principal_name, 0, @principal_sid OUTPUT
      IF @rc <> 0
         RETURN(2)
   END
   
   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 0, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(3)

   -- insert new account record, rely on primary key constraint to error out
   INSERT INTO msdb.dbo.sysmail_principalprofile (principal_sid,profile_id,is_default)
   VALUES (@principal_sid,@profileid,@is_default)

   IF (@is_default IS NOT NULL AND @is_default = 1 )
   BEGIN
      -- a principal can only have one default profile so reset other, if there are any
      UPDATE msdb.dbo.sysmail_principalprofile
      SET is_default=0
      WHERE principal_sid = @principal_sid AND profile_id <> @profileid
   END
   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_update_principalprofile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_update_principalprofile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_update_principalprofile_sp
go

CREATE PROCEDURE dbo.sysmail_update_principalprofile_sp
   @principal_id int = NULL, -- must provide id or name
   @principal_name sysname = NULL,
   @profile_id int = NULL, -- must provide id or name
   @profile_name sysname = NULL,
   @is_default bit
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @principal_sid varbinary(85)
   DECLARE @profileid int

   IF (@principal_id IS NOT NULL AND @principal_id = 0) OR (@principal_name IS NOT NULL AND @principal_name = N'public')
   BEGIN
      IF (@principal_id IS NOT NULL AND @principal_id <> 0) OR (@principal_name IS NOT NULL AND @principal_name <> N'public')
      BEGIN
         RAISERROR(14605, -1, -1, 'principal')  -- id and name do not match
      END
      SET @principal_sid = 0x00 -- public
   END
   ELSE
   BEGIN
      exec @rc = msdb.dbo.sysmail_verify_principal_sp @principal_id, @principal_name, 0, @principal_sid OUTPUT
      IF @rc <> 0
         RETURN(1)
   END
   
   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 0, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(2)

   UPDATE msdb.dbo.sysmail_principalprofile
   SET is_default=@is_default
   WHERE principal_sid = @principal_sid AND profile_id = @profileid

   IF (@is_default IS NOT NULL AND @is_default = 1)
   BEGIN
      -- a principal can only have one default profile so reset others (if there are any)
      UPDATE msdb.dbo.sysmail_principalprofile
      SET is_default=0
      WHERE principal_sid = @principal_sid AND profile_id <> @profileid
   END

   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_delete_principalprofile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_delete_principalprofile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_delete_principalprofile_sp
go

CREATE PROCEDURE dbo.sysmail_delete_principalprofile_sp
   @principal_id int = NULL, -- must provide id or name
   @principal_name sysname = NULL,
   @profile_id int = NULL, -- must provide id or name
   @profile_name sysname = NULL
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @principal_sid varbinary(85)
   DECLARE @profileid int

   IF (@principal_id IS NOT NULL AND @principal_id = 0) OR (@principal_name IS NOT NULL AND @principal_name = N'public')
   BEGIN
      IF (@principal_id IS NOT NULL AND @principal_id <> 0) OR (@principal_name IS NOT NULL AND @principal_name <> N'public')
      BEGIN
         RAISERROR(14605, -1, -1, 'principal')  -- id and name do not match
      END
      SET @principal_sid = 0x00 -- public
   END
   ELSE
   BEGIN
      IF (@principal_id IS NOT NULL OR @principal_name IS NOT NULL) 
      BEGIN
         exec @rc = msdb.dbo.sysmail_verify_principal_sp @principal_id, @principal_name, 1, @principal_sid OUTPUT
         IF @rc <> 0
            RETURN(1)
      END
   END
   
   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 1, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(2)

   IF ((@principal_id IS NOT NULL OR @principal_name IS NOT NULL) AND @profileid IS NOT NULL)
   BEGIN
      DELETE FROM msdb.dbo.sysmail_principalprofile WHERE principal_sid=@principal_sid AND profile_id = @profileid
   END
   ELSE IF (@principal_id IS NOT NULL OR @principal_name IS NOT NULL)
   BEGIN
      DELETE FROM msdb.dbo.sysmail_principalprofile WHERE principal_sid=@principal_sid
   END
   ELSE IF (@profileid IS NOT NULL)
   BEGIN
      DELETE FROM msdb.dbo.sysmail_principalprofile WHERE profile_id = @profileid
   END
   ELSE -- no parameters are supplied for deletion
   BEGIN
      RAISERROR(14608, -1, -1, 'principal', 'profile') 
      RETURN(6)
   END
   
   RETURN(0)
go

PRINT ''
PRINT 'Creating procedure sysmail_help_principalprofile_sp...'
IF (NOT OBJECT_ID(N'dbo.sysmail_help_principalprofile_sp', 'P') IS NULL)
  DROP PROCEDURE dbo.sysmail_help_principalprofile_sp
go

CREATE PROCEDURE dbo.sysmail_help_principalprofile_sp
   @principal_id int = NULL, -- must provide id or name
   @principal_name sysname = NULL,
   @profile_id int = NULL, -- must provide id or name
   @profile_name sysname = NULL
AS
   SET NOCOUNT ON

   DECLARE @rc int
   DECLARE @principal_sid varbinary(85)
   DECLARE @profileid int

   exec @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id, @profile_name, 1, 0, @profileid OUTPUT
   IF @rc <> 0
      RETURN(1)

   IF (@principal_id IS NOT NULL AND @principal_id = 0) OR (@principal_name IS NOT NULL AND @principal_name = N'public')
   BEGIN
      IF (@principal_id IS NOT NULL AND @principal_id <> 0) OR (@principal_name IS NOT NULL AND @principal_name <> N'public')
      BEGIN
         RAISERROR(14605, -1, -1, 'principal')  -- id and name do not match
      END
      SET @principal_sid = 0x00 -- public

      IF (@profileid IS NOT NULL)
      BEGIN
         SELECT principal_id=0, 
                principal_name = N'public', 
                prof.profile_id, 
                profile_name=prof.name, 
                prin.is_default
         FROM msdb.dbo.sysmail_principalprofile prin, msdb.dbo.sysmail_profile prof 
         WHERE prin.profile_id=prof.profile_id AND 
               prin.principal_sid = @principal_sid AND 
               prof.profile_id=@profileid
      END
      ELSE
      BEGIN
         SELECT principal_id=0, 
                principal_name = N'public', 
                prof.profile_id, 
                profile_name=prof.name, 
                prin.is_default
         FROM msdb.dbo.sysmail_principalprofile prin, msdb.dbo.sysmail_profile prof 
         WHERE prin.profile_id=prof.profile_id AND 
               prin.principal_sid = @principal_sid
      END
   END
   ELSE -- non-public profiles
   BEGIN
      IF (@principal_id IS NOT NULL OR @principal_name IS NOT NULL)      
      BEGIN
            exec @rc = msdb.dbo.sysmail_verify_principal_sp @principal_id, @principal_name, 1, @principal_sid OUTPUT
            IF @rc <> 0
               RETURN(2)
      END

      IF ((@principal_id IS NOT NULL OR @principal_name IS NOT NULL) AND @profileid IS NOT NULL)
      BEGIN
            SELECT principal_id=dbprin.principal_id,
                   principal_name=dbprin.name,
                   prof.profile_id,
                   profile_name=prof.name,
                   prinprof.is_default
            FROM sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof
            WHERE dbprin.principal_id = dbo.get_principal_id(prinprof.principal_sid) AND
                  (prinprof.principal_sid = @principal_sid OR prinprof.principal_sid = 0x00) AND
                  prof.profile_id = prinprof.profile_id AND
                  prinprof.profile_id = @profileid
      END
      ELSE IF (@principal_id IS NOT NULL OR @principal_name IS NOT NULL)
      BEGIN
            SELECT principal_id=dbprin.principal_id,
                   principal_name=dbprin.name,
                   prof.profile_id,
                   profile_name=prof.name,
                   prinprof.is_default
            FROM sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof
            WHERE dbprin.principal_id = dbo.get_principal_id(prinprof.principal_sid) AND
                  (prinprof.principal_sid = @principal_sid OR prinprof.principal_sid = 0x00) AND
                  prof.profile_id = prinprof.profile_id
      END
      ELSE IF (@profileid IS NOT NULL)
      BEGIN
            SELECT principal_id=dbprin.principal_id,
                   principal_name=dbprin.name,
                   prof.profile_id,
                   profile_name=prof.name,
                   prinprof.is_default
            FROM sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof
            WHERE dbprin.principal_id = dbo.get_principal_id(prinprof.principal_sid) AND
                  prof.profile_id = prinprof.profile_id AND
                  prinprof.profile_id = @profileid
      END
      ELSE -- no parameters are supplied for filtering
      BEGIN
            SELECT principal_id=dbprin.principal_id,
                   principal_name=dbprin.name,
                   prof.profile_id,
                   profile_name=prof.name,
                   prinprof.is_default
            FROM sys.database_principals dbprin, msdb.dbo.sysmail_principalprofile prinprof, msdb.dbo.sysmail_profile prof
            WHERE dbprin.principal_id = dbo.get_principal_id(prinprof.principal_sid) AND
                  prof.profile_id = prinprof.profile_id
      END
   END
   RETURN(0)
go

-----------------------------------------------------------
-- Database Mail: mail host database stored procedures
-----------------------------------------------------------

-----------------------------------------------------------
-- procedure sysmail_logmailevent_sp
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sysmail_logmailevent_sp', 'P') IS NULL
    DROP PROCEDURE dbo.sysmail_logmailevent_sp
GO
-----
PRINT 'Creating sysmail_logmailevent_sp'
-----
GO
-- sysmail_logmailevent_sp : inserts an entry in the sysmail_log table
CREATE PROCEDURE sysmail_logmailevent_sp
    @event_type     INT,
    @description    NVARCHAR(max)   = NULL, 
    @process_id     INT             = NULL,
    @mailitem_id    INT             = NULL,
    @account_id     INT             = NULL
AS
    SET NOCOUNT ON

    --Try and get the optional logging level for the DatabaseMail process
    DECLARE @loggingLevel nvarchar(256)
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel', 
                                                  @parameter_value = @loggingLevel OUTPUT

    DECLARE @loggingLevelInt int   
    SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2) 

    IF (@event_type = 3) OR                           -- error
       (@event_type = 2 AND @loggingLevelInt >= 2) OR -- warning with extended logging
       (@event_type = 1 AND @loggingLevelInt >= 2) OR -- info with extended logging
       (@event_type = 0 AND @loggingLevelInt >= 3)    -- success with verbose logging
    BEGIN
       INSERT sysmail_log(event_type, description, process_id, mailitem_id, account_id) 
       VALUES(@event_type, @description, @process_id , @mailitem_id, @account_id)
    END
RETURN 0
GO

-----------------------------------------------------------
-- procedure sysmail_start_sp
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sysmail_start_sp', 'P') IS NULL
    DROP PROCEDURE dbo.sysmail_start_sp
GO
-----
PRINT 'Creating sysmail_start_sp'
-----
GO
-- sysmail_start_sp : allows databasemail to process mail from the queue 
CREATE PROCEDURE sysmail_start_sp
AS
    SET NOCOUNT ON
    DECLARE @rc INT 
   DECLARE @localmessage nvarchar(255)

    ALTER QUEUE ExternalMailQueue WITH STATUS = ON
    SELECT @rc = @@ERROR
    IF(@rc = 0)
    BEGIN
      ALTER QUEUE ExternalMailQueue WITH ACTIVATION (STATUS = ON);
       SET @localmessage = FORMATMESSAGE(14639, SUSER_SNAME())
       exec msdb.dbo.sysmail_logmailevent_sp @event_type=1, @description=@localmessage
    END
RETURN @rc
GO

-----------------------------------------------------------
-- procedure sysmail_stop_sp
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sysmail_stop_sp', 'P') IS NULL
    DROP PROCEDURE dbo.sysmail_stop_sp
GO
-----
PRINT 'Creating sysmail_stop_sp'
-----
GO
-- sysmail_stop_sp : stops the DatabaseMail process. Mail items remain in the queue until sqlmail started 
CREATE PROCEDURE sysmail_stop_sp
AS
    SET NOCOUNT ON
    DECLARE @rc INT
   DECLARE @localmessage nvarchar(255)
  
    ALTER QUEUE ExternalMailQueue WITH ACTIVATION (STATUS = OFF);
    SELECT @rc = @@ERROR
    IF(@rc = 0)
    BEGIN
       ALTER QUEUE ExternalMailQueue WITH STATUS = OFF;
       SELECT @rc = @@ERROR
       IF(@rc = 0)
       BEGIN
          SET @localmessage = FORMATMESSAGE(14640, SUSER_SNAME())
          exec msdb.dbo.sysmail_logmailevent_sp @event_type=1, @description=@localmessage
       END
    END
RETURN @rc
GO


-----------------------------------------------------------
-- procedure sysmail_help_status_sp
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sysmail_help_status_sp', 'P') IS NULL
    DROP PROCEDURE dbo.sysmail_help_status_sp
GO
-----
PRINT 'Creating sysmail_help_status_sp'
-----
GO
CREATE PROCEDURE sysmail_help_status_sp
  WITH EXECUTE AS 'dbo'
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1)
       SELECT 'STOPPED' AS Status
    ELSE
       SELECT 'STARTED' AS Status
END
GO


-----------------------------------------------------------
-- procedure sysmail_help_queue_sp
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sysmail_help_queue_sp', 'P') IS NULL
    DROP PROCEDURE dbo.sysmail_help_queue_sp
GO
-----
PRINT 'Creating sysmail_help_queue_sp'
-----
GO
CREATE PROCEDURE sysmail_help_queue_sp
   @queue_type nvarchar(6) = NULL -- Type of queue
AS
BEGIN

   SELECT @queue_type       = LTRIM(RTRIM(@queue_type))
   IF @queue_type           = '' SELECT @queue_type = NULL

   IF ( (@queue_type IS NOT NULL) AND
         (LOWER(@queue_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ( N'mail', N'status') ) )
   BEGIN
        RAISERROR(14266, -1, -1, '@queue_type', 'mail, status')
      RETURN(1) -- Failure
   END   

   DECLARE @depth      int
   DECLARE @temp TABLE (
                        queue_type nvarchar(6),
                        length INT NOT NULL, 
                        state nvarchar(64), 
                        last_empty_rowset_time DATETIME,
                        last_activated_time DATETIME
                       )
  
   IF ( (@queue_type IS NULL) OR (LOWER(@queue_type collate SQL_Latin1_General_CP1_CS_AS) = N'mail' ) )
   BEGIN
      SET @depth = (SELECT COUNT(*) FROM ExternalMailQueue WITH (NOWAIT NOLOCK READUNCOMMITTED))

      INSERT INTO @temp
         SELECT 
                N'mail',
                @depth, 
            qm.state as state,
            qm.last_empty_rowset_time as last_empty_rowset_time,
            qm.last_activated_time as last_activated_time
         FROM sys.dm_broker_queue_monitors qm
         JOIN sys.service_queues sq ON sq.object_id = qm.queue_id
         WHERE sq.name = 'ExternalMailQueue'
   END

   IF ( (@queue_type IS NULL) OR (LOWER(@queue_type collate SQL_Latin1_General_CP1_CS_AS) = N'status' ) )
   BEGIN
      SET @depth = (SELECT COUNT(*) FROM InternalMailQueue WITH (NOWAIT NOLOCK READUNCOMMITTED))

      INSERT INTO @temp
         SELECT 
                N'status',
                @depth, 
            qm.state as state,
            qm.last_empty_rowset_time as last_empty_rowset_time,
            qm.last_activated_time as last_activated_time
         FROM sys.dm_broker_queue_monitors qm
         JOIN sys.service_queues sq ON sq.object_id = qm.queue_id
         WHERE sq.name = 'InternalMailQueue'
   END

   SELECT * from @temp
END
GO

-----------------------------------------------------------
-- procedure sp_SendMailMessage
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_SendMailMessage', 'P') IS NULL
    DROP PROCEDURE dbo.sp_SendMailMessage
GO
-----
PRINT 'Creating sp_SendMailMessage'
-----
GO
-- sp_SendMailMessage : Sends a request on the mail items SSB queue
CREATE PROCEDURE sp_SendMailMessage
    @contract_name sysname, -- Name of contract
    @message_type sysname,  -- Type of message
    @request varchar(max) -- XML message to send
  WITH EXECUTE AS 'dbo'
AS

SET NOCOUNT ON

DECLARE @conversationHandle uniqueidentifier;
DECLARE @error int

-- Start a conversation with the remote service
BEGIN DIALOG  @conversationHandle
    FROM SERVICE    [InternalMailService]
    TO SERVICE      'ExternalMailService'
    ON CONTRACT     @contract_name
    WITH ENCRYPTION=OFF

-- Check error
SET @error = @@ERROR
IF @error <> 0
BEGIN
    RETURN @error
END

-- Send message
;SEND ON CONVERSATION @conversationHandle MESSAGE TYPE @message_type (@request)

-- Check error
SET @error = @@ERROR
IF @error <> 0
BEGIN
    RETURN @error
END

RETURN 0
GO

-----------------------------------------------------------
-- procedure sp_isprohibited
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_isprohibited', 'P') IS NULL
    DROP PROCEDURE dbo.sp_isprohibited
GO
-----
PRINT 'Creating sp_isprohibited'
-----
GO
-- sp_isprohibited : To test if the attachment is prohibited or not.
--
CREATE PROCEDURE sp_isprohibited
   @attachment nvarchar(max),
   @prohibitedextensions nvarchar(1000)
AS

DECLARE @extensionIndex int
DECLARE @extensionName nvarchar(255)

IF (@attachment IS NOT NULL AND LEN(@attachment) > 0) 
BEGIN
    SET @prohibitedextensions = UPPER(@prohibitedextensions)

   -- find @extensionName: the substring between the last '.' and the end of the string
   SET @extensionIndex = 0
   WHILE (1=1)
   BEGIN
      DECLARE @lastExtensionIndex int
      SET @lastExtensionIndex = CHARINDEX('.', @attachment, @extensionIndex+1)
      IF (@lastExtensionIndex = 0)
         BREAK
      SET @extensionIndex = @lastExtensionIndex
   END

   IF (@extensionIndex > 0)
   BEGIN
      SET @extensionName = SUBSTRING(@attachment, @extensionIndex + 1, (LEN(@attachment) - @extensionIndex)) 
      SET @extensionName = UPPER(@extensionName)

      -- compare @extensionName with each extension in the comma-separated @prohibitedextensions list
      DECLARE @currentExtensionStart int
      DECLARE @currentExtensionEnd int

      SET @currentExtensionStart = 0
      SET @currentExtensionEnd = 0
      WHILE (@currentExtensionEnd < LEN(@prohibitedextensions))
      BEGIN
         SET @currentExtensionEnd = CHARINDEX(',', @prohibitedextensions, @currentExtensionStart)

         IF (@currentExtensionEnd = 0) -- we have reached the last extension of the list, or the list was empty
            SET @currentExtensionEnd = LEN(@prohibitedextensions)+1

         DECLARE @prohibitedExtension nvarchar(1000)
         SET @prohibitedExtension = SUBSTRING(@prohibitedextensions, @currentExtensionStart, @currentExtensionEnd - @currentExtensionStart) 
         SET @prohibitedExtension = RTRIM(LTRIM(@prohibitedExtension))

         IF( @extensionName = @prohibitedExtension )
            RETURN 1

         SET @currentExtensionStart = @currentExtensionEnd + 1
      END
   END

   RETURN 0
END
GO

-----------------------------------------------------------
-- procedure sp_SendMailQueues
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_SendMailQueues', 'P') IS NULL
    DROP PROCEDURE dbo.sp_SendMailQueues
GO
-----
PRINT 'Creating sp_SendMailQueues'
-----
GO
-- sp_SendMailQueues : Writes a send mail request to the queue.
--
CREATE  PROCEDURE sp_SendMailQueues
    @message_data      varchar(max) -- The request in XML
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @contract_name nvarchar(128)
    DECLARE @message_type nvarchar(128)
    DECLARE @retValue int

    SET @message_type = '{//www.microsoft.com/databasemail/messages}SendMail'
    SET @contract_name = '//www.microsoft.com/databasemail/contracts/SendMail/v1.0'

    --Writes the message to the queue
    EXEC @retValue = sp_SendMailMessage @contract_name, @message_type, @message_data

    RETURN @retValue
END
GO


-----------------------------------------------------------
-- procedure sp_ProcessResponse
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_ProcessResponse', 'P') IS NULL
    DROP PROCEDURE dbo.sp_ProcessResponse
GO
-----
PRINT 'Creating sp_ProcessResponse'
-----
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF


GO
-- Processes responses from dbmail 
--
CREATE PROCEDURE [dbo].[sp_ProcessResponse]
    @conv_handle        uniqueidentifier,
    @message_type_name  NVARCHAR(256),
    @xml_message_body   NVARCHAR(max)
AS
BEGIN
    DECLARE 
        @idoc               INT,
        @mailitem_id        INT,
        @sent_status        INT,
        @rc                 INT,
        @index              INT,
        @processId          INT,
        @sent_date          DATETIME,
        @localmessage       NVARCHAR(max),
        @LogMessage         NVARCHAR(max),
        @retry_hconv        uniqueidentifier,
        @paramStr           NVARCHAR(256),
        @accRetryDelay      INT

    --------------------------
    --Always send the response 
    ;SEND ON CONVERSATION @conv_handle MESSAGE TYPE @message_type_name (@xml_message_body)


    --
    -- Need to handle the case where a sent retry is requested. 
    -- This is done by setting a conversation timer, The timer with go off in the external queue

    -- Get the handle to the xml document
    EXEC @rc = sp_xml_preparedocument 
                    @idoc OUTPUT, 
                    @xml_message_body, 
                    N'<ns xmlns:responses="http://schemas.microsoft.com/databasemail/responses" />'
    IF(@rc <> 0)
    BEGIN
        --Log the error. The response has already sent to the Internal queue. 
        -- This will update the mail with the latest staus
        SET @localmessage = FORMATMESSAGE(14655, CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage

        GOTO ErrorHandler;
    END

    -- Execute a SELECT statement that uses the OPENXML rowset provider to get the MailItemId and sent status.
    SELECT @mailitem_id     = MailItemId, 
           @sent_status     = SentStatus
    FROM OPENXML (@idoc, '/responses:SendMail', 1)
        WITH (MailItemId    INT      './MailItemId/@Id', 
              SentStatus    INT      './SentStatus/@Status')

    --Close the handle to the xml document
    EXEC sp_xml_removedocument @idoc

    IF(@mailitem_id IS NULL OR @sent_status IS NULL)
    BEGIN  
        --Log error and continue.
        SET @localmessage = FORMATMESSAGE(14652, CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage

        GOTO ErrorHandler;
    END      

    -- Update the status of the email item
    UPDATE msdb.dbo.sysmail_mailitems
    SET sent_status = @sent_status
    WHERE mailitem_id = @mailitem_id

    --
    -- A send retry has been requested. Set a conversation timer
    IF(@sent_status = 3)
    BEGIN
        -- Get the associated mail item data for the given @conversation_handle (if it exists)
       SELECT @retry_hconv = conversation_handle
       FROM sysmail_send_retries as sr
            RIGHT JOIN sysmail_mailitems as mi
            ON sr.mailitem_id = mi.mailitem_id
       WHERE mi.mailitem_id = @mailitem_id

        --Must be the first retry attempt. Create a sysmail_send_retries record to track retries
        IF(@retry_hconv IS NULL)
        BEGIN
            INSERT sysmail_send_retries(conversation_handle, mailitem_id) --last_send_attempt_date
            VALUES(@conv_handle, @mailitem_id)
        END
        ELSE
        BEGIN
            --Update existing retry record
            UPDATE sysmail_send_retries
            SET last_send_attempt_date = GETDATE(),
                send_attempts = send_attempts + 1
            WHERE mailitem_id = @mailitem_id

        END

        --Get the global retry delay time
        EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'AccountRetryDelay', 
                                                    @parameter_value = @paramStr OUTPUT
        --ConvertToInt will return the default if @paramStr is null
        SET @accRetryDelay = dbo.ConvertToInt(@paramStr, 0x7fffffff, 300) -- 5 min default


        --Now set the dialog timer. This triggers the send retry
        ;BEGIN CONVERSATION TIMER (@conv_handle) TIMEOUT = @accRetryDelay 

    END
    ELSE
    BEGIN
        --Only end theconversation if a retry isn't being attempted
        END CONVERSATION @conv_handle
    END


    -- All done OK
    goto ExitProc;

    -----------------
    -- Error Handler
    -----------------
ErrorHandler:

    ------------------
    -- Exit Procedure
    ------------------
ExitProc:
    RETURN (@rc);

END
GO


-----------------------------------------------------------
-- procedure sp_MailItemResultSets
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_MailItemResultSets', 'P') IS NULL
    DROP PROCEDURE dbo.sp_MailItemResultSets
GO
-----
PRINT 'Creating sp_MailItemResultSets'
-----
GO
-- sp_MailItemResultSets : 
--  Sends back multiple rowsets with the mail items data
CREATE PROCEDURE sp_MailItemResultSets
    @mailitem_id            INT,
    @profile_id             INT,
    @conversation_handle    uniqueidentifier,
   @service_contract_name  NVARCHAR(256),
   @message_type_name      NVARCHAR(256)
AS
BEGIN
    SET NOCOUNT ON
   --
   -- Send back multiple rowsets with the mail items data

   ----
   -- 1) MessageTypeName
   SELECT @message_type_name  as 'message_type_name',
      @service_contract_name as 'service_contract_name',
      @conversation_handle   as 'conversation_handle',
      @mailitem_id           as 'mailitem_id'

   -----
   -- 2) The mail item record from sysmail_mailitems.
   SELECT 
      mi.mailitem_id,
      mi.profile_id,
      (SELECT name FROM msdb.dbo.sysmail_profile p WHERE p.profile_id = mi.profile_id) as 'profile_name',
      mi.recipients,
      mi.copy_recipients,
      mi.blind_copy_recipients,
      mi.subject,
      mi.body, 
      mi.body_format, 
      mi.importance,
      mi.sensitivity,
      ISNULL(sr.send_attempts, 0) as retry_attempt,
      ISNULL(mi.from_address, '') as from_address,
      ISNULL(mi.reply_to, '')     as reply_to
   FROM sysmail_mailitems as mi
      LEFT JOIN sysmail_send_retries as sr
         ON sr.mailitem_id = mi.mailitem_id 
   WHERE mi.mailitem_id = @mailitem_id

   -----
   -- 3) Account information 
   SELECT a.account_id,
         a.name
   FROM msdb.dbo.sysmail_profileaccount as pa
   JOIN msdb.dbo.sysmail_account as a
      ON pa.account_id = a.account_id
   WHERE pa.profile_id = @profile_id
   ORDER BY pa.sequence_number 

   -----
   -- 4) Attachments if any
   SELECT attachment_id,
      mailitem_id,
      filename,
      filesize,
      attachment
   FROM sysmail_attachments
   WHERE mailitem_id = @mailitem_id
   

   RETURN 0
END
GO

-----------------------------------------------------------
-- procedure sp_process_DialogTimer
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_process_DialogTimer', 'P') IS NULL
    DROP PROCEDURE dbo.sp_process_DialogTimer
GO
-----
PRINT 'Creating sp_process_DialogTimer'
-----
GO
-- Processes a DialogTimer message from the the queue. This is used for send mail retries.
-- Returns the mail to be send if a retry is required or logs a failure if max retry count has been reached 
CREATE PROCEDURE sp_process_DialogTimer
    @conversation_handle    uniqueidentifier,
   @service_contract_name  NVARCHAR(256),
   @message_type_name      NVARCHAR(256)
AS
BEGIN
    SET NOCOUNT ON

    -- Declare all variables
    DECLARE 
        @mailitem_id        INT,
        @profile_id         INT,
        @send_attempts      INT,
        @mail_request_date  DATETIME,
        @localmessage       NVARCHAR(255),
        @paramStr           NVARCHAR(256),
        @accRetryAttempts   INT

    -- Get the associated mail item data for the given @conversation_handle
   SELECT @mailitem_id     = mi.mailitem_id,
      @profile_id         = mi.profile_id,
        @mail_request_date  = mi.send_request_date,
      @send_attempts      = sr.send_attempts
   FROM sysmail_send_retries as sr
      JOIN sysmail_mailitems as mi
        ON sr.mailitem_id = mi.mailitem_id
   WHERE sr.conversation_handle = @conversation_handle

   -- If not able to find a mailitem_id return and move to the next message.
    -- This could happen if the mail items table was cleared before the retry was fired
   IF(@mailitem_id IS NULL)
   BEGIN
        --Log warning and continue
        -- "mailitem_id on conversation %s was not found in the sysmail_send_retries table. This mail item will not be sent."
        SET @localmessage = FORMATMESSAGE(14662, convert(NVARCHAR(50), @conversation_handle))
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=2, @description=@localmessage

        -- Resource clean-up
        IF(@conversation_handle IS NOT NULL)
        BEGIN
           END CONVERSATION @conversation_handle;
        END

        -- return code has special meaning and will be propagated to the calling process
        RETURN 2;
    END


    --Get the retry attempt count from sysmailconfig.
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'AccountRetryAttempts', 
                                                 @parameter_value = @paramStr OUTPUT
    --ConvertToInt will return the default if @paramStr is null
    SET @accRetryAttempts = dbo.ConvertToInt(@paramStr, 0x7fffffff, 1)


   --Check the send attempts and log and error if send_attempts >= retry count.
    --This shouldn't happen unless the retry configuration was changed
   IF(@send_attempts > @accRetryAttempts)
   BEGIN
        --Log warning and continue
        -- "Mail Id %d has exceeded the retry count. This mail item will not be sent."
        SET @localmessage = FORMATMESSAGE(14663, @mailitem_id)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=2, @description=@localmessage, @mailitem_id=@mailitem_id

        -- Resource clean-up
        IF(@conversation_handle IS NOT NULL)
        BEGIN
           END CONVERSATION @conversation_handle;
        END

        -- return code has special meaning and will be propagated to the calling process
        RETURN 3;
    END

    -- This returns the mail item to the client as multiple result sets
    EXEC sp_MailItemResultSets
            @mailitem_id            = @mailitem_id,
            @profile_id             = @profile_id,
            @conversation_handle    = @conversation_handle,
           @service_contract_name  = @service_contract_name,
           @message_type_name      = @message_type_name

   
   RETURN 0

END
GO

-----------------------------------------------------------
-- procedure sp_readrequest
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_readrequest', 'P') IS NULL
    DROP PROCEDURE dbo.sp_readrequest
GO
-----
PRINT 'Creating sp_readrequest'
-----
GO
-- sp_readrequest : Reads a request from the the queue and returns its
--                  contents.
CREATE PROCEDURE sp_readrequest
    @receive_timeout    INT     -- the max time this read will wait for new message
AS
BEGIN
    SET NOCOUNT ON

    -- Table to store message information.
    DECLARE @msgs TABLE
    (
       [conversation_handle] uniqueidentifier,
       [service_contract_name] nvarchar(256),
       [message_type_name] nvarchar(256),
       [message_body] varbinary(max)
    )

    -- Declare variables to store row values fetched from the cursor
    DECLARE 
        @exit                   INT,
        @idoc                   INT,
        @mailitem_id            INT,
        @profile_id             INT,
        @conversation_handle    uniqueidentifier,
        @service_contract_name  NVARCHAR(256),
        @message_type_name      NVARCHAR(256),
        @xml_message_body       VARCHAR(max),
        @timediff               INT,
        @rec_timeout            INT,
        @start_time             DATETIME,
        @localmessage           NVARCHAR(256),
        @rc                     INT

    --Init variables
    SELECT @start_time = GETDATE(),
           @timediff = 0,
           @exit = 0,
           @rc = 0

    WHILE (@timediff <= @receive_timeout)
    BEGIN
        -- Delete all messages from @msgs table
        DELETE FROM @msgs

        -- Pick all message from queue
        SET @rec_timeout = @receive_timeout - @timediff
        WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body 
                FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

        -- Check if there was some error in reading from queue
        SET @rc = @@ERROR
        IF (@rc <> 0)
        BEGIN
           IF(@rc < 4) -- make sure return code is not in reserved range (1-3)
               SET @rc = 4

           --Note: we will get error no. 9617 if the service queue 'ExternalMailQueue' is currently disabled.
           BREAK
        END

       --If there is no message in the queue return 1 to indicate a timeout
        IF NOT EXISTS(SELECT * FROM @msgs)
        BEGIN
          SET @rc = 1
          BREAK
        END

        -- Create a cursor to iterate through the messages.
        DECLARE msgs_cursor CURSOR FOR
        SELECT conversation_handle, 
            service_contract_name, 
            message_type_name,
            CONVERT(VARCHAR(MAX), message_body)
        FROM @msgs;

        -- Open the cursor
        OPEN msgs_cursor;

        -- Perform the first fetch and store the values in the variables.
        FETCH NEXT FROM msgs_cursor
        INTO 
            @conversation_handle,
            @service_contract_name,
            @message_type_name,
            @xml_message_body

        -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            -- Check if the message is a send mail message
            IF(@message_type_name = N'{//www.microsoft.com/databasemail/messages}SendMail')
            BEGIN
                -- Get the handle to the xml document
                EXEC @rc = sp_xml_preparedocument 
                                @idoc OUTPUT, 
                                @xml_message_body, 
                                N'<ns xmlns:requests="http://schemas.microsoft.com/databasemail/requests" />'
                IF(@rc <> 0)
                BEGIN
                   IF(@rc < 4) -- make sure return code is not in reserved rang (1-3)
                      SET @rc = 4
                END
                ELSE -- parse the document and process its contents
                BEGIN
                   -- Execute a SELECT statement that uses the OPENXML rowset provider to get the MailItemId.
                   SELECT @mailitem_id = MailItemId
                   FROM OPENXML (@idoc, '/requests:SendMail', 1)
                        WITH (MailItemId INT './MailItemId')

                   --Close the handle to the xml document
                   EXEC sp_xml_removedocument @idoc

                   -- get account information 
                   SELECT @profile_id = profile_id
                   FROM sysmail_mailitems 
                   WHERE mailitem_id = @mailitem_id

                   IF(@profile_id IS NULL) -- mail item has been deleted from the database
                   BEGIN
                      -- log warning
                      SET @localmessage = FORMATMESSAGE(14667, convert(NVARCHAR(50), @mailitem_id))
                      exec msdb.dbo.sysmail_logmailevent_sp @event_type=2, @description=@localmessage

                      -- Resource clean-up
                      IF(@conversation_handle IS NOT NULL)
                         END CONVERSATION @conversation_handle;
                   
                      -- return code has special meaning and will be propagated to the calling process
                      SET @rc = 2
                   END
                   ELSE
                   BEGIN
                      -- This returns the mail item to the client as multiple result sets
                      EXEC sp_MailItemResultSets
                           @mailitem_id            = @mailitem_id,
                           @profile_id             = @profile_id,
                           @conversation_handle    = @conversation_handle,
                           @service_contract_name  = @service_contract_name,
                           @message_type_name      = @message_type_name
                
                      SET @exit = 1 -- make sure we exit outer loop
                   END
                END

                -- always break from the loop upon processing SendMail message
                BREAK
            END
            -- Check if the message is a dialog timer. This is used for account retries
            ELSE IF(@message_type_name = N'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer')
            BEGIN
                -- Handle the retry case. - DialogTimer is used for send mail reties
                EXEC @rc = sp_process_DialogTimer
                            @conversation_handle    = @conversation_handle,
                            @service_contract_name  = @service_contract_name,
                            @message_type_name      = N'{//www.microsoft.com/databasemail/messages}SendMail'

                -- Always break from the loop upon processing DialogTimer message
                -- In case of failure return code from procedure call will simply be propagated to the calling process
                SET @exit = 1 -- make sure we exit outer loop
                BREAK
            END
            -- Error case
            ELSE IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
            -- Error in the conversation, hence ignore all the messages of this conversation.
                BREAK

            -- This is executed as long as fetch succeeds.
            FETCH NEXT FROM msgs_cursor
            INTO 
                @conversation_handle,
                @service_contract_name,
                @message_type_name,
                @xml_message_body
        END

        CLOSE msgs_cursor;
        DEALLOCATE msgs_cursor;

        -- Check if we read any request or only SSB generated messages
        -- If a valid request is read with or without an error break out of loop
        IF (@exit = 1 OR @rc <> 0)
            BREAK

       --Keep track of how long this sp has been running
        select @timediff = DATEDIFF(ms, @start_time, getdate()) 
    END

    -- propagate return code to the calling process
    RETURN @rc
END
GO


-----------------------------------------------------------
-- procedure sp_GetAttachmentData
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_GetAttachmentData', 'P') IS NULL
    DROP PROCEDURE dbo.sp_GetAttachmentData
GO
-----
PRINT 'Creating sp_GetAttachmentData'
-----
GO

CREATE PROCEDURE sp_GetAttachmentData
   @attachments           nvarchar(max),
   @temp_table_uid        uniqueidentifier,
   @exclude_query_output  BIT        = 0
AS
BEGIN
    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @rc             INT,
            @prohibitedExts NVARCHAR(1000),
            @attachFilePath NVARCHAR(260),
            @scIndex        INT,
            @startLocation  INT,
            @fileSizeStr    NVARCHAR(256),
            @fileSize       INT,
            @mailDbName     sysname,
            @uidStr         VARCHAR(36)

    --Get the maximum file size allowed for attachments from sysmailconfig.
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxFileSize', 
                                                @parameter_value = @fileSizeStr OUTPUT
    --ConvertToInt will return the default if @fileSizeStr is null
    SET @fileSize = dbo.ConvertToInt(@fileSizeStr, 0x7fffffff, 100000)

    --May need this if attaching files
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ProhibitedExtensions', 
                                                @parameter_value = @prohibitedExts OUTPUT

    SET @mailDbName = DB_NAME()
    SET @uidStr = CONVERT(VARCHAR(36), @temp_table_uid)

   SET @attachments = @attachments + ';'
   SET @startLocation = 0
   SET @scIndex = CHARINDEX(';', @attachments, @startLocation)

   WHILE (@scIndex <> 0)
   BEGIN
      SET @attachFilePath = SUBSTRING(@attachments, @startLocation, (@scIndex - @startLocation))
      
      -- Make sure we have an attachment file name to work with, and that it hasn't been truncated
      IF (@scIndex - @startLocation > 260 )
      BEGIN
            RAISERROR(14628, 16, 1)
          RETURN 1 
      END

        IF ((@attachFilePath IS NULL) OR (LEN(@attachFilePath) = 0))
        BEGIN
            RAISERROR(14628, 16, 1)
          RETURN 1 
        END

      --Check if attachment ext is allowed 
      EXEC @rc = sp_isprohibited @attachFilePath, @prohibitedExts
      IF (@rc <> 0)
      BEGIN
          RAISERROR(14630, 16, 1, @attachFilePath, @prohibitedExts)
          RETURN 2
      END

        DECLARE  @no_output_int  INT
        SET @no_output_int         = CONVERT(int, @exclude_query_output)

        -- return code checked after select and delete calls
        EXEC @rc = master..xp_sysmail_attachment_load @message       = @mailDbName, 
                                                      @attachments      = @attachFilePath,
                                                      @subject       = @uidStr,
                                                      @max_attachment_size = @fileSize,
                                                      @no_output = @no_output_int
      IF (@rc <> 0)
            RETURN (@rc)
               
        --Get next substring index
      SET @startLocation = @scIndex + 1
      SET @scIndex = CHARINDEX(';', @attachments, @startLocation)

      IF (@scIndex = 0)
         BREAK
   END

    RETURN 0
END
GO


-----------------------------------------------------------
-- procedure sp_RunMailQuery
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_RunMailQuery', 'P') IS NULL
    DROP PROCEDURE dbo.sp_RunMailQuery
GO
-----
PRINT 'Creating sp_RunMailQuery'
-----
USE msdb
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_RunMailQuery]
   @query                      NVARCHAR(max),
   @attach_results             BIT,
   @query_attachment_filename  NVARCHAR(260) = NULL,
   @no_output                  BIT,
   @query_result_header        BIT,
   @separator                  VARCHAR(1),
   @echo_error                 BIT,
   @dbuse                      sysname,
   @width                      INT,
   @temp_table_uid             uniqueidentifier,
   @query_no_truncate          BIT,
   @query_result_no_padding    BIT
AS
BEGIN
    SET NOCOUNT ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @rc             INT,
            @prohibitedExts NVARCHAR(1000),
            @fileSizeStr    NVARCHAR(256),
            @fileSize       INT,
            @attach_res_int INT,
            @no_output_int  INT,
            @no_header_int  INT,
            @echo_error_int INT,
         @query_no_truncate_int INT,
         @query_result_no_padding_int   INT,
            @mailDbName     sysname,
            @uid            uniqueidentifier,
            @uidStr         VARCHAR(36)

    --
    --Get config settings and verify parameters
    --
    SET @query_attachment_filename = LTRIM(RTRIM(@query_attachment_filename))

    --Get the maximum file size allowed for attachments from sysmailconfig.
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxFileSize', 
                                                @parameter_value = @fileSizeStr OUTPUT
    --ConvertToInt will return the default if @fileSizeStr is null
    SET @fileSize = dbo.ConvertToInt(@fileSizeStr, 0x7fffffff, 100000)

    IF (@attach_results = 1)
    BEGIN
        --Need this if attaching the query
        EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ProhibitedExtensions', 
                                                    @parameter_value = @prohibitedExts OUTPUT

        -- If attaching query results to a file and a filename isn't given create one
        IF ((@query_attachment_filename IS NOT NULL) AND (LEN(@query_attachment_filename) > 0))
        BEGIN 
          EXEC @rc = sp_isprohibited @query_attachment_filename, @prohibitedExts
          IF (@rc <> 0)
          BEGIN
              RAISERROR(14630, 16, 1, @query_attachment_filename, @prohibitedExts)
              RETURN 2
          END
        END
        ELSE
        BEGIN
            --If queryfilename is not specified, generate a random name (doesn't have to be unique)
           SET @query_attachment_filename = 'QueryResults' + CONVERT(varchar, ROUND(RAND() * 1000000, 0)) + '.txt'
        END
    END

    --Init variables used in the query execution
    SET @mailDbName = db_name()
    SET @uidStr = convert(varchar(36), @temp_table_uid)

    SET @attach_res_int        = CONVERT(int, @attach_results)
    SET @no_output_int         = CONVERT(int, @no_output)
    IF(@query_result_header = 0) SET @no_header_int  = 1 ELSE SET @no_header_int  = 0
    SET @echo_error_int        = CONVERT(int, @echo_error)
    SET @query_no_truncate_int = CONVERT(int, @query_no_truncate)
    SET @query_result_no_padding_int = CONVERT(int, @query_result_no_padding )

    EXEC @rc = master..xp_sysmail_format_query  
                @query        = @query,
                @message      = @mailDbName,
                    @subject     = @uidStr,
                    @dbuse       = @dbuse, 
                    @attachments = @query_attachment_filename,
                    @attach_results = @attach_res_int,
                    -- format params
                    @separator      = @separator,
                    @no_header      = @no_header_int,
                    @no_output      = @no_output_int,
                    @echo_error     = @echo_error_int,
                @max_attachment_size = @fileSize,
                    @width       = @width, 
                    @query_no_truncate = @query_no_truncate_int,
                    @query_result_no_padding    = @query_result_no_padding_int
   RETURN @rc
END
GO

-----------------------------------------------------------
-- procedure sp_validate_user, used by sp_send_dbmail
-----------------------------------------------------------
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_validate_user')))
  DROP PROCEDURE sp_validate_user
go
use msdb
GO
/****** Object:  StoredProcedure [dbo].sp_validate_user ********/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_validate_user]
    @send_request_user sysname,
    @user_sid varbinary(85) OUTPUT
  WITH EXECUTE AS 'dbo'
AS
BEGIN
    SET NOCOUNT ON
    -- And make sure ARITHABORT is on. This is the default for yukon DB's
    SET ARITHABORT ON

    declare @groupSid varbinary(85)
    declare @temp table
    ([account name] sysname, 
    [type] char(8),
    [privilege] char(9),
    [mapped login name] sysname,
    [permission path] sysname)

    SET @user_sid = NULL
    SET @groupSid = NULL

    -- Lookup the Windows Group membership, if any, that grants this
    -- user access to SQL Server. xp_logininfo may fail if the sql
    -- server service account cannot talk to the domain controller to
    -- validate the windows username, or it may fail if the
    -- @send_request_user is not a valid windows user or group name.
    BEGIN TRY 
        insert @temp exec master.dbo.xp_logininfo @send_request_user
        select @groupSid = suser_sid([permission path]) from @temp
    END TRY
    BEGIN CATCH
        RETURN 2
    END CATCH

    -- Lookup a default profile for the Group. If there is one,
    -- then use the group's mail profile.
    IF ((@groupSid IS NOT NULL) AND (EXISTS(SELECT * 
                                            FROM msdb.dbo.sysmail_principalprofile as pp
                                            WHERE (pp.is_default = 1) AND
                                            (pp.principal_sid = @groupSid))))
    BEGIN
        SET @user_sid = @groupSid
        RETURN 0
    END
    RETURN 1
END
GO



-----------------------------------------------------------
-- procedure sp_send_dbmail
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_send_dbmail', 'P') IS NULL
    DROP PROCEDURE dbo.sp_send_dbmail
GO
-----
PRINT 'Creating sp_send_dbmail'
-----
USE msdb
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- sp_send_dbmail : Sends a mail from Yukon outbox.
--
CREATE PROCEDURE [dbo].[sp_send_dbmail]
   @profile_name               sysname    = NULL,        
   @recipients                 VARCHAR(MAX)  = NULL, 
   @copy_recipients            VARCHAR(MAX)  = NULL,
   @blind_copy_recipients      VARCHAR(MAX)  = NULL,
   @subject                    NVARCHAR(255) = NULL,
   @body                       NVARCHAR(MAX) = NULL, 
   @body_format                VARCHAR(20)   = NULL, 
   @importance                 VARCHAR(6)    = 'NORMAL',
   @sensitivity                VARCHAR(12)   = 'NORMAL',
   @file_attachments           NVARCHAR(MAX) = NULL,  
   @query                      NVARCHAR(MAX) = NULL,
   @execute_query_database     sysname       = NULL,  
   @attach_query_result_as_file BIT          = 0,
   @query_attachment_filename  NVARCHAR(260) = NULL,  
   @query_result_header        BIT           = 1,
   @query_result_width         INT           = 256,            
   @query_result_separator     CHAR(1)       = ' ',
   @exclude_query_output       BIT           = 0,
   @append_query_error         BIT           = 0,
   @query_no_truncate          BIT           = 0,
   @query_result_no_padding    BIT           = 0,
   @mailitem_id               INT            = NULL OUTPUT,
   @from_address               VARCHAR(max)  = NULL,
   @reply_to                   VARCHAR(max)  = NULL
  WITH EXECUTE AS 'dbo'
AS
BEGIN
    SET NOCOUNT ON

    -- And make sure ARITHABORT is on. This is the default for yukon DB's
    SET ARITHABORT ON

    --Declare variables used by the procedure internally
    DECLARE @profile_id         INT,
            @temp_table_uid     uniqueidentifier,
            @sendmailxml        VARCHAR(max),
            @CR_str             NVARCHAR(2),
            @localmessage       NVARCHAR(255),
            @QueryResultsExist  INT,
            @AttachmentsExist   INT,
            @RetErrorMsg        NVARCHAR(4000), --Impose a limit on the error message length to avoid memory abuse 
            @rc                 INT,
            @procName           sysname,
            @trancountSave      INT,
            @tranStartedBool    INT,
            @is_sysadmin        BIT,
            @send_request_user  sysname,
            @database_user_id   INT,
            @sid                varbinary(85)

    -- Initialize 
    SELECT  @rc                 = 0,
            @QueryResultsExist  = 0,
            @AttachmentsExist   = 0,
            @temp_table_uid     = NEWID(),
            @procName           = OBJECT_NAME(@@PROCID),
            @tranStartedBool    = 0,
            @trancountSave      = @@TRANCOUNT,
            @sid                = NULL

    EXECUTE AS CALLER
       SELECT @is_sysadmin       = IS_SRVROLEMEMBER('sysadmin'),
              @send_request_user = SUSER_SNAME(),
              @database_user_id  = USER_ID()
    REVERT

    --Check if SSB is enabled in this database
    IF (ISNULL(DATABASEPROPERTYEX(DB_NAME(), N'IsBrokerEnabled'), 0) <> 1)
    BEGIN
       RAISERROR(14650, 16, 1)
       RETURN 1
    END

    --Report error if the mail queue has been stopped. 
    --sysmail_stop_sp/sysmail_start_sp changes the receive status of the SSB queue
    IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1)
    BEGIN
       RAISERROR(14641, 16, 1)
       RETURN 1
    END

    -- Get the relevant profile_id 
    --
    IF (@profile_name IS NULL)
    BEGIN
        -- Use the global or users default if profile name is not supplied
        SELECT TOP (1) @profile_id = pp.profile_id
        FROM msdb.dbo.sysmail_principalprofile as pp
        WHERE (pp.is_default = 1) AND
            (dbo.get_principal_id(pp.principal_sid) = @database_user_id OR pp.principal_sid = 0x00)
        ORDER BY dbo.get_principal_id(pp.principal_sid) DESC

        --Was a profile found
        IF(@profile_id IS NULL)
        BEGIN
            -- Try a profile lookup based on Windows Group membership, if any
            EXEC @rc = msdb.dbo.sp_validate_user @send_request_user, @sid OUTPUT
            IF (@rc = 0)
            BEGIN
                SELECT TOP (1) @profile_id = pp.profile_id
                FROM msdb.dbo.sysmail_principalprofile as pp
                WHERE (pp.is_default = 1) AND
                      (pp.principal_sid = @sid)
                ORDER BY dbo.get_principal_id(pp.principal_sid) DESC
            END

            IF(@profile_id IS NULL)
            BEGIN
                RAISERROR(14636, 16, 1)
                RETURN 1
            END
        END
    END
    ELSE
    BEGIN
        --Get primary account if profile name is supplied
        EXEC @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id = NULL, 
                         @profile_name = @profile_name, 
                         @allow_both_nulls = 0, 
                         @allow_id_name_mismatch = 0,
                         @profileid = @profile_id OUTPUT
        IF (@rc <> 0)
            RETURN @rc

        --Make sure this user has access to the specified profile.
        --sysadmins can send on any profiles
        IF ( @is_sysadmin <> 1)
        BEGIN
            --Not a sysadmin so check users access to profile
            iF NOT EXISTS(SELECT * 
                        FROM msdb.dbo.sysmail_principalprofile 
                        WHERE ((profile_id = @profile_id) AND
                                (dbo.get_principal_id(principal_sid) = @database_user_id OR principal_sid = 0x00)))
            BEGIN
                EXEC msdb.dbo.sp_validate_user @send_request_user, @sid OUTPUT
                IF(@sid IS NULL)
                BEGIN
                    RAISERROR(14607, -1, -1, 'profile')
                    RETURN 1
                END
            END
        END
    END

    --Attach results must be specified
    IF @attach_query_result_as_file IS NULL
    BEGIN
       RAISERROR(14618, 16, 1, 'attach_query_result_as_file')
       RETURN 2
    END

    --No output must be specified
    IF @exclude_query_output IS NULL
    BEGIN
       RAISERROR(14618, 16, 1, 'exclude_query_output')
       RETURN 3
    END

    --No header must be specified
    IF @query_result_header IS NULL
    BEGIN
       RAISERROR(14618, 16, 1, 'query_result_header')
       RETURN 4
    END

    -- Check if query_result_separator is specifed
    IF @query_result_separator IS NULL OR DATALENGTH(@query_result_separator) = 0
    BEGIN
       RAISERROR(14618, 16, 1, 'query_result_separator')
       RETURN 5
    END

    --Echo error must be specified
    IF @append_query_error IS NULL
    BEGIN
       RAISERROR(14618, 16, 1, 'append_query_error')
       RETURN 6
    END

    --@body_format can be TEXT (default) or HTML
    IF (@body_format IS NULL)
    BEGIN
       SET @body_format = 'TEXT'
    END
    ELSE
    BEGIN
       SET @body_format = UPPER(@body_format)

       IF @body_format NOT IN ('TEXT', 'HTML') 
       BEGIN
          RAISERROR(14626, 16, 1, @body_format)
          RETURN 13
       END
    END

    --Importance must be specified
    IF @importance IS NULL
    BEGIN
       RAISERROR(14618, 16, 1, 'importance')
       RETURN 15
    END

    SET @importance = UPPER(@importance)

    --Importance must be one of the predefined values
    IF @importance NOT IN ('LOW', 'NORMAL', 'HIGH')
    BEGIN
       RAISERROR(14622, 16, 1, @importance)
       RETURN 16
    END

    --Sensitivity must be specified
    IF @sensitivity IS NULL
    BEGIN
       RAISERROR(14618, 16, 1, 'sensitivity')
       RETURN 17
    END

    SET @sensitivity = UPPER(@sensitivity)

    --Sensitivity must be one of predefined values
    IF @sensitivity NOT IN ('NORMAL', 'PERSONAL', 'PRIVATE', 'CONFIDENTIAL')
    BEGIN
       RAISERROR(14623, 16, 1, @sensitivity)
       RETURN 18
    END

    --Message body cannot be null. Atleast one of message, subject, query,
    --attachments must be specified.
    IF( (@body IS NULL AND @query IS NULL AND @file_attachments IS NULL AND @subject IS NULL)
       OR
    ( (LEN(@body) IS NULL OR LEN(@body) <= 0)  
       AND (LEN(@query) IS NULL  OR  LEN(@query) <= 0)
       AND (LEN(@file_attachments) IS NULL OR LEN(@file_attachments) <= 0)
       AND (LEN(@subject) IS NULL OR LEN(@subject) <= 0)
    )
    )
    BEGIN
       RAISERROR(14624, 16, 1, '@body, @query, @file_attachments, @subject')
       RETURN 19
    END   
    ELSE
       IF @subject IS NULL OR LEN(@subject) <= 0
          SET @subject='SQL Server Message'

    --Recipients cannot be empty. Atleast one of the To, Cc, Bcc must be specified
    IF ( (@recipients IS NULL AND @copy_recipients IS NULL AND 
       @blind_copy_recipients IS NULL
        )     
       OR
        ( (LEN(@recipients) IS NULL OR LEN(@recipients) <= 0)
       AND (LEN(@copy_recipients) IS NULL OR LEN(@copy_recipients) <= 0)
       AND (LEN(@blind_copy_recipients) IS NULL OR LEN(@blind_copy_recipients) <= 0)
        )
    )
    BEGIN
       RAISERROR(14624, 16, 1, '@recipients, @copy_recipients, @blind_copy_recipients')
       RETURN 20
    END

    --If query is not specified, attach results and no header cannot be true.
    IF ( (@query IS NULL OR LEN(@query) <= 0) AND @attach_query_result_as_file = 1)
    BEGIN
       RAISERROR(14625, 16, 1)
       RETURN 21
    END

    --
    -- Execute Query if query is specified
    IF ((@query IS NOT NULL) AND (LEN(@query) > 0))
    BEGIN
        EXECUTE AS CALLER
        EXEC @rc = sp_RunMailQuery 
                    @query                     = @query,
               @attach_results            = @attach_query_result_as_file,
                    @query_attachment_filename = @query_attachment_filename,
               @no_output                 = @exclude_query_output,
               @query_result_header       = @query_result_header,
               @separator                 = @query_result_separator,
               @echo_error                = @append_query_error,
               @dbuse                     = @execute_query_database,
               @width                     = @query_result_width,
                @temp_table_uid            = @temp_table_uid,
            @query_no_truncate         = @query_no_truncate,
            @query_result_no_padding           = @query_result_no_padding
      -- This error indicates that query results size was over the configured MaxFileSize.
      -- Note, an error has already beed raised in this case
      IF(@rc = 101)
         GOTO ErrorHandler;
         REVERT
 
         -- Always check the transfer tables for data. They may also contain error messages
         -- Only one of the tables receives data in the call to sp_RunMailQuery
         IF(@attach_query_result_as_file = 1)
         BEGIN
             IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid)
            SET @AttachmentsExist = 1
         END
         ELSE
         BEGIN
             IF EXISTS(SELECT * FROM sysmail_query_transfer WHERE uid = @temp_table_uid AND uid IS NOT NULL)
            SET @QueryResultsExist = 1
         END

         -- Exit if there was an error and caller doesn't want the error appended to the mail
         IF (@rc <> 0 AND @append_query_error = 0)
         BEGIN
            --Error msg with be in either the attachment table or the query table 
            --depending on the setting of @attach_query_result_as_file
            IF(@attach_query_result_as_file = 1)
            BEGIN
               --Copy query results from the attachments table to mail body
               SELECT @RetErrorMsg = CONVERT(NVARCHAR(4000), attachment)
               FROM sysmail_attachments_transfer 
               WHERE uid = @temp_table_uid
            END
            ELSE
            BEGIN
               --Copy query results from the query table to mail body
               SELECT @RetErrorMsg = text_data 
               FROM sysmail_query_transfer 
               WHERE uid = @temp_table_uid
            END

            GOTO ErrorHandler;
         END
         SET @AttachmentsExist = @attach_query_result_as_file
    END
    ELSE
    BEGIN
        --If query is not specified, attach results cannot be true.
        IF (@attach_query_result_as_file = 1)
        BEGIN
           RAISERROR(14625, 16, 1)
           RETURN 21
        END
    END

    --Get the prohibited extensions for attachments from sysmailconfig.
    IF ((@file_attachments IS NOT NULL) AND (LEN(@file_attachments) > 0)) 
    BEGIN
        EXECUTE AS CALLER
        EXEC @rc = sp_GetAttachmentData 
                        @attachments = @file_attachments, 
                        @temp_table_uid = @temp_table_uid,
                        @exclude_query_output = @exclude_query_output
        REVERT
        IF (@rc <> 0)
            GOTO ErrorHandler;
        
        IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid)
            SET @AttachmentsExist = 1
    END

    -- Start a transaction if not already in one. 
    -- Note: For rest of proc use GOTO ErrorHandler for falures  
    if (@trancountSave = 0) 
       BEGIN TRAN @procName

    SET @tranStartedBool = 1

    -- Store complete mail message for history/status purposes  
    INSERT sysmail_mailitems
    (
       profile_id,   
       recipients,
       copy_recipients,
       blind_copy_recipients,
       subject,
       body, 
       body_format, 
       importance,
       sensitivity,
       file_attachments,  
       attachment_encoding,
       query,
       execute_query_database,
       attach_query_result_as_file,
       query_result_header,
       query_result_width,          
       query_result_separator,
       exclude_query_output,
       append_query_error,
       send_request_user,
       from_address,
       reply_to
    )
    VALUES
    (
       @profile_id,        
       @recipients, 
       @copy_recipients,
       @blind_copy_recipients,
       @subject,
       @body, 
       @body_format, 
       @importance,
       @sensitivity,
       @file_attachments,  
       'MIME',
       @query,
       @execute_query_database,  
       @attach_query_result_as_file,
       @query_result_header,
       @query_result_width,            
       @query_result_separator,
       @exclude_query_output,
       @append_query_error,
       @send_request_user,
       @from_address,
       @reply_to
    )

    SELECT @rc          = @@ERROR,
           @mailitem_id = SCOPE_IDENTITY()

    IF(@rc <> 0)
        GOTO ErrorHandler;

    --Copy query into the message body
    IF(@QueryResultsExist = 1)
    BEGIN
      -- if the body is null initialize it
        UPDATE sysmail_mailitems
        SET body = N''
        WHERE mailitem_id = @mailitem_id
        AND body is null

        --Add CR, a \r followed by \n, which is 0xd and then 0xa
        SET @CR_str = CHAR(13) + CHAR(10)
        UPDATE sysmail_mailitems
        SET body.WRITE(@CR_str, NULL, NULL)
        WHERE mailitem_id = @mailitem_id

   --Copy query results to mail body
        UPDATE sysmail_mailitems
        SET body.WRITE( (SELECT text_data from sysmail_query_transfer WHERE uid = @temp_table_uid), NULL, NULL )
        WHERE mailitem_id = @mailitem_id

    END

    --Copy into the attachments table
    IF(@AttachmentsExist = 1)
    BEGIN
        --Copy temp attachments to sysmail_attachments      
        INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)
        SELECT @mailitem_id, filename, filesize, attachment
        FROM sysmail_attachments_transfer
        WHERE uid = @temp_table_uid
    END

    -- Create the primary SSB xml maessage
    SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
                        + CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'

    -- Send the send request on queue.
    EXEC @rc = sp_SendMailQueues @sendmailxml
    IF @rc <> 0
    BEGIN
       RAISERROR(14627, 16, 1, @rc, 'send mail')
       GOTO ErrorHandler;
    END

    -- Print success message if required
    IF (@exclude_query_output = 0)
    BEGIN
       SET @localmessage = FORMATMESSAGE(14635)
       PRINT @localmessage
    END  

    --
    -- See if the transaction needs to be commited
    --
    IF (@trancountSave = 0 and @tranStartedBool = 1)
       COMMIT TRAN @procName

    -- All done OK
    goto ExitProc;

    -----------------
    -- Error Handler
    -----------------
ErrorHandler:
    IF (@tranStartedBool = 1) 
       ROLLBACK TRAN @procName

    ------------------
    -- Exit Procedure
    ------------------
ExitProc:
   
    --Always delete query and attactment transfer records. 
   --Note: Query results can also be returned in the sysmail_attachments_transfer table
    DELETE sysmail_attachments_transfer WHERE uid = @temp_table_uid
    DELETE sysmail_query_transfer WHERE uid = @temp_table_uid

   --Raise an error it the query execution fails
   -- This will only be the case when @append_query_error is set to 0 (false)
   IF( (@RetErrorMsg IS NOT NULL) AND (@exclude_query_output=0) )
   BEGIN
      RAISERROR(14661, -1, -1, @RetErrorMsg)
   END

    RETURN (@rc)
END
GO

-----------------------------------------------------------
-- procedure sp_ExternalMailQueueListener
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_ExternalMailQueueListener', 'P') IS NULL
    DROP PROCEDURE dbo.sp_ExternalMailQueueListener
GO
-----
PRINT 'Creating sp_ExternalMailQueueListener'
-----
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF

GO
-- Processes messages from the external mail queue
--
CREATE PROCEDURE [dbo].[sp_ExternalMailQueueListener]
AS
BEGIN
    DECLARE 
        @idoc               INT,
        @mailitem_id        INT,
        @sent_status        INT,
        @sent_account_id    INT,
        @rc                 INT,
        @processId          INT,
        @sent_date          DATETIME,
        @localmessage       NVARCHAR(max),
        @conv_handle        uniqueidentifier,
       @message_type_name  NVARCHAR(256),
       @xml_message_body   NVARCHAR(max),
        @LogMessage         NVARCHAR(max)

    -- Table to store message information.
    DECLARE @msgs TABLE
    (
        [conversation_handle]   uniqueidentifier,
       [message_type_name]     nvarchar(256),
       [message_body]          varbinary(max)
    )

    --RECEIVE messages from the external queue. 
    --MailItem status messages are sent from the external sql mail process along with other SSB notifications and errors
    ;RECEIVE conversation_handle, message_type_name, message_body FROM InternalMailQueue INTO @msgs
    -- Check if there was some error in reading from queue
    SET @rc = @@ERROR
    IF (@rc <> 0)
    BEGIN
        --Log error and continue. Don't want to block the following messages on the queue
        SET @localmessage = FORMATMESSAGE(@@ERROR)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage

        GOTO ErrorHandler;
    END
   
    -----------------------------------
    --Process sendmail status messages
    SELECT 
        @conv_handle        = conversation_handle,
        @message_type_name  = message_type_name, 
        @xml_message_body   = CAST(message_body AS NVARCHAR(MAX))
    FROM @msgs 
    WHERE [message_type_name] = N'{//www.microsoft.com/databasemail/messages}SendMailStatus'

    IF(@message_type_name IS NOT NULL)
    BEGIN
        --
        --Expecting the xml body to be n the following form:
        --
        --<?xml version="1.0" encoding="utf-16"?>
        --<responses:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/responses ResponseTypes.xsd" xmlns:responses="http://schemas.microsoft.com/databasemail/responses">
        --<Information>
        --    <Failure Message="THe error message...."/>
        --</Information>
        --<MailItemId Id="1" />
        --<SentStatus Status="3" />
        --<SentAccountId Id="0" />
        --<SentDate Date="2005-03-30T14:55:13" />
        --<CallingProcess Id="3012" />
        --</responses:SendMail>

        -- Get the handle to the xml document
        EXEC @rc = sp_xml_preparedocument 
                        @idoc OUTPUT, 
                        @xml_message_body, 
                        N'<ns xmlns:responses="http://schemas.microsoft.com/databasemail/responses" />'
        IF(@rc <> 0)
        BEGIN
            --Log error and continue. Don't want to block the following messages on the queue
            SET @localmessage = FORMATMESSAGE(14655, CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body)
            exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage

            GOTO ErrorHandler;
        END

        -- Execute a SELECT statement that uses the OPENXML rowset provider to get the MailItemId and sent status.
        SELECT @mailitem_id     = MailItemId, 
               @sent_status     = SentStatus,
               @sent_account_id = SentAccountId,
               @sent_date       = SentDate,
               @processId       = CallingProcess,
               @LogMessage      = LogMessage
        FROM OPENXML (@idoc, '/responses:SendMail', 1)
            WITH (MailItemId    INT      './MailItemId/@Id', 
                  SentStatus    INT      './SentStatus/@Status',
                  SentAccountId INT      './SentAccountId/@Id',
                  SentDate      DATETIME './SentDate/@Date', --The date was formated using ISO8601
                  CallingProcess INT     './CallingProcess/@Id', 
                  LogMessage     NVARCHAR(max) './Information/Failure/@Message')

        --Close the handle to the xml document
        EXEC sp_xml_removedocument @idoc

        IF(@mailitem_id IS NULL)
        BEGIN  
            --Log error and continue. Don't want to block the following messages on the queue by rolling back the tran
            SET @localmessage = FORMATMESSAGE(14652, CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body)
            exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
        END      
        ELSE
        BEGIN
            -- check sent_status is valid : 0(PendingSend), 1(SendSuccessful), 2(SendFailed), 3(AttemptingSendRetry)
            IF(@sent_status NOT IN (1, 2, 3))
            BEGIN
                SET @localmessage = FORMATMESSAGE(14653, N'SentStatus', CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body)
                exec msdb.dbo.sysmail_logmailevent_sp @event_type=2, @description=@localmessage

                --Set value to SendFailed
                SET @sent_status = 2
            END

            --Make the @sent_account_id NULL if it is 0. 
            IF(@sent_account_id IS NOT NULL AND @sent_account_id = 0)
                SET @sent_account_id = NULL

            --
            -- Update the mail status if not a retry. Nothing else needs to be done in this case
            UPDATE sysmail_mailitems
            SET sent_status     = CAST (@sent_status as TINYINT),
                sent_account_id = @sent_account_id,
                sent_date       = @sent_date
            WHERE mailitem_id = @mailitem_id
        
            -- Report a failure if no record is found in the sysmail_mailitems table
            IF (@@ROWCOUNT = 0)
            BEGIN
                SET @localmessage = FORMATMESSAGE(14653, N'MailItemId', CONVERT(NVARCHAR(50), @conv_handle), @message_type_name, @xml_message_body)
                exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
            END

            IF (@LogMessage IS NOT NULL)
            BEGIN
                exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@LogMessage, @process_id=@processId, @mailitem_id=@mailitem_id, @account_id=@sent_account_id
            END
        END
    END

    -------------------------------------------------------
    --Process all other messages by logging to sysmail_log
    SET @conv_handle = NULL;
    
    --Always end the conversion if this message is received
    SELECT @conv_handle = conversation_handle
    FROM @msgs 
    WHERE [message_type_name] = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    
    IF(@conv_handle IS NOT NULL)
    BEGIN
        END CONVERSATION @conv_handle;
    END

    DECLARE @queuemessage nvarchar(max)
    DECLARE queue_messages_cursor CURSOR LOCAL 
    FOR
        SELECT FORMATMESSAGE(14654, CONVERT(NVARCHAR(50), conversation_handle), message_type_name, message_body)
        FROM @msgs 
        WHERE [message_type_name] 
              NOT IN (N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
                      N'{//www.microsoft.com/databasemail/messages}SendMailStatus')
  
    OPEN queue_messages_cursor 
    FETCH NEXT FROM queue_messages_cursor INTO @queuemessage
    WHILE (@@fetch_status = 0)
    BEGIN
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=2, @description=@queuemessage
        FETCH NEXT FROM queue_messages_cursor INTO @queuemessage
    END
    CLOSE queue_messages_cursor 
    DEALLOCATE queue_messages_cursor 

    -- All done OK
    goto ExitProc;

    -----------------
    -- Error Handler
    -----------------
ErrorHandler:

    ------------------
    -- Exit Procedure
    ------------------
ExitProc:
    RETURN (@rc)
END
GO

----------------------------------------------------------
-- procedure sp_sysmail_activate
-----------------------------------------------------------
IF NOT OBJECT_ID('dbo.sp_sysmail_activate', 'P') IS NULL
    DROP PROCEDURE dbo.sp_sysmail_activate
GO

-----
PRINT 'Creating sp_sysmail_activate'
-----
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

GO
-- sp_sysmail_activate : Starts the DatabaseMail process if it isn't already running
--
CREATE PROCEDURE [dbo].[sp_sysmail_activate]
AS
BEGIN
    DECLARE @mailDbName sysname
    DECLARE @mailDbId INT
    DECLARE @mailEngineLifeMin INT
    DECLARE @loggingLevel nvarchar(256)
    DECLARE @loggingLevelInt int   
    DECLARE @parameter_value nvarchar(256)
    DECLARE @localmessage nvarchar(max)
    DECLARE @readFromConfigFile INT
    DECLARE @rc INT

    SET NOCOUNT ON
    EXEC sp_executesql @statement = N'RECEIVE TOP(0) * FROM msdb.dbo.ExternalMailQueue'

    EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime', 
                                                        @parameter_value = @parameter_value OUTPUT
    IF(@rc <> 0)
        RETURN (1)

    --ConvertToInt will return the default if @parameter_value is null or config value can't be converted
    --Setting max exe lifetime is 1 week (604800 secs). Can't see a reason for it to ever run longer that this
    SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600) 

    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ReadFromConfigurationFile', 
                                                  @parameter_value = @parameter_value OUTPUT
    --Try to read the optional read from configuration file:
    SET @readFromConfigFile = dbo.ConvertToInt(@parameter_value, 1, 0) 

    --Try and get the optional logging level for the DatabaseMail process
    EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel', 
                                                  @parameter_value = @loggingLevel OUTPUT

    --Convert logging level into string value for passing into XP
    SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2) 
    IF @loggingLevelInt = 1
       SET @loggingLevel = 'Normal'
    ELSE IF @loggingLevelInt = 3
       SET @loggingLevel = 'Verbose'
    ELSE -- default
       SET @loggingLevel = 'Extended'

    SET @mailDbName = DB_NAME()
    SET @mailDbId   = DB_ID()

    EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @readFromConfigFile,
    @mailEngineLifeMin, @loggingLevel
    IF(@rc <> 0)
    BEGIN
        SET @localmessage = FORMATMESSAGE(14637)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
    END
    ELSE
    BEGIN
        SET @localmessage = FORMATMESSAGE(14638)
        exec msdb.dbo.sysmail_logmailevent_sp @event_type=0, @description=@localmessage
    END

    RETURN @rc
END
GO

--------------------------------------------------------------
-- Database Mail roles and permissions
--------------------------------------------------------------

-- Create the DatabaseMailUserRole role
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysusers
            WHERE (name = N'DatabaseMailUserRole')
              AND (issqlrole = 1)))
BEGIN
  -- If there are no members in the role, then drop and re-create it
  IF ((SELECT COUNT(*)
       FROM msdb.dbo.sysusers   su,
            msdb.dbo.sysmembers sm
       WHERE (su.uid = sm.groupuid)
         AND (su.name = N'DatabaseMailUserRole')
         AND (su.issqlrole = 1)) = 0)
  BEGIN
    EXECUTE msdb.dbo.sp_droprole @rolename = N'DatabaseMailUserRole'
    EXECUTE msdb.dbo.sp_addrole @rolename = N'DatabaseMailUserRole'
  END
END
ELSE
  EXECUTE msdb.dbo.sp_addrole @rolename = N'DatabaseMailUserRole'  

go

GRANT EXECUTE   ON [dbo].[sp_send_dbmail]                TO DatabaseMailUserRole
                                                    
GRANT EXECUTE   ON [dbo].[sysmail_help_status_sp]        TO DatabaseMailUserRole
GRANT EXECUTE   ON [dbo].[sysmail_delete_mailitems_sp]   TO DatabaseMailUserRole

GRANT SELECT   ON [dbo].[sysmail_allitems]          TO DatabaseMailUserRole
GRANT SELECT   ON [dbo].[sysmail_sentitems]         TO DatabaseMailUserRole
GRANT SELECT   ON [dbo].[sysmail_unsentitems]       TO DatabaseMailUserRole
GRANT SELECT   ON [dbo].[sysmail_faileditems]       TO DatabaseMailUserRole

     
GRANT  SELECT   ON [dbo].[sysmail_mailattachments]  TO DatabaseMailUserRole
GRANT  SELECT   ON [dbo].[sysmail_event_log]        TO DatabaseMailUserRole

go

/*************************************************************************/
/*                                                                       */
/*  Database Mail SSB objects (Messages, Contracts, Queues, Services)    */
/*                                                                       */
/*************************************************************************/

PRINT ''
PRINT 'Dropping Database Mail MESSAGES, CONTRACTS, QUEUES AND SERVICES...'
PRINT ''

-- Drop service InternalMailService if existing.
IF EXISTS (SELECT * FROM sys.services WHERE name ='InternalMailService')
BEGIN
   PRINT 'Dropping SERVICE InternalMailService'
    DROP SERVICE InternalMailService;
END

-- Drop service ExternalMailService if existing.
IF EXISTS (SELECT * FROM sys.services WHERE name ='ExternalMailService')
BEGIN
   PRINT 'Dropping SERVICE ExternalMailService'
    DROP SERVICE ExternalMailService;
END

-- Drop queue InternalMailQueue if existing.
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'InternalMailQueue' AND type = 'SQ')
BEGIN
   PRINT 'Dropping QUEUE InternalMailQueue'
    DROP QUEUE InternalMailQueue;
END    

-- Drop queue ExternalMailQueue if existing.
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ExternalMailQueue' AND type = 'SQ')
BEGIN
   PRINT 'Dropping QUEUE ExternalMailQueue'
    DROP QUEUE ExternalMailQueue;
END    

--Drop Notification service for activation of DatabaseMail.exe
IF EXISTS (SELECT * FROM sys.services WHERE name ='SQL/Notifications/SysMailNotification/v1.0')
BEGIN
   PRINT 'Dropping SERVICE [SQL/Notifications/SysMailNotification/v1.0]'
    DROP SERVICE [SQL/Notifications/SysMailNotification/v1.0];
END    

--Drop SysMailNotificationQueue if existing
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'SysMailNotificationQueue' AND type = 'SQ')
BEGIN
   PRINT 'Dropping QUEUE SysMailNotificationQueue'
    DROP QUEUE SysMailNotificationQueue;
END    

-- Drop SendMail v1.0 contract if existing.
IF EXISTS(SELECT * FROM sys.service_contracts 
          WHERE name = '//www.microsoft.com/databasemail/contracts/SendMail/v1.0') 
BEGIN
   PRINT 'Dropping CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]'          
   DROP CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0];
END

-- Drop SendMail message type if existing.
IF EXISTS(SELECT * FROM sys.service_message_types 
          WHERE name = '{//www.microsoft.com/databasemail/messages}SendMail')
BEGIN
   PRINT 'Dropping MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMail]'           
   DROP MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMail];
END   

-- Drop SendMailStatus message type if existing.
IF EXISTS(SELECT * FROM sys.service_message_types 
          WHERE name = '{//www.microsoft.com/databasemail/messages}SendMailStatus')
BEGIN
   PRINT 'Dropping MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMailStatus]'           
   DROP MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMailStatus];
END 

GO

-------------------------------------------------------------------
-- Create Database Mail MESSAGES, CONTRACTS, QUEUES AND SERVICES 
-------------------------------------------------------------------

PRINT ''
PRINT 'Creating MESSAGES, CONTRACTS, QUEUES AND SERVICES...'
PRINT ''

-- Create SendMail message type.
PRINT 'Creating MESSAGE TYPE [{//www.microsoft.com/databasemail/messages}SendMail]'

CREATE MESSAGE TYPE 
    [{//www.microsoft.com/databasemail/messages}SendMail] 
    VALIDATION = NONE 

CREATE MESSAGE TYPE 
    [{//www.microsoft.com/databasemail/messages}SendMailStatus]
    VALIDATION = NONE 

-- Create SendMail contract.
PRINT 'Creating CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]'

CREATE CONTRACT [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]
(
    [{//www.microsoft.com/databasemail/messages}SendMail]          SENT BY INITIATOR,
    [{//www.microsoft.com/databasemail/messages}SendMailStatus]    SENT BY TARGET
)

-- Create InternalMailQueue queue.
PRINT 'Creating QUEUE InternalMailQueue'

CREATE QUEUE InternalMailQueue
   WITH ACTIVATION (PROCEDURE_NAME = sp_ExternalMailQueueListener,                  
                    MAX_QUEUE_READERS = 1, 
                    EXECUTE AS SELF);

-- Create ExternalMailQueue queue.
PRINT 'Creating QUEUE ExternalMailQueue'

CREATE QUEUE ExternalMailQueue
   WITH ACTIVATION (PROCEDURE_NAME = sp_sysmail_activate,                  
                    MAX_QUEUE_READERS = 1, 
                    EXECUTE AS SELF);

-- Create InternalMailService service.
PRINT 'Creating SERVICE InternalMailService ON QUEUE InternalMailQueue'

CREATE SERVICE InternalMailService ON QUEUE InternalMailQueue
(
  [//www.microsoft.com/databasemail/contracts/SendMail/v1.0] 
 -- ,[//www.microsoft.com/databasemail/contracts/TestProfile/v1.0]
);

-- Create ExternalMailService service.
PRINT 'Creating SERVICE ExternalMailService ON QUEUE ExternalMailQueue'

CREATE SERVICE ExternalMailService ON QUEUE ExternalMailQueue
(
  [//www.microsoft.com/databasemail/contracts/SendMail/v1.0]
 -- ,[//www.microsoft.com/databasemail/contracts/TestProfile/v1.0]
);

GO

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


/**************************************************************/
/* sp_maintplan_delete_log                                    */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_delete_log...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_delete_log')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_delete_log
go
CREATE PROCEDURE sp_maintplan_delete_log
    @plan_id        UNIQUEIDENTIFIER    = NULL,
    @subplan_id     UNIQUEIDENTIFIER    = NULL,
    @oldest_time    DATETIME            = NULL
AS
BEGIN
    -- @plan_id and @subplan_id must be both NULL or only one exclusively set
   IF (@plan_id IS NOT NULL) AND (@subplan_id IS NOT NULL)
   BEGIN
      RAISERROR(12980, -1, -1, '@plan_id', '@subplan_id')
      RETURN(1)
   END

   --Scenario 1: User wants to delete all logs
   --Scenario 2: User wants to delete all logs older than X date
   --Scenario 3: User wants to delete all logs for a given plan
   --Scenario 4: User wants to delete all logs for a specific subplan
   --Scenario 5: User wants to delete all logs for a given plan older than X date
   --Scenario 6: User wants to delete all logs for a specific subplan older than X date

   -- Special case 1: Delete all logs
   IF (@plan_id IS NULL) AND (@subplan_id IS NULL) AND (@oldest_time IS NULL)
   BEGIN
      DELETE msdb.dbo.sysmaintplan_logdetail
      DELETE msdb.dbo.sysmaintplan_log
      RETURN (0)
   END

   DELETE msdb.dbo.sysmaintplan_log 
    WHERE ( task_detail_id in 
            (SELECT task_detail_id 
             FROM msdb.dbo.sysmaintplan_log 
             WHERE ((@plan_id IS NULL)     OR (plan_id = @plan_id)) AND 
                   ((@subplan_id IS NULL)  OR (subplan_id = @subplan_id)) AND 
                   ((@oldest_time IS NULL) OR (start_time < @oldest_time))) )

    RETURN (0)
END
GO


/**************************************************************/
/* sp_maintplan_delete_subplan                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_delete_subplan...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_delete_subplan')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_delete_subplan
go
CREATE PROCEDURE sp_maintplan_delete_subplan
    @subplan_id       UNIQUEIDENTIFIER,
    @delete_jobs BIT                   = 1
AS
BEGIN

    DECLARE @retval     INT
    DECLARE @job        UNIQUEIDENTIFIER
    DECLARE @jobMsx     UNIQUEIDENTIFIER

    SET NOCOUNT ON
    SET @retval = 0

    -- Raise an error if the @subplan_id doesn't exist
    IF( NOT EXISTS(SELECT * FROM sysmaintplan_subplans WHERE subplan_id = @subplan_id))
    BEGIN
        DECLARE @subplan_id_as_char VARCHAR(36)
        SELECT @subplan_id_as_char = CONVERT(VARCHAR(36), @subplan_id)
        RAISERROR(14262, -1, -1, '@subplan_id', @subplan_id_as_char)
        RETURN(1)
    END


    BEGIN TRAN

    --Is there an Agent Job/Schedule associated with this subplan?
    SELECT @job = job_id, @jobMsx = msx_job_id
    FROM msdb.dbo.sysmaintplan_subplans 
    WHERE subplan_id = @subplan_id

    EXEC @retval = msdb.dbo.sp_maintplan_delete_log @subplan_id = @subplan_id
    IF (@retval <> 0)
    BEGIN
        ROLLBACK TRAN
        RETURN @retval
    END

    -- Delete the subplans table entry first since it has a foreign
    -- key constraint on its job_id existing in sysjobs.
    DELETE msdb.dbo.sysmaintplan_subplans 
    WHERE (subplan_id = @subplan_id)

    IF (@delete_jobs = 1)
    BEGIN
        --delete the local job associated with this subplan
        IF (@job IS NOT NULL)
        BEGIN
            EXEC @retval = msdb.dbo.sp_delete_job @job_id = @job, @delete_unused_schedule = 1
            IF (@retval <> 0)
            BEGIN
                ROLLBACK TRAN
                RETURN @retval
            END
        END

        --delete the multi-server job associated with this subplan.
        IF (@jobMsx IS NOT NULL)
        BEGIN 
            EXEC @retval = msdb.dbo.sp_delete_job @job_id = @jobMsx, @delete_unused_schedule = 1
            IF (@retval <> 0)
            BEGIN
                ROLLBACK TRAN
                RETURN @retval
            END
        END
    END

    COMMIT TRAN
    RETURN (0)
END
go

/**************************************************************/
/* SP_MAINTPLAN_UPDATE_SUBPLAN_TSX                            */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_update_subplan_tsx...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_update_subplan_tsx')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_update_subplan_tsx
go
-- This procedure is called when a maintenance plan subplan record
-- needs to be created or updated to match a multi-server Agent job
-- that has arrived from the master server.
CREATE PROCEDURE sp_maintplan_update_subplan_tsx
    @subplan_id    UNIQUEIDENTIFIER,
    @plan_id       UNIQUEIDENTIFIER,
    @name          sysname,
    @description   NVARCHAR(512),
    @job_id        UNIQUEIDENTIFIER
AS
BEGIN
    -- Find out what schedule, if any, is associated with the job.
    declare @schedule_id int
    select @schedule_id = (SELECT TOP(1) schedule_id
                           FROM msdb.dbo.sysjobschedules
                           WHERE (job_id = @job_id) )

    exec sp_maintplan_update_subplan @subplan_id, @plan_id, @name, @description, @job_id, @schedule_id, @allow_create=1

    -- Be sure to mark this subplan as coming from the master, not locally.
    update sysmaintplan_subplans
    set msx_plan = 1
    where subplan_id = @subplan_id
    
END
go

/**************************************************************/
/* SP_MAINTPLAN_SUBPLANS_BY_JOB                               */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_subplans_by_job...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_subplans_by_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_subplans_by_job
go
-- If the given job_id is associated with a maintenance plan,
-- then matching entries from sysmaintplan_subplans are returned.
CREATE PROCEDURE sp_maintplan_subplans_by_job
    @job_id  UNIQUEIDENTIFIER
AS
BEGIN
    select plans.name as 'plan_name', plans.id as 'plan_id', subplans.subplan_name, subplans.subplan_id
    from sysmaintplan_plans plans, sysmaintplan_subplans subplans
    where  plans.id = subplans.plan_id
    and (job_id = @job_id
         or msx_job_id = @job_id)
    order by subplans.plan_id, subplans.subplan_id
END
go


/**************************************************************/
/* sp_maintplan_open_logentry                                 */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_open_logentry...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_open_logentry')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_open_logentry
go
CREATE PROCEDURE sp_maintplan_open_logentry
    @plan_id       UNIQUEIDENTIFIER,
    @subplan_id       UNIQUEIDENTIFIER,   
    @start_time       DATETIME            = NULL,
    @task_detail_id  UNIQUEIDENTIFIER    = NULL OUTPUT
AS
BEGIN

   --Set defaults
   IF (@start_time IS NULL)
   BEGIN
      SELECT @start_time = GETDATE()
   END

   SELECT @task_detail_id = NEWID()

   --Insert a new record into sysmaintplan_log table
   INSERT INTO msdb.dbo.sysmaintplan_log(task_detail_id, plan_id, subplan_id, start_time)
    VALUES(@task_detail_id, @plan_id, @subplan_id, @start_time)

   RETURN (@@ERROR)
END
GO


/**************************************************************/
/* sp_maintplan_close_logentry                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_close_logentry...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_close_logentry')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_close_logentry
go
CREATE PROCEDURE sp_maintplan_close_logentry
    @task_detail_id     UNIQUEIDENTIFIER,
    @end_time          DATETIME            = NULL,
    @succeeded         TINYINT
AS
BEGIN

   --Set defaults
   IF (@end_time IS NULL)
   BEGIN
      SELECT @end_time = GETDATE()
   END

    -- Raise an error if the @task_detail_id doesn't exist
    IF( NOT EXISTS(SELECT * FROM sysmaintplan_log WHERE (task_detail_id = @task_detail_id)))
   BEGIN
        DECLARE @task_detail_id_as_char VARCHAR(36)
        SELECT @task_detail_id_as_char = CONVERT(VARCHAR(36), @task_detail_id)
        RAISERROR(14262, -1, -1, '@task_detail_id', @task_detail_id_as_char)
      RETURN(1)
   END

   UPDATE msdb.dbo.sysmaintplan_log 
    SET end_time = @end_time, succeeded = @succeeded 
    WHERE (task_detail_id = @task_detail_id)

    RETURN (@@ERROR)
END
go



/**************************************************************/
/* sp_maintplan_update_log                                    */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_update_log...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_update_log')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_update_log
go
CREATE PROCEDURE sp_maintplan_update_log
    --Updates the log_details table
    @task_detail_id      UNIQUEIDENTIFIER,       --Required
    @Line1              NVARCHAR(256),       --Required
    @Line2              NVARCHAR(256)   = NULL,
    @Line3              NVARCHAR(256)   = NULL,
    @Line4              NVARCHAR(256)   = NULL,
    @Line5              NVARCHAR(256)   = NULL,
    @server_name      sysname,            --Required
    @succeeded         TINYINT,           --Required
    @start_time           DATETIME,          --Required
    @end_time          DATETIME,          --Required
    @error_number     int=NULL,
    @error_message       NVARCHAR(max)   = NULL,
    @command           NVARCHAR(max)   = NULL
AS
BEGIN

   --Prep strings
   SET NOCOUNT ON
   SELECT @Line1 = LTRIM(RTRIM(@Line1))
   SELECT @Line2 = LTRIM(RTRIM(@Line2))
   SELECT @Line3 = LTRIM(RTRIM(@Line3))
   SELECT @Line4 = LTRIM(RTRIM(@Line4))
   SELECT @Line5 = LTRIM(RTRIM(@Line5))

   INSERT INTO msdb.dbo.sysmaintplan_logdetail(
        task_detail_id, 
        line1,
        line2, 
        line3, 
        line4, 
        line5, 
        server_name, 
        start_time, 
        end_time, 
        error_number, 
        error_message, 
        command, 
        succeeded)
   VALUES(
        @task_detail_id,
        @Line1,
        @Line2,
        @Line3,
        @Line4,
        @Line5,
        @server_name,
        @start_time,
        @end_time,
        @error_number,
        @error_message,
        @command,
        @succeeded)

    RETURN (@@ERROR)
END
GO



/**************************************************************/
/* sp_maintplan_update_subplan                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_update_subplan...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_update_subplan')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_update_subplan
go
CREATE PROCEDURE sp_maintplan_update_subplan
    @subplan_id       UNIQUEIDENTIFIER,
    @plan_id       UNIQUEIDENTIFIER    = NULL,
    @name          sysname             = NULL,
    @description  NVARCHAR(512)       = NULL,
    @job_id        UNIQUEIDENTIFIER    = NULL,
    @schedule_id  INT                 = NULL,
    @allow_create   BIT                 = 0,
    @msx_job_id    UNIQUEIDENTIFIER    = NULL
AS
BEGIN

   SET NOCOUNT ON

   SELECT @name = LTRIM(RTRIM(@name))
   SELECT @description = LTRIM(RTRIM(@description))

   --Are we creating a new entry or updating an existing one?

   IF( NOT EXISTS(SELECT * FROM msdb.dbo.sysmaintplan_subplans WHERE subplan_id = @subplan_id) )
   BEGIN
        -- Only allow creation of a record if user permits it
        IF(@allow_create = 0)
        BEGIN
            DECLARE @subplan_id_as_char VARCHAR(36)
            SELECT @subplan_id_as_char = CONVERT(VARCHAR(36), @subplan_id)
            RAISERROR(14262, -1, -1, '@subplan_id', @subplan_id_as_char)
          RETURN(1)
        END

        --Insert it's a new subplan
      IF (@name IS NULL)
      BEGIN
          RAISERROR(12981, -1, -1, '@name')
         RETURN(1) -- Failure
      END

      IF (@plan_id IS NULL)
      BEGIN
          RAISERROR(12981, -1, -1, '@plan_id')
         RETURN(1) -- Failure
      END

      INSERT INTO msdb.dbo.sysmaintplan_subplans(
            subplan_id,
            plan_id,
            subplan_description,
            subplan_name,
            job_id,
            schedule_id,
            msx_job_id)
      VALUES(
            @subplan_id,
            @plan_id,
            @description,
            @name,
            @job_id,
            @schedule_id,
            @msx_job_id)

   END
   ELSE
   BEGIN --Update the table

      DECLARE @s_subplan_name sysname
      DECLARE @s_job_id UNIQUEIDENTIFIER

      SELECT @s_subplan_name         = subplan_name,
            @s_job_id               = job_id
      FROM msdb.dbo.sysmaintplan_subplans
      WHERE (@subplan_id = subplan_id)

      --Determine if user wants to change these variables
      IF (@name IS NOT NULL)          SELECT @s_subplan_name          = @name
      IF (@job_id IS NOT NULL)        SELECT @s_job_id                = @job_id

      --UPDATE the record

      UPDATE msdb.dbo.sysmaintplan_subplans 
        SET subplan_name        = @s_subplan_name,
            subplan_description = @description,
            job_id              = @s_job_id,
            schedule_id         = @schedule_id,
            msx_job_id          = @msx_job_id
      WHERE (subplan_id = @subplan_id)

   END

    RETURN (@@ERROR)
END
GO


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

   DECLARE @sp_id UNIQUEIDENTIFIER
    DECLARE @retval     INT

    SET @retval = 0

   --Loop through Subplans
   DECLARE sp CURSOR LOCAL FOR 
        SELECT subplan_id 
        FROM msdb.dbo.sysmaintplan_subplans 
        WHERE plan_id = @plan_id FOR READ ONLY

   OPEN sp
   FETCH NEXT FROM sp INTO @sp_id
   WHILE @@FETCH_STATUS = 0
   BEGIN 
     EXECUTE @retval = sp_maintplan_delete_subplan @subplan_id = @sp_id
      IF(@retval <> 0)
        BREAK

     FETCH NEXT FROM sp INTO @sp_id
   END
   CLOSE sp
   DEALLOCATE sp

    RETURN (@retval)
END
go



/**************************************************************/
/* sp_maintplan_start                                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_maintplan_start...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_maintplan_start')
              AND (type = 'P')))
  DROP PROCEDURE sp_maintplan_start
go
CREATE PROCEDURE sp_maintplan_start
    @plan_id        UNIQUEIDENTIFIER    = NULL,
    @subplan_id     UNIQUEIDENTIFIER    = NULL
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @jobid  UNIQUEIDENTIFIER
    DECLARE @retval INT
    SET @retval = 0

    -- A @plan_id or @subplan_id must be supplied
   IF (@plan_id IS NULL) AND (@subplan_id IS NULL)
   BEGIN
      RAISERROR(12982, -1, -1, '@plan_id', '@subplan_id')
      RETURN(1)
   END

    -- either @plan_id or @subplan_id must be exclusively set
   IF (@plan_id IS NOT NULL) AND (@subplan_id IS NOT NULL)
   BEGIN
      RAISERROR(12982, -1, -1, '@plan_id', '@subplan_id')
      RETURN(1)
   END

    IF (@subplan_id IS NOT NULL)
    BEGIN 
        -- subplan_id supplied so simply start the subplan's job

        SELECT @jobid = job_id 
        FROM msdb.dbo.sysmaintplan_subplans 
        WHERE subplan_id = @subplan_id 

        if(@jobid IS NOT NULL)
        BEGIN
            EXEC @retval = msdb.dbo.sp_start_job @job_id = @jobid
        END

    END
    ELSE
    BEGIN
        -- Loop through Subplans and fire off all associated jobs
       DECLARE spj CURSOR LOCAL FOR 
            SELECT job_id
            FROM msdb.dbo.sysmaintplan_subplans 
            WHERE plan_id = @plan_id FOR READ ONLY

       OPEN spj
       FETCH NEXT FROM spj INTO @jobid
       WHILE (@@FETCH_STATUS = 0)
       BEGIN 
           EXEC @retval = msdb.dbo.sp_start_job @job_id = @jobid
            IF(@retval <> 0)
                BREAK

           FETCH NEXT FROM spj INTO @jobid
       END

       CLOSE spj
       DEALLOCATE spj

    END

    RETURN (@retval)
END
GO

/**************************************************************/
/* sp_get_script                                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_get_script...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_get_script')
              AND (type = 'P')))
  DROP PROCEDURE sp_get_script
go
CREATE PROCEDURE sp_get_script
    @name sysname
AS
BEGIN
    exec master.dbo.xp_get_script @name
END
GO


/*==================================================================*/
--TODO: The following SYSDBMAINT... tables and SP's will be removed  
/*==================================================================*/


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

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

