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

  CREATE TABLE sysdbmaintplans
  (
  plan_id                    UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
  plan_name                  sysname          NOT NULL,
  date_created               DATETIME         NOT NULL DEFAULT (GETDATE()),
  owner                      sysname          NOT NULL DEFAULT (ISNULL(NT_CLIENT(), SUSER_SNAME())),
  max_history_rows           INT              NOT NULL DEFAULT (0),
  remote_history_server      sysname          NOT NULL DEFAULT (''),
  max_remote_history_rows    INT              NOT NULL DEFAULT (0),
  user_defined_1             INT              NULL,
  user_defined_2             NVARCHAR(100)    NULL,
  user_defined_3             DATETIME         NULL,
  user_defined_4             UNIQUEIDENTIFIER NULL
  )
END
go

-- Add row for "plan 0"
IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysdbmaintplans
                WHERE (plan_id = CONVERT(UNIQUEIDENTIFIER, 0x00))))
  INSERT INTO sysdbmaintplans(plan_id, plan_name, owner) VALUES (0x00, N'All ad-hoc plans', N'sa')
go

/**************************************************************/
/* SYSDBMAINTPLAN_JOBS                                        */
/**************************************************************/

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

  CREATE TABLE sysdbmaintplan_jobs
  (
  plan_id UNIQUEIDENTIFIER NOT NULL UNIQUE CLUSTERED (plan_id, job_id)
                                    FOREIGN KEY REFERENCES msdb.dbo.sysdbmaintplans (plan_id),
  job_id  UNIQUEIDENTIFIER NOT NULL
  )
END
go

/**************************************************************/
/* SYSDBMAINTPLAN_DATABASES                                   */
/**************************************************************/

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

  CREATE TABLE sysdbmaintplan_databases
  (
  plan_id       UNIQUEIDENTIFIER NOT NULL UNIQUE CLUSTERED (plan_id, database_name)
                                          FOREIGN KEY REFERENCES msdb.dbo.sysdbmaintplans (plan_id),
  database_name sysname          NOT NULL
  )
END
go

/**************************************************************/
/* SYSDBMAINTPLAN_HISTORY                                     */
/**************************************************************/

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

  CREATE TABLE sysdbmaintplan_history
  (
  sequence_id    INT               NOT NULL IDENTITY UNIQUE NONCLUSTERED,
  plan_id        UNIQUEIDENTIFIER  NOT NULL DEFAULT('00000000-0000-0000-0000-000000000000'),
  plan_name      sysname           NOT NULL DEFAULT('All ad-hoc plans'),
  database_name  sysname           NULL,
  server_name    sysname           NOT NULL DEFAULT (CONVERT(sysname, ServerProperty('ServerName'))),
  activity       NVARCHAR(128)     NULL,
  succeeded      BIT               NOT NULL DEFAULT (1),
  end_time       DATETIME          NOT NULL DEFAULT (GETDATE()),
  duration       INT               NULL     DEFAULT (0),
  start_time     AS                DATEADD (ss, -duration, end_time),
  error_number   INT               NOT NULL DEFAULT (0),
  message        NVARCHAR(512)     NULL
  )

  CREATE CLUSTERED INDEX clust ON sysdbmaintplan_history(plan_id)
END
-- ALTER TABLE to correct default constraint 
ELSE
BEGIN
  DECLARE @t TABLE
  (
  constraint_type         NVARCHAR(146)  COLLATE database_default NULL,
  constraint_name         sysname        COLLATE database_default NULL,
  delete_action           NVARCHAR(20)   COLLATE database_default NULL,
  update_action           NVARCHAR(20)   COLLATE database_default NULL,
  status_enabled          NVARCHAR(20)   COLLATE database_default NULL,
  status_for_replication  NVARCHAR(20)   COLLATE database_default NULL,
  constraint_keys         NVARCHAR(2126) COLLATE database_default NULL
  )

  INSERT INTO @t EXEC sp_helpconstraint N'sysdbmaintplan_history', 'nomsg'

  DECLARE @constraint_name sysname
  DECLARE @sql NVARCHAR(4000)

  SELECT @constraint_name = constraint_name 
  FROM   @t 
  WHERE  constraint_type = N'DEFAULT on column server_name' 
  AND    constraint_keys = N'(@@servername)'

  -- default found
  IF (@constraint_name IS NOT NULL)
  BEGIN
    PRINT ''
    PRINT 'Alter sysdbmaintplan_history ...'
    SELECT @sql = N'ALTER TABLE sysdbmaintplan_history DROP CONSTRAINT ' + @constraint_name
    EXEC (@sql)

    ALTER TABLE sysdbmaintplan_history 
      ADD CONSTRAINT servername_default DEFAULT (CONVERT(sysname, ServerProperty('ServerName')))
      FOR server_name
  END
END
go

/**************************************************************/
/* SPs for the maintenance plans                              */
/**************************************************************/
/**************************************************************/
/* sp_clear_dbmaintplan_by_db                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_clear_dbmaintplan_by_db...'
GO
IF (EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sp_clear_dbmaintplan_by_db') AND (type = 'P')))
  DROP PROCEDURE sp_clear_dbmaintplan_by_db
GO
CREATE PROCEDURE sp_clear_dbmaintplan_by_db
  @db_name sysname
AS
BEGIN
  DECLARE planid_cursor CURSOR
  FOR
  select plan_id from msdb.dbo.sysdbmaintplan_databases where database_name=@db_name
  OPEN planid_cursor
  declare @planid uniqueidentifier
  FETCH NEXT FROM planid_cursor INTO @planid
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
      delete from msdb.dbo.sysdbmaintplan_databases where plan_id=@planid AND database_name=@db_name
      if (NOT EXISTS(select * from msdb.dbo.sysdbmaintplan_databases where plan_id=@planid))
      BEGIN
        --delete the job
        DECLARE jobid_cursor CURSOR
        FOR
        select job_id from msdb.dbo.sysdbmaintplan_jobs where plan_id=@planid
        OPEN jobid_cursor
        DECLARE @jobid uniqueidentifier
        FETCH NEXT FROM jobid_cursor INTO @jobid
        WHILE (@@FETCH_STATUS <> -1)
        BEGIN
          if (@@FETCH_STATUS <> -2)
          BEGIN
            execute msdb.dbo.sp_delete_job @jobid
          END
          FETCH NEXT FROM jobid_cursor into @jobid
        END
        CLOSE jobid_cursor
        DEALLOCATE jobid_cursor
        --delete the history
        delete from msdb.dbo.sysdbmaintplan_history where plan_id=@planid
        --delete the plan
        delete from msdb.dbo.sysdbmaintplans where plan_id=@planid
      END
    END
    FETCH NEXT FROM planid_cursor INTO @planid
  END
  CLOSE planid_cursor
  DEALLOCATE planid_cursor
END
GO

/**************************************************************/
/* sp_add_maintenance_plan                                    */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_add_maintenance_plan...'
GO
IF (EXISTS (SELECT *
                FROM msdb.dbo.sysobjects
                WHERE (name = N'sp_add_maintenance_plan') AND (type = 'P')))
  DROP PROCEDURE sp_add_maintenance_plan
GO
CREATE PROCEDURE sp_add_maintenance_plan
  @plan_name varchar(128),
  @plan_id   UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
  IF (NOT EXISTS (SELECT *
                FROM msdb.dbo.sysdbmaintplans
                WHERE plan_name=@plan_name))
    BEGIN
      SELECT @plan_id=NEWID()
      INSERT INTO msdb.dbo.sysdbmaintplans (plan_id, plan_name) VALUES (@plan_id, @plan_name)
    END
  ELSE
    BEGIN
      RAISERROR(14261,-1,-1,'@plan_name',@plan_name)
      RETURN(1) -- failure
    END
END
GO

/**************************************************************/
/* sp_delete_maintenance_plan                                 */
/**************************************************************/

PRINT ''
PRINT 'Creating procedure sp_delete_maintenance_plan...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_maintenance_plan')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_maintenance_plan
GO
CREATE PROCEDURE sp_delete_maintenance_plan
  @plan_id UNIQUEIDENTIFIER
AS
BEGIN
  /*check if the plan_id is valid*/
  IF (NOT EXISTS(SELECT *
                 FROM sysdbmaintplans
                 WHERE plan_id=@plan_id))
  BEGIN
    DECLARE @syserr VARCHAR(100)
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)
    RAISERROR(14262,-1,-1,'@plan_id',@syserr)
    RETURN(1)
  END
  /* clean the related records in sysdbmaintplan_database */
  DELETE FROM msdb.dbo.sysdbmaintplan_databases
  WHERE plan_id=@plan_id
  /* clean the related records in sysdbmaintplan_jobs*/
  DELETE FROM msdb.dbo.sysdbmaintplan_jobs
  WHERE plan_id=@plan_id
  /* clean sysdbmaintplans */
  DELETE FROM msdb.dbo.sysdbmaintplans
  WHERE  plan_id= @plan_id
END
GO

/**************************************************************/
/* sp_add_maintenance_plan_db                                 */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_maintenance_plan_db...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_add_maintenance_plan_db')
              AND (type = 'P')))
  DROP PROCEDURE sp_add_maintenance_plan_db
GO
CREATE PROCEDURE sp_add_maintenance_plan_db
  @plan_id UNIQUEIDENTIFIER,
  @db_name sysname
AS
BEGIN
  DECLARE @syserr VARCHAR(100)
  /*check if the plan_id is valid */
  IF (NOT EXISTS (SELECT plan_id
              FROM  msdb.dbo.sysdbmaintplans
              WHERE plan_id=@plan_id))
  BEGIN
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)
    RAISERROR(14262,-1,-1,'@plan_id',@syserr)
    RETURN(1)
  END
  /*check if the database name is valid */
  IF (NOT EXISTS (SELECT name
              FROM master.dbo.sysdatabases
              WHERE name=@db_name))
   BEGIN
    RAISERROR(14262,-1,-1,'@db_name',@db_name)
    RETURN(1)
  END
  /*check if the (plan_id, database) pair already exists*/
  IF (EXISTS (SELECT *
              FROM sysdbmaintplan_databases
              WHERE plan_id=@plan_id AND database_name=@db_name))
  BEGIN
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)+' + '+@db_name
    RAISERROR(14261,-1,-1,'@plan_id+@db_name',@syserr)
    RETURN(1)
  END
  INSERT INTO msdb.dbo.sysdbmaintplan_databases (plan_id,database_name) VALUES (@plan_id, @db_name)
END
GO

/**************************************************************/
/* sp_delete_maintenance_plan_db                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_maintenance_plan_db...'
go
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_maintenance_plan_db')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_maintenance_plan_db
go
CREATE PROCEDURE sp_delete_maintenance_plan_db
  @plan_id uniqueidentifier,
  @db_name sysname
AS
BEGIN
  /*check if the (plan_id, db_name) exists in the table*/
  IF (NOT EXISTS(SELECT *
                 FROM msdb.dbo.sysdbmaintplan_databases
                 WHERE @plan_id=plan_id AND @db_name=database_name))
  BEGIN
    DECLARE @syserr VARCHAR(300)
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)+' + '+@db_name
    RAISERROR(14262,-1,-1,'@plan_id+@db_name',@syserr)
    RETURN(1)
  END
  /*delete the pair*/
  DELETE FROM msdb.dbo.sysdbmaintplan_databases
  WHERE plan_id=@plan_id AND database_name=@db_name
END
GO

/**************************************************************/
/* sp_add_maintenance_plan_job                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_maintenance_plan_job...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_add_maintenance_plan_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_add_maintenance_plan_job
GO
CREATE PROCEDURE sp_add_maintenance_plan_job
  @plan_id UNIQUEIDENTIFIER,
  @job_id  UNIQUEIDENTIFIER
AS
BEGIN
  DECLARE @syserr varchar(100)
  /*check if the @plan_id is valid*/
  IF (NOT EXISTS(SELECT plan_id
                 FROM msdb.dbo.sysdbmaintplans
                 WHERE plan_id=@plan_id))
  BEGIN
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)
    RAISERROR(14262,-1,-1,'@plan_id',@syserr)
    RETURN(1)
  END
  /*check if the @job_id is valid*/
  IF (NOT EXISTS(SELECT job_id
                 FROM msdb.dbo.sysjobs
                 WHERE job_id=@job_id))
  BEGIN
    SELECT @syserr=CONVERT(VARCHAR(100),@job_id)
    RAISERROR(14262,-1,-1,'@job_id',@syserr)
    RETURN(1)
  END
  /*check if the job has at least one step calling xp_sqlmaint*/
  DECLARE @maxind INT
  SELECT @maxind=(SELECT MAX(CHARINDEX('xp_sqlmaint', command))
                FROM  msdb.dbo.sysjobsteps
                WHERE @job_id=job_id)
  IF (@maxind<=0)
  BEGIN
    /*print N'Warning: The job is not for maitenance plan.' -- will add the new sysmessage here*/
    SELECT @syserr=CONVERT(VARCHAR(100),@job_id)
    RAISERROR(14199,-1,-1,@syserr)
    RETURN(1)
  END
  INSERT INTO msdb.dbo.sysdbmaintplan_jobs(plan_id,job_id) VALUES (@plan_id, @job_id) --don't have to check duplicate here
END
GO

/**************************************************************/
/* sp_delete_maintenance_plan_job                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_maintenance_plan_job...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_delete_maintenance_plan_job')
              AND (type = 'P')))
  DROP PROCEDURE sp_delete_maintenance_plan_job
GO
CREATE PROCEDURE sp_delete_maintenance_plan_job
  @plan_id uniqueidentifier,
  @job_id  uniqueidentifier
AS
BEGIN
  /*check if the (plan_id, job_id) exists*/
  IF (NOT EXISTS(SELECT *
                 FROM sysdbmaintplan_jobs
                 WHERE @plan_id=plan_id AND @job_id=job_id))
  BEGIN
    DECLARE @syserr VARCHAR(300)
    SELECT @syserr=CONVERT(VARCHAR(100),@plan_id)+' + '+CONVERT(VARCHAR(100),@job_id)
    RAISERROR(14262,-1,-1,'@plan_id+@job_id',@syserr)
    RETURN(1)
  END
  DELETE FROM msdb.dbo.sysdbmaintplan_jobs
  WHERE plan_id=@plan_id AND job_id=@job_id
END
GO

/**************************************************************/
/* sp_help_maintenance_plan                                   */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_help_maintenance_plan...'
GO
IF (EXISTS (SELECT *
            FROM msdb.dbo.sysobjects
            WHERE (name = N'sp_help_maintenance_plan')
              AND (type = 'P')))
  DROP PROCEDURE sp_help_maintenance_plan
GO
CREATE PROCEDURE sp_help_maintenance_plan
  @plan_id UNIQUEIDENTIFIER = NULL
AS
BEGIN
  IF (@plan_id IS NOT NULL)
    BEGIN
      /*return the information about the plan itself*/
      SELECT *
      FROM msdb.dbo.sysdbmaintplans
      WHERE plan_id=@plan_id
      /*return the information about databases this plan defined on*/
      SELECT database_name
      FROM msdb.dbo.sysdbmaintplan_databases
      WHERE plan_id=@plan_id
      /*return the information about the jobs that relating to the plan*/
      SELECT job_id
      FROM msdb.dbo.sysdbmaintplan_jobs
      WHERE plan_id=@plan_id
    END
  ELSE
    BEGIN
      SELECT *
      FROM msdb.dbo.sysdbmaintplans
    END
END
GO

/**************************************************************/
/*                                                            */
/* B A C K U P  H I S T O R Y                                 */
/*                                                            */
/**************************************************************/
/**************************************************************/
/* sp_delete_database_backuphistory                           */
/**************************************************************/

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

  DECLARE @backup_set_id TABLE      (backup_set_id INT)
  DECLARE @media_set_id TABLE       (media_set_id INT)
  DECLARE @restore_history_id TABLE (restore_history_id INT)

  INSERT INTO @backup_set_id (backup_set_id)
  SELECT DISTINCT backup_set_id
  FROM msdb.dbo.backupset
  WHERE database_name = @database_name

  INSERT INTO @media_set_id (media_set_id)
  SELECT DISTINCT media_set_id
  FROM msdb.dbo.backupset
  WHERE database_name = @database_name

  INSERT INTO @restore_history_id (restore_history_id)
  SELECT DISTINCT restore_history_id
  FROM msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)

  BEGIN TRANSACTION

  DELETE FROM msdb.dbo.backupfile
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.backupfilegroup
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorefile
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorefilegroup
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorehistory
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.backupset
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE msdb.dbo.backupmediafamily
  FROM msdb.dbo.backupmediafamily bmf
  WHERE bmf.media_set_id IN (SELECT media_set_id
                             FROM @media_set_id)
    AND ((SELECT COUNT(*)
          FROM msdb.dbo.backupset
          WHERE media_set_id = bmf.media_set_id) = 0)
  IF (@@error > 0)
    GOTO Quit

  DELETE msdb.dbo.backupmediaset
  FROM msdb.dbo.backupmediaset bms
  WHERE bms.media_set_id IN (SELECT media_set_id
                             FROM @media_set_id)
    AND ((SELECT COUNT(*)
          FROM msdb.dbo.backupset
          WHERE media_set_id = bms.media_set_id) = 0)
  IF (@@error > 0)
    GOTO Quit

  COMMIT TRANSACTION
  RETURN

Quit:
  ROLLBACK TRANSACTION
END
go

/**************************************************************/
/* SP_DELETE_BACKUPHISTORY                                    */
/**************************************************************/

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

  DECLARE @backup_set_id TABLE      (backup_set_id INT)
  DECLARE @media_set_id TABLE       (media_set_id INT)
  DECLARE @restore_history_id TABLE (restore_history_id INT)

  INSERT INTO @backup_set_id (backup_set_id)
  SELECT DISTINCT backup_set_id
  FROM msdb.dbo.backupset
  WHERE backup_finish_date < @oldest_date

  INSERT INTO @media_set_id (media_set_id)
  SELECT DISTINCT media_set_id
  FROM msdb.dbo.backupset
  WHERE backup_finish_date < @oldest_date

  INSERT INTO @restore_history_id (restore_history_id)
  SELECT DISTINCT restore_history_id
  FROM msdb.dbo.restorehistory
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)

  BEGIN TRANSACTION

  DELETE FROM msdb.dbo.backupfile
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.backupfilegroup
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorefile
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorefilegroup
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.restorehistory
  WHERE restore_history_id IN (SELECT restore_history_id
                               FROM @restore_history_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE FROM msdb.dbo.backupset
  WHERE backup_set_id IN (SELECT backup_set_id
                          FROM @backup_set_id)
  IF (@@error > 0)
    GOTO Quit

  DELETE msdb.dbo.backupmediafamily
  FROM msdb.dbo.backupmediafamily bmf
  WHERE bmf.media_set_id IN (SELECT media_set_id
                             FROM @media_set_id)
    AND ((SELECT COUNT(*)
          FROM msdb.dbo.backupset
          WHERE media_set_id = bmf.media_set_id) = 0)
  IF (@@error > 0)
    GOTO Quit

  DELETE msdb.dbo.backupmediaset
  FROM msdb.dbo.backupmediaset bms
  WHERE bms.media_set_id IN (SELECT media_set_id
                             FROM @media_set_id)
    AND ((SELECT COUNT(*)
          FROM msdb.dbo.backupset
          WHERE media_set_id = bms.media_set_id) = 0)
  IF (@@error > 0)
    GOTO Quit

  COMMIT TRANSACTION
  RETURN

Quit:
  ROLLBACK TRANSACTION

END
go




/**********************************************************************/
/* TABLE : log_shipping_primaries                                     */
/* Populated on the monitor server                                    */
/*                                                                    */
/**********************************************************************/

IF (NOT EXISTS (SELECT *
            FROM INFORMATION_SCHEMA.TABLES
            WHERE (TABLE_NAME = N'log_shipping_primaries')))
BEGIN
 PRINT ''
 PRINT 'Creating table log_shipping_primaries...'
 CREATE TABLE log_shipping_primaries
 (
  primary_id                   INT IDENTITY     NOT NULL PRIMARY KEY,
  primary_server_name          sysname          NOT NULL,
  primary_database_name        sysname          NOT NULL,
  maintenance_plan_id          UNIQUEIDENTIFIER NULL,
  backup_threshold             INT              NOT NULL,
  threshold_alert              INT              NOT NULL,
  threshold_alert_enabled      BIT              NOT NULL, /* 1 = enabled, 0 = disabled */
  last_backup_filename         NVARCHAR(500)    NULL,
  last_updated                 DATETIME         NULL,
  planned_outage_start_time    INT              NOT NULL,
  planned_outage_end_time      INT              NOT NULL,
  planned_outage_weekday_mask  INT              NOT NULL,
  source_directory             NVARCHAR(500)    NULL
 )
END
ELSE 
BEGIN
  IF (NOT EXISTS (SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_NAME = N'log_shipping_primaries')
      AND (COLUMN_NAME = N'source_directory')))

  BEGIN
    PRINT ''
    PRINT 'Adding columns to table log_shipping_primaries...'

    ALTER TABLE log_shipping_primaries
     ADD source_directory NVARCHAR(500) NULL

  END
END 
go

/**********************************************************************/
/* TABLE : log_shipping_secondaries                                   */
/* Populated on the monitor server                                    */
/*                                                                    */
/**********************************************************************/

IF (NOT EXISTS (SELECT *
            FROM INFORMATION_SCHEMA.TABLES
            WHERE (TABLE_NAME = N'log_shipping_secondaries')))
BEGIN
 PRINT ''
 PRINT 'Creating table log_shipping_secondaries...'
 CREATE TABLE log_shipping_secondaries
 (
  primary_id                   INT                FOREIGN KEY REFERENCES log_shipping_primaries (primary_id),
  secondary_server_name        sysname,
  secondary_database_name      sysname,
  last_copied_filename         NVARCHAR(500),
  last_loaded_filename         NVARCHAR(500),
  last_copied_last_updated     DATETIME,
  last_loaded_last_updated     DATETIME,
  secondary_plan_id            UNIQUEIDENTIFIER,
  copy_enabled                 BIT,
  load_enabled                 BIT,              /* 1 = load enabled, 0 = load disabled */
  out_of_sync_threshold        INT,
  threshold_alert              INT,
  threshold_alert_enabled      BIT,              /*1 = enabled, 0 = disabled */
  planned_outage_start_time    INT,
  planned_outage_end_time      INT,
  planned_outage_weekday_mask  INT,
  allow_role_change            BIT DEFAULT (0)
 )
END
ELSE 
BEGIN
  IF (NOT EXISTS (SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_NAME = N'log_shipping_secondaries')
      AND (COLUMN_NAME = N'allow_role_change')))

  BEGIN
    PRINT ''
    PRINT 'Adding columns to table log_shipping_secondaries...'

    ALTER TABLE log_shipping_secondaries
     ADD allow_role_change BIT DEFAULT (0)

  END
END 
go

/**************************************************************/
/* sp_add_log_shipping_monitor_jobs                           */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_log_shipping_monitor_jobs...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_monitor_jobs' AND type = N'P')  )
  drop procedure sp_add_log_shipping_monitor_jobs
go
CREATE PROCEDURE sp_add_log_shipping_monitor_jobs AS 
BEGIN
  SET NOCOUNT ON
  BEGIN TRANSACTION
  DECLARE @rv INT
  DECLARE @backup_job_name sysname
  SET @backup_job_name = N'Log Shipping Alert Job - Backup'
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @backup_job_name))
  BEGIN
    EXECUTE @rv = msdb.dbo.sp_add_job @job_name = N'Log Shipping Alert Job - Backup'

    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 

    EXECUTE @rv = msdb.dbo.sp_add_jobstep 
      @job_name = N'Log Shipping Alert Job - Backup', 
      @step_id = 1, 
      @step_name = N'Log Shipping Alert - Backup', 
      @command = N'EXECUTE msdb.dbo.sp_log_shipping_monitor_backup',
      @on_fail_action = 2, 
      @flags = 4, 
      @subsystem = N'TSQL', 
      @on_success_step_id = 0, 
      @on_success_action = 1, 
      @on_fail_step_id = 0
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 

   EXECUTE @rv = msdb.dbo.sp_add_jobschedule 
      @job_name = @backup_job_name, 
      @freq_type = 4, 
      @freq_interval = 1, 
      @freq_subday_type = 0x4, 
      @freq_subday_interval = 1, -- run every minute
      @freq_relative_interval = 0, 
      @name = @backup_job_name
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error

   EXECUTE @rv = msdb.dbo.sp_add_jobserver @job_name = @backup_job_name, @server_name = NULL
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error
  END

  DECLARE @restore_job_name sysname
  SET @restore_job_name = 'Log Shipping Alert Job - Restore'
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @restore_job_name))
  BEGIN
    EXECUTE @rv = msdb.dbo.sp_add_job @job_name = @restore_job_name

    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 

    EXECUTE @rv = msdb.dbo.sp_add_jobstep 
      @job_name = @restore_job_name, 
      @step_id = 1, 
      @step_name = @restore_job_name, 
      @command = N'EXECUTE msdb.dbo.sp_log_shipping_monitor_restore',
      @on_fail_action = 2, 
      @flags = 4, 
      @subsystem = N'TSQL', 
      @on_success_step_id = 0, 
      @on_success_action = 1, 
      @on_fail_step_id = 0
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error 

    EXECUTE @rv = msdb.dbo.sp_add_jobschedule 
      @job_name = @restore_job_name, 
      @freq_type = 4, 
      @freq_interval = 1, 
      @freq_subday_type = 0x4, 
      @freq_subday_interval = 1, -- run every minute
      @freq_relative_interval = 0, 
      @name = @restore_job_name
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error

    EXECUTE @rv = msdb.dbo.sp_add_jobserver @job_name = @restore_job_name, @server_name = NULL
    IF (@@error <> 0 OR @rv <> 0) GOTO rollback_quit -- error
  END
  COMMIT TRANSACTION
  RETURN

rollback_quit:
  ROLLBACK TRANSACTION
END
go

/**************************************************************/
/* sp_add_log_shipping_primary                                */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_log_shipping_primary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_primary' AND type = N'P'))
  drop procedure sp_add_log_shipping_primary
go
CREATE PROCEDURE sp_add_log_shipping_primary
  @primary_server_name         sysname,
  @primary_database_name       sysname,
  @maintenance_plan_id         UNIQUEIDENTIFIER = NULL,
  @backup_threshold            INT              = 60,
  @threshold_alert             INT              = 14420,
  @threshold_alert_enabled     BIT              = 1,
  @planned_outage_start_time   INT              = 0,
  @planned_outage_end_time     INT              = 0,
  @planned_outage_weekday_mask INT              = 0,
  @primary_id              INT = NULL OUTPUT       
AS
BEGIN
  SET NOCOUNT ON
  IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name)
  BEGIN  
    DECLARE @pair_name NVARCHAR 
   SELECT @pair_name = @primary_server_name + N'.' + @primary_database_name
   RAISERROR (14261,16,1, N'primary_server_name.primary_database_name', @pair_name)
    RETURN (1) -- error
  END
  INSERT INTO msdb.dbo.log_shipping_primaries (
    primary_server_name,
    primary_database_name,
    maintenance_plan_id,
    backup_threshold,
    threshold_alert,
    threshold_alert_enabled,
    last_backup_filename,
    last_updated,
    planned_outage_start_time,
    planned_outage_end_time,
    planned_outage_weekday_mask,
    source_directory)  
  VALUES (@primary_server_name,  
    @primary_database_name, 
    @maintenance_plan_id, 
    @backup_threshold,
    @threshold_alert,
    @threshold_alert_enabled,
    N'first_file_000000000000.trn',
    GETDATE (),
    @planned_outage_start_time,
    @planned_outage_end_time,
    @planned_outage_weekday_mask,
    NULL)

  SELECT @primary_id = @@IDENTITY

  EXECUTE msdb.dbo.sp_add_log_shipping_monitor_jobs
END
go

/**************************************************************/
/* sp_add_log_shipping_secondary                              */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_add_log_shipping_secondary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_add_log_shipping_secondary' AND type = N'P'))
  drop procedure sp_add_log_shipping_secondary
go
CREATE PROCEDURE sp_add_log_shipping_secondary
  @primary_id                  INT,
  @secondary_server_name       sysname,
  @secondary_database_name     sysname,
  @secondary_plan_id           UNIQUEIDENTIFIER,
  @copy_enabled                BIT              = 1,
  @load_enabled                BIT              = 1,
  @out_of_sync_threshold       INT              = 60,
  @threshold_alert             INT              = 14421,
  @threshold_alert_enabled     BIT              = 1,
  @planned_outage_start_time   INT              = 0,
  @planned_outage_end_time     INT              = 0,
  @planned_outage_weekday_mask INT              = 0,
  @allow_role_change           BIT              = 0 
AS
BEGIN
  SET NOCOUNT ON
  IF NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries where primary_id = @primary_id)
  BEGIN
    RAISERROR (14262, 16, 1, N'primary_id', N'msdb.dbo.log_shipping_primaries')
    RETURN(1)
  END

  INSERT INTO msdb.dbo.log_shipping_secondaries (
    primary_id,
    secondary_server_name,
    secondary_database_name,
    last_copied_filename,
    last_loaded_filename,
    last_copied_last_updated,
    last_loaded_last_updated,
    secondary_plan_id,
    copy_enabled,
    load_enabled,
    out_of_sync_threshold,
    threshold_alert,
    threshold_alert_enabled,
    planned_outage_start_time,
    planned_outage_end_time,
    planned_outage_weekday_mask,
    allow_role_change)
   VALUES (@primary_id,
    @secondary_server_name,
    @secondary_database_name,
    N'first_file_000000000000.trn',
    N'first_file_000000000000.trn',
    GETDATE (),
    GETDATE (),
    @secondary_plan_id,
    @copy_enabled,
    @load_enabled,
    @out_of_sync_threshold,
    @threshold_alert,
    @threshold_alert_enabled,
    @planned_outage_start_time,
    @planned_outage_end_time,
    @planned_outage_weekday_mask,
    @allow_role_change)
END
go

/**************************************************************/
/* sp_delete_log_shipping_monitor_jobs                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_monitor_jobs...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_monitor_jobs' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_monitor_jobs
go
CREATE PROCEDURE sp_delete_log_shipping_monitor_jobs AS
BEGIN
  DECLARE @backup_job_name sysname
  SET NOCOUNT ON
  SET @backup_job_name = N'Log Shipping Alert Job - Backup'
  IF (EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @backup_job_name))
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Log Shipping Alert Job - Backup'

  DECLARE @restore_job_name sysname
  SET @restore_job_name = 'Log Shipping Alert Job - Restore'
  IF (EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE name = @restore_job_name))
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Log Shipping Alert Job - Restore'
END
go

/**************************************************************/
/* sp_delete_log_shipping_primary                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_primary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_primary' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_primary
go
CREATE PROCEDURE sp_delete_log_shipping_primary 
  @primary_server_name sysname,
  @primary_database_name sysname,
  @delete_secondaries BIT = 0
AS BEGIN
  DECLARE @primary_id INT

  SET NOCOUNT ON

  SELECT @primary_id = primary_id 
    FROM msdb.dbo.log_shipping_primaries 
    WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name
  IF (@primary_id IS NULL)
    RETURN (0)

  BEGIN TRANSACTION
  IF (EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE primary_id = @primary_id))
  BEGIN
    IF (@delete_secondaries = 0)
    BEGIN
      RAISERROR (14429,-1,-1)
      goto rollback_quit
    END
    DELETE FROM msdb.dbo.log_shipping_secondaries WHERE primary_id = @primary_id
    IF (@@ERROR <> 0)
      GOTO rollback_quit
  END
  DELETE FROM msdb.dbo.log_shipping_primaries WHERE primary_id = @primary_id
  IF (@@ERROR <> 0)
    GOTO rollback_quit

  COMMIT TRANSACTION
  DECLARE @i INT
  SELECT @i = COUNT(*) FROM msdb.dbo.log_shipping_primaries
  IF (@i=0)
    EXECUTE msdb.dbo.sp_delete_log_shipping_monitor_jobs
  RETURN (0)

rollback_quit:
  ROLLBACK TRANSACTION
  RETURN(1) -- error
END
go

/**************************************************************/
/* sp_delete_log_shipping_secondary                           */
/**************************************************************/
PRINT ''
PRINT 'Creating sp_delete_log_shipping_secondary...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_secondary' AND type = N'P')  )
  drop procedure sp_delete_log_shipping_secondary
go
CREATE PROCEDURE sp_delete_log_shipping_secondary 
  @secondary_server_name   sysname,
  @secondary_database_name sysname
AS BEGIN
  SET NOCOUNT ON
  DELETE FROM msdb.dbo.log_shipping_secondaries WHERE 
    secondary_server_name   = @secondary_server_name AND
    secondary_database_name = @secondary_database_name
END
go

/**************************************************************/
/* sp_log_shipping_in_sync                                    */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_in_sync...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_in_sync' AND type = N'P')  )
  drop procedure sp_log_shipping_in_sync
go
CREATE PROCEDURE sp_log_shipping_in_sync
  @last_updated        DATETIME,
  @compare_with        DATETIME,
  @threshold           INT,
  @outage_start_time   INT,
  @outage_end_time     INT,
  @outage_weekday_mask INT,
  @enabled             BIT = 1,
  @delta               INT = NULL OUTPUT
AS BEGIN
  SET NOCOUNT ON
  DECLARE @cur_time INT

  SELECT @delta = DATEDIFF (mi, @last_updated, @compare_with)
  -- in sync
  IF (@delta <= @threshold)
    RETURN (0) -- in sync

  IF (@enabled = 0) 
    RETURN(0) -- in sync

  IF (@outage_weekday_mask & DATEPART(dw, GETDATE ()) > 0) -- potentially in outage window
  BEGIN
    SELECT @cur_time = DATEPART (hh, GETDATE()) * 10000 +
                       DATEPART (mi, GETDATE()) * 100 + 
                       DATEPART (ss, GETDATE())
     -- outage doesn't span midnight
    IF (@outage_start_time < @outage_end_time)
    BEGIN
      IF (@cur_time >= @outage_start_time AND @cur_time < @outage_end_time)
        RETURN(1) -- in outage
    END
     -- outage does span midnight
   ELSE IF (@outage_start_time > @outage_end_time)
   BEGIN
     IF (@cur_time >= @outage_start_time OR @cur_time < @outage_end_time)
       RETURN(1) -- in outage
   END
  END
  RETURN(-1 ) -- not in outage, not in sync
END
go

/**************************************************************/
/* sp_log_shipping_get_date_from_file                         */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_get_date_from_file...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_get_date_from_file' AND type = N'P')  )
  drop procedure sp_log_shipping_get_date_from_file
go
CREATE PROCEDURE sp_log_shipping_get_date_from_file 
  @db_name sysname,
  @filename NVARCHAR (500),
  @file_date DATETIME OUTPUT
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @tempname NVARCHAR (500)
  IF (LEN (@filename) - (LEN(@db_name) + LEN ('_tlog_')) <= 0)
    RETURN(1) -- filename string isn't long enough
  SELECT @tempname = RIGHT (@filename, LEN (@filename) - (LEN(@db_name) + LEN ('_tlog_')))
  IF (CHARINDEX ('.',@tempname,0) > 0)
    SELECT @tempname = LEFT (@tempname, CHARINDEX ('.',@tempname,0) - 1)
  IF (LEN (@tempname) <>  8 AND LEN (@tempname) <> 12)
    RETURN (1) -- error must be yyyymmddhhmm or yyyymmdd
  IF (ISNUMERIC (@tempname) = 0 OR CHARINDEX ('.',@tempname,0) <> 0 OR CONVERT (FLOAT,SUBSTRING (@tempname, 1,8)) < 1 )
    RETURN (1) -- must be numeric, can't contain any '.' etc
  SELECT @file_date = CONVERT (DATETIME,SUBSTRING (@tempname, 1,8),112)
  IF (LEN (@tempname) = 12)
  BEGIN
    SELECT @file_date = DATEADD (hh, CONVERT (INT, SUBSTRING (@tempname,9,2)),@file_date)
    SELECT @file_date = DATEADD (mi, CONVERT (INT, SUBSTRING (@tempname,11,2)),@file_date)
  END
  RETURN (0) -- success
END
go

/**************************************************************/
/* sp_get_log_shipping_monitor_info                           */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_get_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_get_log_shipping_monitor_info' AND type = N'P')  )
  drop procedure sp_get_log_shipping_monitor_info
go
CREATE PROCEDURE sp_get_log_shipping_monitor_info
  @primary_server_name     sysname = N'%',
  @primary_database_name   sysname = N'%',
  @secondary_server_name   sysname = N'%',
  @secondary_database_name sysname = N'%'
AS BEGIN
  SET NOCOUNT ON
  DECLARE @lsp TABLE (
    primary_server_name            sysname       COLLATE database_default NOT NULL,
    primary_database_name          sysname       COLLATE database_default NOT NULL,
    secondary_server_name          sysname       COLLATE database_default NOT NULL,
    secondary_database_name        sysname       COLLATE database_default NOT NULL,
    backup_threshold               INT           NOT NULL,
    backup_threshold_alert         INT           NOT NULL,
    backup_threshold_alert_enabled BIT           NOT NULL,
    last_backup_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_backup_last_updated       DATETIME      NOT NULL,
    backup_outage_start_time       INT           NOT NULL,
    backup_outage_end_time         INT           NOT NULL,
    backup_outage_weekday_mask     INT           NOT NULL,
    backup_in_sync                 INT           NULL, -- 0 = in sync, -1 = out of sync, 1 = in outage window
    backup_delta                   INT           NULL,
    last_copied_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_copied_last_updated       DATETIME      NOT NULL,
    last_loaded_filename           NVARCHAR(500) COLLATE database_default NOT NULL,
    last_loaded_last_updated       DATETIME      NOT NULL,
    copy_delta                     INT           NULL,
    copy_enabled                   BIT           NOT NULL,
    load_enabled                   BIT           NOT NULL,
    out_of_sync_threshold          INT           NOT NULL,
    load_threshold_alert           INT           NOT NULL,
    load_threshold_alert_enabled   BIT           NOT NULL,
    load_outage_start_time         INT           NOT NULL,
    load_outage_end_time           INT           NOT NULL,
    load_outage_weekday_mask       INT           NOT NULL,
    load_in_sync                   INT           NULL, -- 0 = in sync, -1 = out of sync, 1 = in outage window
    load_delta                     INT           NULL,
    maintenance_plan_id             UNIQUEIDENTIFIER NULL,
    secondary_plan_id              UNIQUEIDENTIFIER NOT NULL)

  INSERT INTO @lsp

 SELECT
    primary_server_name,
    primary_database_name,
    secondary_server_name,
    secondary_database_name,
    backup_threshold,
    p.threshold_alert,
    p.threshold_alert_enabled,
    last_backup_filename,
    p.last_updated,
    p.planned_outage_start_time,
    p.planned_outage_end_time,
    p.planned_outage_weekday_mask,
    NULL,
    NULL,
    last_copied_filename,
    last_copied_last_updated,
    last_loaded_filename,
    last_loaded_last_updated,
    NULL,
    copy_enabled,
    load_enabled,
    out_of_sync_threshold,
    s.threshold_alert,
    s.threshold_alert_enabled,
    s.planned_outage_start_time,
    s.planned_outage_weekday_mask,
    s.planned_outage_end_time,
    NULL,
    NULL,
    maintenance_plan_id,
    secondary_plan_id
  FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s
  WHERE 
    p.primary_id = s.primary_id AND
    primary_server_name LIKE @primary_server_name AND
    primary_database_name LIKE @primary_database_name AND
    secondary_server_name LIKE @secondary_server_name AND
    secondary_database_name LIKE @secondary_database_name

  DECLARE @load_in_sync                   INT
  DECLARE @backup_in_sync                 INT
  DECLARE @_primary_server_name           sysname 
  DECLARE @_primary_database_name         sysname 
  DECLARE @_secondary_server_name         sysname
  DECLARE @_secondary_database_name       sysname
  DECLARE @last_loaded_last_updated       DATETIME
  DECLARE @last_loaded_filename           NVARCHAR (500)
  DECLARE @last_copied_filename           NVARCHAR (500)
  DECLARE @last_backup_last_updated       DATETIME
  DECLARE @last_backup_filename           NVARCHAR (500)
  DECLARE @backup_outage_start_time       INT
  DECLARE @backup_outage_end_time         INT
  DECLARE @backup_outage_weekday_mask     INT
  DECLARE @backup_threshold               INT
  DECLARE @backup_threshold_alert_enabled BIT
  DECLARE @load_outage_start_time         INT
  DECLARE @load_outage_end_time           INT
  DECLARE @load_outage_weekday_mask       INT
  DECLARE @load_threshold                 INT
  DECLARE @load_threshold_alert_enabled   BIT
  DECLARE @backupdt                       DATETIME
  DECLARE @restoredt                      DATETIME
  DECLARE @copydt                         DATETIME
  DECLARE @rv                             INT
  DECLARE @dt                             DATETIME
  DECLARE @copy_delta                     INT
  DECLARE @load_delta                     INT
  DECLARE @backup_delta                   INT
  DECLARE @last_copied_last_updated       DATETIME

  SELECT @dt = GETDATE ()

  DECLARE sync_update CURSOR FOR
    SELECT 
      primary_server_name, 
      primary_database_name, 
      secondary_server_name, 
      secondary_database_name,
      last_backup_filename,
      last_backup_last_updated,
      last_loaded_filename,
      last_loaded_last_updated,
      backup_outage_start_time,
      backup_outage_end_time,
      backup_outage_weekday_mask,
      backup_threshold,
      backup_threshold_alert_enabled,
      load_outage_start_time,
      load_outage_end_time,
      out_of_sync_threshold,
      load_outage_weekday_mask,
      load_threshold_alert_enabled,
      last_copied_filename,
      last_copied_last_updated
    FROM @lsp
    FOR READ ONLY

  OPEN sync_update

loop:
  FETCH NEXT FROM sync_update INTO
    @_primary_server_name, 
    @_primary_database_name, 
    @_secondary_server_name, 
    @_secondary_database_name,
    @last_backup_filename,
    @last_backup_last_updated,
    @last_loaded_filename,
    @last_loaded_last_updated,
    @backup_outage_start_time,
    @backup_outage_end_time,
    @backup_outage_weekday_mask,
    @backup_threshold,
    @backup_threshold_alert_enabled,
    @load_outage_start_time,
    @load_outage_end_time,
    @load_threshold,
    @load_outage_weekday_mask,
    @load_threshold_alert_enabled,
    @last_copied_filename,
    @last_copied_last_updated

  IF @@fetch_status <> 0
    GOTO _loop

  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_backup_filename, @backupdt OUTPUT
  IF (@rv <> 0)
    SELECT @backupdt = @last_backup_last_updated
  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_loaded_filename, @restoredt OUTPUT
  IF  (@rv <> 0)
    SELECT @restoredt = @last_loaded_last_updated
  EXECUTE @rv = sp_log_shipping_get_date_from_file @_primary_database_name, @last_copied_filename, @copydt OUTPUT
  IF  (@rv <> 0)
    SELECT @copydt = @last_copied_last_updated
  
  EXECUTE @load_in_sync = msdb.dbo.sp_log_shipping_in_sync
    @restoredt,
    @backupdt,
    @load_threshold,
    @load_outage_start_time,
    @load_outage_end_time,
    @load_outage_weekday_mask,
    @load_threshold_alert_enabled,
    @load_delta OUTPUT

  EXECUTE @backup_in_sync = msdb.dbo.sp_log_shipping_in_sync
    @last_backup_last_updated,
    @dt,
    @backup_threshold,
    @backup_outage_start_time,
    @backup_outage_end_time,
    @backup_outage_weekday_mask,
    @backup_threshold_alert_enabled,
    @backup_delta OUTPUT

  EXECUTE msdb.dbo.sp_log_shipping_in_sync
    @copydt,
    @backupdt,
    1,0,0,0,0,
    @copy_delta OUTPUT

  UPDATE @lsp 
  SET backup_in_sync = @backup_in_sync, load_in_sync  = @load_in_sync, 
    copy_delta = @copy_delta, load_delta = @load_delta, backup_delta = @backup_delta
  WHERE primary_server_name = @_primary_server_name AND
    secondary_server_name = @_secondary_server_name AND
    primary_database_name = @_primary_database_name AND
    secondary_database_name = @_secondary_database_name 
  GOTO loop
_loop:
  CLOSE sync_update
  DEALLOCATE sync_update
  SELECT * FROM @lsp
END
go

/**************************************************************/
/* sp_update_log_shipping_monitor_info                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_update_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_update_log_shipping_monitor_info' AND type = N'P')  )
  DROP PROCEDURE sp_update_log_shipping_monitor_info
go
CREATE PROCEDURE sp_update_log_shipping_monitor_info
  @primary_server_name                 sysname,
  @primary_database_name               sysname,
  @secondary_server_name               sysname,
  @secondary_database_name             sysname,
  @backup_threshold                    INT = NULL,
  @backup_threshold_alert              INT = NULL,
  @backup_threshold_alert_enabled      BIT = NULL,
  @backup_outage_start_time            INT = NULL,
  @backup_outage_end_time              INT = NULL,
  @backup_outage_weekday_mask          INT = NULL,
  @copy_enabled                        BIT = NULL,
  @load_enabled                        BIT = NULL,
  @out_of_sync_threshold               INT = NULL,
  @out_of_sync_threshold_alert         INT = NULL,
  @out_of_sync_threshold_alert_enabled BIT = NULL,
  @out_of_sync_outage_start_time       INT = NULL,
  @out_of_sync_outage_end_time         INT = NULL,
  @out_of_sync_outage_weekday_mask     INT = NULL
AS BEGIN
  SET NOCOUNT ON
  DECLARE @_backup_threshold                    INT
  DECLARE @_backup_threshold_alert              INT
  DECLARE @_backup_threshold_alert_enabled      BIT
  DECLARE @_backup_outage_start_time            INT
  DECLARE @_backup_outage_end_time              INT
  DECLARE @_backup_outage_weekday_mask          INT
  DECLARE @_copy_enabled                        BIT
  DECLARE @_load_enabled                        BIT
  DECLARE @_out_of_sync_threshold               INT
  DECLARE @_out_of_sync_threshold_alert         INT
  DECLARE @_out_of_sync_threshold_alert_enabled BIT
  DECLARE @_out_of_sync_outage_start_time       INT
  DECLARE @_out_of_sync_outage_end_time         INT
  DECLARE @_out_of_sync_outage_weekday_mask     INT

  -- check that the primary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DECLARE @pp sysname
    SELECT @pp = @primary_server_name + N'.' + @primary_database_name
    RAISERROR (14262, 16, 1, N'primary_server_name.primary_database_name', @pp)
    RETURN (1) -- error
  END

  -- check that the secondary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name))
  BEGIN
    DECLARE @sp sysname
    SELECT @sp = @secondary_server_name + N'.' + @secondary_database_name
    RAISERROR (14262, 16, 1, N'secondary_server_name.secondary_database_name', @sp)
    RETURN (1) -- error
  END

  -- load the original variables

 SELECT
    @_backup_threshold                    = backup_threshold,
    @_backup_threshold_alert              = p.threshold_alert,
    @_backup_threshold_alert_enabled      = p.threshold_alert_enabled,
    @_backup_outage_start_time            = p.planned_outage_start_time,
    @_backup_outage_end_time              = p.planned_outage_end_time,
    @_backup_outage_weekday_mask          = p.planned_outage_weekday_mask,
    @_copy_enabled                        = copy_enabled,
    @_load_enabled                        = load_enabled,
    @_out_of_sync_threshold               = out_of_sync_threshold,
    @_out_of_sync_threshold_alert         = s.threshold_alert,
    @_out_of_sync_threshold_alert_enabled = s.threshold_alert_enabled,
    @_out_of_sync_outage_start_time       = s.planned_outage_start_time,
    @_out_of_sync_outage_weekday_mask     = s.planned_outage_weekday_mask,
    @_out_of_sync_outage_end_time         = s.planned_outage_end_time
  FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s
  WHERE 
    p.primary_id            = s.primary_id           AND
    primary_server_name     = @primary_server_name   AND
    primary_database_name   = @primary_database_name AND
    secondary_server_name   = @secondary_server_name AND
    secondary_database_name = @secondary_database_name

  SELECT @_backup_threshold                    = ISNULL (@backup_threshold,                    @_backup_threshold)
  SELECT @_backup_threshold_alert              = ISNULL (@backup_threshold_alert,              @_backup_threshold_alert)
  SELECT @_backup_threshold_alert_enabled      = ISNULL (@backup_threshold_alert_enabled,      @_backup_threshold_alert_enabled)
  SELECT @_backup_outage_start_time            = ISNULL (@backup_outage_start_time,            @_backup_outage_start_time)
  SELECT @_backup_outage_end_time              = ISNULL (@backup_outage_end_time,              @_backup_outage_end_time)
  SELECT @_backup_outage_weekday_mask          = ISNULL (@backup_outage_weekday_mask,          @_backup_outage_weekday_mask)
  SELECT @_copy_enabled                        = ISNULL (@copy_enabled,                        @_copy_enabled)
  SELECT @_load_enabled                        = ISNULL (@load_enabled,                        @_load_enabled)
  SELECT @_out_of_sync_threshold               = ISNULL (@out_of_sync_threshold,               @_out_of_sync_threshold)
  SELECT @_out_of_sync_threshold_alert         = ISNULL (@out_of_sync_threshold_alert,         @_out_of_sync_threshold_alert)
  SELECT @_out_of_sync_threshold_alert_enabled = ISNULL (@out_of_sync_threshold_alert_enabled, @_out_of_sync_threshold_alert_enabled)
  SELECT @_out_of_sync_outage_start_time       = ISNULL (@out_of_sync_outage_start_time,       @_out_of_sync_outage_start_time)
  SELECT @_out_of_sync_outage_end_time         = ISNULL (@out_of_sync_outage_end_time,         @_out_of_sync_outage_end_time)
  SELECT @_out_of_sync_outage_weekday_mask     = ISNULL (@out_of_sync_outage_weekday_mask,     @_out_of_sync_outage_weekday_mask)

  -- updates
  UPDATE msdb.dbo.log_shipping_primaries SET
    backup_threshold            = @_backup_threshold,
    threshold_alert             = @_backup_threshold_alert,
    threshold_alert_enabled     = @_backup_threshold_alert_enabled,
    planned_outage_start_time   = @_backup_outage_start_time,
    planned_outage_end_time     = @_backup_outage_end_time,
    planned_outage_weekday_mask = @_backup_outage_weekday_mask
  WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name

  UPDATE msdb.dbo.log_shipping_secondaries SET
    copy_enabled                = @_copy_enabled,
    load_enabled                = @_load_enabled,
    out_of_sync_threshold       = @_out_of_sync_threshold,
    threshold_alert             = @_out_of_sync_threshold_alert,
    threshold_alert_enabled     = @_out_of_sync_threshold_alert_enabled,
    planned_outage_start_time   = @_out_of_sync_outage_start_time,
    planned_outage_end_time     = @_out_of_sync_outage_weekday_mask,
    planned_outage_weekday_mask = @_out_of_sync_outage_end_time
  WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name
RETURN(0)
END
go

/**************************************************************/
/* sp_delete_log_shipping_monitor_info                        */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_delete_log_shipping_monitor_info...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_delete_log_shipping_monitor_info' AND type = N'P')  )
  DROP PROCEDURE sp_delete_log_shipping_monitor_info
go
CREATE PROCEDURE sp_delete_log_shipping_monitor_info
  @primary_server_name                 sysname,
  @primary_database_name               sysname,
  @secondary_server_name               sysname,
  @secondary_database_name             sysname
AS BEGIN
  -- check that the primary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DECLARE @pp sysname
    SELECT @pp = @primary_server_name + N'.' + @primary_database_name
    RAISERROR (14262, 16, 1, N'primary_server_name.primary_database_name', @pp)
    RETURN (1) -- error
  END

  -- check that the secondary exists
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name))
  BEGIN
    DECLARE @sp sysname
    SELECT @sp = @secondary_server_name + N'.' + @secondary_database_name
    RAISERROR (14262, 16, 1, N'secondary_server_name.secondary_database_name', @sp)
    RETURN (1) -- error
  END

  BEGIN TRANSACTION

  -- delete the secondary
  DELETE FROM msdb.dbo.log_shipping_secondaries WHERE secondary_server_name = @secondary_server_name AND secondary_database_name = @secondary_database_name
  IF (@@error <> 0)
    goto rollback_quit

  -- if there are no more secondaries for this primary then delete it
  IF (NOT EXISTS (SELECT * FROM msdb.dbo.log_shipping_primaries p, msdb.dbo.log_shipping_secondaries s WHERE p.primary_id = s.primary_id AND primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name))
  BEGIN
    DELETE FROM msdb.dbo.log_shipping_primaries WHERE primary_server_name = @primary_server_name AND primary_database_name = @primary_database_name
    IF (@@error <> 0)
      goto rollback_quit
  END
 COMMIT TRANSACTION
 RETURN (0)

rollback_quit:
  ROLLBACK TRANSACTION
  RETURN(1) -- Failure
END
go 

/**************************************************************/
/* sp_remove_log_shipping_monitor_account                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_remove_log_shipping_monitor_account...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_remove_log_shipping_monitor_account' AND type = N'P')  )
  DROP PROCEDURE sp_remove_log_shipping_monitor_account
go

CREATE PROCEDURE sp_remove_log_shipping_monitor_account
AS
BEGIN
  SET NOCOUNT ON
  EXECUTE sp_dropuser N'log_shipping_monitor_probe'
  EXECUTE sp_droplogin N'log_shipping_monitor_probe'
END
go

/**************************************************************/
/* sp_log_shipping_monitor_backup                             */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_monitor_backup...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_monitor_backup' AND type = N'P')  )
  drop procedure sp_log_shipping_monitor_backup
go

CREATE PROCEDURE sp_log_shipping_monitor_backup AS
BEGIN
  DECLARE @primary_id                  sysname
  DECLARE @primary_server_name         sysname 
  DECLARE @primary_database_name       sysname 
  DECLARE @maintenance_plan_id         UNIQUEIDENTIFIER
  DECLARE @backup_threshold            INT
  DECLARE @threshold_alert             INT 
  DECLARE @threshold_alert_enabled     BIT 
  DECLARE @last_backup_filename        sysname 
  DECLARE @last_updated                DATETIME
  DECLARE @planned_outage_start_time   INT
  DECLARE @planned_outage_end_time     INT 
  DECLARE @planned_outage_weekday_mask INT
  DECLARE @sync_status                 INT
  DECLARE @backup_delta                INT
  DECLARE @delta_string                NVARCHAR (10)
  DECLARE @dt                             DATETIME

  SELECT @dt = GETDATE ()

  SET NOCOUNT ON

  DECLARE bmlsp_cur CURSOR FOR
    SELECT primary_id, 
           primary_server_name, 
           primary_database_name, 
         maintenance_plan_id, 
           backup_threshold, 
           threshold_alert, 
           threshold_alert_enabled, 
           last_backup_filename, 
           last_updated,
           planned_outage_start_time, 
           planned_outage_end_time, 
           planned_outage_weekday_mask 
    FROM msdb.dbo.log_shipping_primaries
    FOR READ ONLY

  OPEN bmlsp_cur
loop:
  FETCH NEXT FROM bmlsp_cur 
  INTO @primary_id, 
       @primary_server_name, 
      @primary_database_name, 
      @maintenance_plan_id,
       @backup_threshold, 
      @threshold_alert, 
      @threshold_alert_enabled, 
      @last_backup_filename, 
      @last_updated, 
      @planned_outage_start_time,
       @planned_outage_end_time, 
      @planned_outage_weekday_mask

  IF @@FETCH_STATUS <> 0 -- nothing more to fetch, finish the loop
    GOTO _loop

  EXECUTE @sync_status = sp_log_shipping_in_sync
    @last_updated,
    @dt,
     @backup_threshold,
   @planned_outage_start_time,
   @planned_outage_end_time,
    @planned_outage_weekday_mask,
   @threshold_alert_enabled,
   @backup_delta OUTPUT

   IF (@sync_status < 0)
   BEGIN
     SELECT @delta_string = CONVERT (NVARCHAR(10), @backup_delta)
     RAISERROR (@threshold_alert, 16, 1, @primary_server_name, @primary_database_name, @delta_string)
   END

  GOTO loop
_loop:
  CLOSE bmlsp_cur
  DEALLOCATE bmlsp_cur
END
go

/**************************************************************/
/* sp_log_shipping_monitor_restore                            */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_log_shipping_monitor_restore...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_log_shipping_monitor_restore' AND type = N'P')  )
  drop procedure sp_log_shipping_monitor_restore
go
CREATE PROCEDURE sp_log_shipping_monitor_restore AS
BEGIN
  SET NOCOUNT ON
  DECLARE @primary_id                  INT
  DECLARE @secondary_server_name       sysname
  DECLARE @secondary_database_name     sysname
  DECLARE @secondary_plan_id           UNIQUEIDENTIFIER
  DECLARE @out_of_sync_threshold       INT 
  DECLARE @threshold_alert             INT 
  DECLARE @threshold_alert_enabled     BIT 
  DECLARE @last_loaded_filename        NVARCHAR (500)
  DECLARE @last_backup_filename        NVARCHAR (500) 
  DECLARE @primary_database_name       sysname
  DECLARE @last_loaded_last_updated    DATETIME
  DECLARE @last_backup_last_updated    DATETIME
  DECLARE @planned_outage_start_time   INT 
  DECLARE @planned_outage_end_time     INT 
  DECLARE @planned_outage_weekday_mask INT
  DECLARE @sync_status                 INT
  DECLARE @sync_delta                  INT
  DECLARE @delta_string                NVARCHAR(10)

  SET NOCOUNT ON
  DECLARE @backupdt  DATETIME
  DECLARE @restoredt DATETIME
  DECLARE @rv        INT
  DECLARE rmlsp_cur CURSOR FOR
    SELECT s.primary_id, 
      s.secondary_server_name, 
      s.secondary_database_name, 
      s.secondary_plan_id, 
      s.out_of_sync_threshold, 
      s.threshold_alert, 
      s.threshold_alert_enabled, 
      s.last_loaded_filename, 
      s.last_loaded_last_updated,
      p.last_backup_filename,
      p.last_updated,
      p.primary_database_name,
      s.planned_outage_start_time, 
      s.planned_outage_end_time, 
      s.planned_outage_weekday_mask 
    FROM msdb.dbo.log_shipping_secondaries s 
    INNER JOIN msdb.dbo.log_shipping_primaries p 
    ON s.primary_id = p.primary_id
    FOR READ ONLY

  OPEN rmlsp_cur
loop:
  FETCH NEXT FROM rmlsp_cur 
  INTO @primary_id, 
      @secondary_server_name, 
         @secondary_database_name, 
         @secondary_plan_id, 
       @out_of_sync_threshold, 
         @threshold_alert, 
         @threshold_alert_enabled, 
         @last_loaded_filename, 
         @last_loaded_last_updated,
       @last_backup_filename,
       @last_backup_last_updated,
       @primary_database_name,
       @planned_outage_start_time, 
         @planned_outage_end_time, 
         @planned_outage_weekday_mask 

  IF @@FETCH_STATUS <> 0 -- nothing more to fetch, finish the loop
    GOTO _loop

  EXECUTE @rv = sp_log_shipping_get_date_from_file @primary_database_name, @last_backup_filename, @backupdt OUTPUT
  IF (@rv <> 0)
    SELECT @backupdt = @last_backup_last_updated
  
  EXECUTE @rv = sp_log_shipping_get_date_from_file @primary_database_name, @last_loaded_filename, @restoredt OUTPUT
  IF  (@rv <> 0)
    SELECT @restoredt = @last_loaded_last_updated

  EXECUTE @sync_status = sp_log_shipping_in_sync
    @restoredt,
    @backupdt,
     @out_of_sync_threshold,
     @planned_outage_start_time,
     @planned_outage_end_time,
    @planned_outage_weekday_mask,
    @threshold_alert_enabled,
    @sync_delta OUTPUT

   IF (@sync_status < 0)
   BEGIN
     SELECT @delta_string = CONVERT (NVARCHAR(10), @sync_delta)
     RAISERROR (@threshold_alert, 16, 1, @secondary_server_name, @secondary_database_name, @delta_string)
   END

  GOTO loop
_loop:
  CLOSE rmlsp_cur
  DEALLOCATE rmlsp_cur
END
go

/**************************************************************/
/* sp_change_monitor_role                                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_change_monitor_role...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_change_monitor_role' AND type = N'P')  )
  DROP PROCEDURE sp_change_monitor_role
go
CREATE PROCEDURE sp_change_monitor_role
  @primary_server     sysname,
  @secondary_server   sysname,
  @database           sysname,
  @new_source         NVARCHAR (128)
AS BEGIN
  SET NOCOUNT ON

  BEGIN TRANSACTION

  -- drop the secondary
  DELETE FROM msdb.dbo.log_shipping_secondaries 
    WHERE secondary_server_name = @secondary_server AND secondary_database_name = @database

  IF (@@ROWCOUNT <> 1)
  BEGIN
      ROLLBACK TRANSACTION
      RAISERROR (14442,-1,-1)
      return(1)
  END

  -- let everyone know that we are the new primary
  UPDATE msdb.dbo.log_shipping_primaries 
    SET primary_server_name = @secondary_server, primary_database_name = @database, source_directory = @new_source
    WHERE primary_server_name = @primary_server AND primary_database_name = @database

  IF (@@ROWCOUNT <> 1)
  BEGIN
      ROLLBACK TRANSACTION
      RAISERROR (14442,-1,-1)
      return(1)
  END
  COMMIT TRANSACTION

END
go

/**************************************************************/
/* sp_create_log_shipping_monitor_account                     */
/**************************************************************/
PRINT ''
PRINT 'Creating procedure sp_create_log_shipping_monitor_account...'
go
IF (EXISTS (SELECT * from msdb.dbo.sysobjects WHERE name = N'sp_create_log_shipping_monitor_account' AND type = N'P')  )
  drop procedure sp_create_log_shipping_monitor_account
go
CREATE PROCEDURE sp_create_log_shipping_monitor_account @password sysname
AS
BEGIN
  DECLARE @rv INT
  SET NOCOUNT ON
-- raise an error if the password already exists
  if exists(select * from master.dbo.syslogins where loginname = N'log_shipping_monitor_probe')
  begin
    raiserror(15025,-1,-1,N'log_shipping_monitor_probe')
    RETURN (1) -- error
  end

  IF (@password = N'')
  BEGIN
    EXECUTE @rv = sp_addlogin N'log_shipping_monitor_probe', @defdb = N'msdb'
    IF @@error <>0 or @rv <> 0
      RETURN (1) -- error
  END
  ELSE
  BEGIN
    EXECUTE @rv = sp_addlogin N'log_shipping_monitor_probe', @password, N'msdb'
    IF @@error <>0 or @rv <> 0
      RETURN (1) -- error
  END

  EXECUTE @rv = sp_grantdbaccess N'log_shipping_monitor_probe', N'log_shipping_monitor_probe'
  IF @@error <>0 or @rv <> 0
    RETURN (1) -- error

  GRANT UPDATE ON log_shipping_primaries   TO log_shipping_monitor_probe
  GRANT UPDATE ON log_shipping_secondaries TO log_shipping_monitor_probe
  GRANT SELECT ON log_shipping_primaries   TO log_shipping_monitor_probe
  GRANT SELECT ON log_shipping_secondaries TO log_shipping_monitor_probe

  RETURN (0)
END
go

/**************************************************************/
/* INTEGRATION SERVICES SECTION                               */
/**************************************************************/

USE msdb
GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysssispackages]'))
BEGIN
CREATE TABLE [dbo].[sysssispackages] (
   [name] [sysname] NOT NULL ,
   [id] [uniqueidentifier] NOT NULL ,
   [description] [nvarchar] (1024) NULL ,
   [createdate] [datetime] NOT NULL ,
   [folderid] [uniqueidentifier] NOT NULL ,
   [ownersid] [varbinary] (85) NOT NULL ,
   [packagedata] [image] NOT NULL ,
   [packageformat] [int] NOT NULL,
   [packagetype] [int] NOT NULL CONSTRAINT [DF__sysssispackages] DEFAULT (0),
   [vermajor] [int] NOT NULL,
   [verminor] [int] NOT NULL,
   [verbuild] [int] NOT NULL,
   [vercomments] [nvarchar] (1024) NULL,
   [verid] [uniqueidentifier] NOT NULL,
   [isencrypted] [bit] NOT NULL CONSTRAINT [DF__sysssispackages_2] DEFAULT (0),
   [readrolesid] [varbinary] (85) NULL,
   [writerolesid] [varbinary] (85) NULL,
   CONSTRAINT [pk_sysssispackages] PRIMARY KEY NONCLUSTERED 
   (
      [folderid],
      [name]
   ) ON [PRIMARY] ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
else
BEGIN
IF NOT EXISTS (
  select * from msdb.dbo.syscolumns where name='isencrypted' and id =
      (select id from msdb.dbo.sysobjects where name='sysssispackages'))
BEGIN
  ALTER TABLE [dbo].[sysssispackages] ADD [isencrypted] [bit] NOT NULL CONSTRAINT [DF__sysssispackages_2] DEFAULT (0)
  ALTER TABLE [dbo].[sysssispackages] ADD [readrolesid] [varbinary] (85) NULL
  ALTER TABLE [dbo].[sysssispackages] ADD [writerolesid] [varbinary] (85) NULL
END
ELSE
BEGIN
  IF NOT EXISTS (
    select * from msdb.dbo.syscolumns where name='readrolesid' and id =
        (select id from msdb.dbo.sysobjects where name='sysssispackages'))
  BEGIN
    ALTER TABLE [dbo].[sysssispackages] DROP COLUMN [readrole]
    ALTER TABLE [dbo].[sysssispackages] DROP COLUMN [writerole]
    ALTER TABLE [dbo].[sysssispackages] ADD [readrolesid] [varbinary] (85) NULL
    ALTER TABLE [dbo].[sysssispackages] ADD [writerolesid] [varbinary] (85) NULL
  END
END
END
GO

/**************************************************************/
/* sysmaintplan_plans                                      */
/**************************************************************/
PRINT ''
PRINT 'Creating view sysmaintplan_plans...'
go
IF (NOT OBJECT_ID(N'dbo.sysmaintplan_plans', 'V') IS NULL)
  DROP VIEW sysmaintplan_plans
go
CREATE VIEW sysmaintplan_plans
AS
   SELECT
   s.name AS [name],
   s.id AS [id],
   s.description AS [description],
   s.createdate AS [create_date],
   suser_sname(s.ownersid) AS [owner],
   s.vermajor AS [version_major],
   s.verminor AS [version_minor],
   s.verbuild AS [version_build],
   s.vercomments AS [version_comments],
   ISNULL((select TOP 1 msx_plan from sysmaintplan_subplans where plan_id = s.id), 0) AS [from_msx],
   CASE WHEN (NOT EXISTS (select TOP 1 msx_job_id 
                          from sysmaintplan_subplans subplans, sysjobservers jobservers
                          where plan_id = s.id 
                          and msx_job_id is not null
                          and subplans.msx_job_id = jobservers.job_id
                          and server_id != 0)) 
        then 0 
        else 1 END AS [has_targets]
   FROM
   msdb.dbo.sysssispackages AS s
   WHERE
   (s.folderid = '08aa12d5-8f98-4dab-a4fc-980b150a5dc8' and s.packagetype = 6)
go

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysssispackagefolders]'))
BEGIN
CREATE TABLE [dbo].[sysssispackagefolders] (
   [folderid] [uniqueidentifier] NOT NULL ,
   [parentfolderid] [uniqueidentifier] NULL ,
   [foldername] [sysname] NOT NULL ,
   CONSTRAINT [PK_sysssispackagefolders] PRIMARY KEY NONCLUSTERED 
   (
      [folderid]
   )  ON [PRIMARY],
   CONSTRAINT [U_sysssispackagefoldersuniquepath] UNIQUE NONCLUSTERED 
   (
      [parentfolderid], 
      [foldername]
   ) ON [PRIMARY]
) ON [PRIMARY]
END
GO

-- WARNING! IMPORTANT! If you change sysssislog table schema,
-- be sure to update \dts\src\dtr\runtime\logproviders.cpp !!!

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sysssislog]'))
BEGIN
CREATE TABLE [dbo].[sysssislog] (
   [id] [int] NOT NULL IDENTITY PRIMARY KEY,
   [event] [sysname] NOT NULL,
   [computer] [nvarchar] (128) NOT NULL,
   [operator] [nvarchar] (128) NOT NULL,
   [source] [nvarchar] (1024) NOT NULL,
   [sourceid] [uniqueidentifier] NOT NULL,
   [executionid] [uniqueidentifier] NOT NULL,
   [starttime] [datetime] NOT NULL,
   [endtime] [datetime] NOT NULL,
   [datacode] [int] NOT NULL,
   [databytes] [image] NULL,
   [message] [nvarchar] (2048) NOT NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_addlogentry]'))
    drop procedure [dbo].[sp_ssis_addlogentry]   

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_addlogentry]
  @event sysname,
  @computer nvarchar(128),
  @operator nvarchar(128),
  @source nvarchar(1024),
  @sourceid uniqueidentifier,
  @executionid uniqueidentifier,
  @starttime datetime,
  @endtime datetime,
  @datacode int,
  @databytes image,
  @message nvarchar(2048)
AS
  INSERT INTO sysssislog (
      event,
      computer,
      operator,
      source,
      sourceid,
      executionid,
      starttime,
      endtime,
      datacode,
      databytes,
      message )
  VALUES (
      @event,
      @computer,
      @operator,
      @source,
      @sourceid,
      @executionid,
      @starttime,
      @endtime,
      @datacode,
      @databytes,
      @message )
  RETURN 0
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_listpackages]'))
    drop procedure [dbo].[sp_ssis_listpackages]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_listpackages]
  @folderid uniqueidentifier
AS
  SELECT
      name,
      id,
      description,
      createdate,
      folderid,
      datalength(packagedata),
      vermajor,
      verminor,
      verbuild,
      vercomments,
      verid
  FROM
      sysssispackages
  WHERE
      [folderid] = @folderid
  ORDER BY
      name
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_listfolders]'))
    drop procedure [dbo].[sp_ssis_listfolders]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_listfolders]
  @parentfolderid uniqueidentifier = NULL
AS
  SELECT
   folderid,
   parentfolderid,
   foldername
  FROM
      sysssispackagefolders
  WHERE
      [parentfolderid] = @parentfolderid OR 
      (@parentfolderid IS NULL AND [parentfolderid] IS NULL)
  ORDER BY 
      foldername
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_deletepackage]'))
    drop procedure [dbo].[sp_ssis_deletepackage]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_deletepackage]
  @name sysname,
  @folderid uniqueidentifier
AS
  DECLARE @sid varbinary(85)
  DECLARE @writerolesid varbinary(85)
  DECLARE @writerole nvarchar(128)
  SELECT
      @sid = [ownersid],
      @writerolesid = [writerolesid]
  FROM
      sysssispackages
  WHERE
      [name] = @name AND
      [folderid] = @folderid
  IF @sid IS NOT NULL
  BEGIN
      --// The row exists, check security
      IF @writerolesid IS NOT NULL
      BEGIN
          SELECT @writerole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @writerolesid
          IF @writerole IS NULL SET @writerole = ''db_ssisadmin''
      END
      IF @writerole IS NULL
      BEGIN
          IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (@sid<>SUSER_SID()) OR (IS_MEMBER(''db_ssisltduser'')<>1)
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
      ELSE
      BEGIN
          -- If writerrole is set for this package, 
          -- Allow sysadmins and the members of writer role to delete this package
          IF (IS_MEMBER(@writerole)<>1)  AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (@sid<>SUSER_SID()) OR (IS_MEMBER(''db_ssisltduser'')<>1)
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
  END
  DELETE FROM sysssispackages
  WHERE
      [name] = @name AND
      [folderid] = @folderid
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_deletefolder]'))
    drop procedure [dbo].[sp_ssis_deletefolder]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_deletefolder]
  @folderid uniqueidentifier
AS
   DECLARE @name  sysname
   DECLARE @count int

   IF @folderid = ''00000000-0000-0000-0000-000000000000''
   BEGIN
      RAISERROR (14307, -1, -1, ''00000000-0000-0000-0000-000000000000'')
      RETURN 1  -- Failure
   END

   SELECT
       @name = [foldername]
   FROM
       sysssispackagefolders
   WHERE
       [folderid] = @folderid
   IF @name IS NOT NULL
   BEGIN
       --// The row exists, check security
       IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
       BEGIN
           IF (IS_MEMBER(''db_ssisltduser'')<>1)
           BEGIN
               RAISERROR (14307, -1, -1, @name)
               RETURN 1  -- Failure
           END
       END
   END

   -- Get the number of packages in this folder
   SELECT
      @count = count(*)
   FROM
      sysssispackages
   WHERE
      [folderid] = @folderid

   -- Are there any packages in this folder
   IF @count > 0
   BEGIN
      -- Yes, do not delete
      RAISERROR (14593, -1, -1, @name)
      RETURN 1  -- Failure
   END

   -- Get the number of folders in this folder
   SELECT
      @count = count(*)
   FROM
      sysssispackagefolders
   WHERE
      [parentfolderid] = @folderid

   -- Are there any folders in this folder
   IF @count > 0
   BEGIN
      -- Yes, do not delete
      RAISERROR (14593, -1, -1, @name)
      RETURN 1  -- Failure
   END

   DELETE FROM sysssispackagefolders
   WHERE
       [folderid] = @folderid
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_getpackage]'))
    drop procedure [dbo].[sp_ssis_getpackage]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_getpackage]
  @name sysname,
  @folderid uniqueidentifier
AS
  DECLARE @sid varbinary(85)
  DECLARE @isencrypted bit
  DECLARE @readrolesid varbinary(85)
  DECLARE @readrole nvarchar(128)
  --// Check security, if the row exists
  SELECT @sid = [ownersid], @readrolesid = [readrolesid] FROM sysssispackages WHERE [name] = @name AND [folderid] = @folderid
  IF @sid IS NOT NULL
  BEGIN
      IF @readrolesid IS NOT NULL
      BEGIN
          SELECT @readrole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @readrolesid
          IF @readrole IS NULL SET @readrole = ''db_ssisadmin''
      END
      IF @readrole IS NOT NULL
      BEGIN
          IF (IS_MEMBER(@readrole)<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (IS_MEMBER(''db_ssisltduser'')<>1) OR (@sid<>SUSER_SID())
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
      ELSE
      BEGIN
          IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1) AND (IS_MEMBER(''db_ssisoperator'')<>1)
          BEGIN
              IF (IS_MEMBER(''db_ssisltduser'')<>1) OR (@sid<>SUSER_SID())
              BEGIN
                  RAISERROR (14586, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
  END

  SELECT
      packagedata
  FROM
      sysssispackages
  WHERE
      [name] = @name AND
      [folderid] = @folderid
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_getfolder]'))
    drop procedure [dbo].[sp_ssis_getfolder]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_getfolder]
  @name sysname,
  @parentfolderid uniqueidentifier
AS
  SELECT
   folder.folderid,
   folder.foldername,
   folder.parentfolderid,
   parent.foldername
  FROM
      sysssispackagefolders folder 
  LEFT OUTER JOIN 
      sysssispackagefolders parent
  ON
      folder.parentfolderid = parent.folderid
  WHERE
      folder.foldername = @name AND
      (folder.parentfolderid = @parentfolderid OR 
      (@parentfolderid IS NULL AND folder.parentfolderid IS NULL))
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_putpackage]'))
    drop procedure [dbo].[sp_ssis_putpackage]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_putpackage]
  @name sysname,
  @id uniqueidentifier,
  @description nvarchar(1024),
  @createdate datetime,
  @folderid uniqueidentifier,
  @packagedata image,
  @packageformat int,
  @packagetype int,
  @vermajor int,
  @verminor int,
  @verbuild int,
  @vercomments nvarchar(1024),
  @verid uniqueidentifier
AS
  SET NOCOUNT ON
  DECLARE @sid varbinary(85)
  DECLARE @writerolesid varbinary(85)
  DECLARE @writerole nvarchar(128)
  --// Determine if we should INSERT or UPDATE
  SELECT @sid = [ownersid], @writerolesid = [writerolesid] FROM sysssispackages WHERE [name] = @name AND [folderid] = @folderid
  IF @sid IS NOT NULL
  BEGIN
      --// The row exists, check security
      IF @writerolesid IS NOT NULL
      BEGIN
          SELECT @writerole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @writerolesid
          IF @writerole IS NULL SET @writerole = ''db_ssisadmin''
      END
      IF @writerole IS NULL
      BEGIN
          IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (@sid<>SUSER_SID()) OR (IS_MEMBER(''db_ssisltduser'')<>1)
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
      ELSE
      BEGIN
          IF (IS_MEMBER(@writerole)<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
          BEGIN
              IF (@sid<>SUSER_SID()) OR (IS_MEMBER(''db_ssisltduser'')<>1)
              BEGIN
                  RAISERROR (14307, -1, -1, @name)
                  RETURN 1  -- Failure
              END
          END
      END
      --// Security check passed, UPDATE now
      UPDATE sysssispackages
      SET
          id = @id,
          description = @description,
          createdate = @createdate,
          packagedata = @packagedata,
          packageformat = @packageformat,
          packagetype = @packagetype,
          vermajor = @vermajor,
          verminor = @verminor,
          verbuild = @verbuild,
          vercomments = @vercomments,
          verid = @verid
      WHERE
          name = @name AND folderid = @folderid
  END
  ELSE
  BEGIN
      --// The row does not exist, check security
      IF (IS_MEMBER(''db_ssisltduser'')<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
      BEGIN
          RAISERROR (14307, -1, -1, @name)
          RETURN 1  -- Failure
      END
      --// Security check passed, INSERT now
      INSERT INTO sysssispackages (
          name,
          id,
          description,
          createdate,
          folderid,
          ownersid,
          packagedata,
          packageformat,
          packagetype,
          vermajor,
          verminor,
          verbuild,
          vercomments,
          verid )
      VALUES (
          @name,
          @id,
          @description,
          @createdate,
          @folderid,
          SUSER_SID(),
          @packagedata,
          @packageformat,
          @packagetype,
          @vermajor,
          @verminor,
          @verbuild,
          @vercomments,
          @verid )
  END
  RETURN 0    -- SUCCESS
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_checkexists]'))
    drop procedure [dbo].[sp_ssis_checkexists]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_checkexists]
  @name sysname,
  @folderid uniqueidentifier
AS
  SET NOCOUNT ON
  SELECT TOP 1 1 FROM sysssispackages WHERE [name] = @name AND [folderid] = @folderid
  RETURN 0    -- SUCCESS
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_addfolder]'))
    drop procedure [dbo].[sp_ssis_addfolder]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_addfolder]
  @parentfolderid uniqueidentifier,
  @name sysname,
  @folderid uniqueidentifier = NULL
AS
   --Check security
   IF (IS_MEMBER(''db_ssisltduser'')<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
   BEGIN
       RAISERROR (14591, -1, -1, @name)
       RETURN 1  -- Failure
   END

   --// Security check passed, INSERT now
   INSERT INTO sysssispackagefolders (folderid, parentfolderid, foldername)
   VALUES (ISNULL(@folderid, NEWID()), @parentfolderid, @name)
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_renamefolder]'))
    drop procedure [dbo].[sp_ssis_renamefolder]

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_renamefolder]
  @folderid uniqueidentifier,
  @name sysname
AS
   --Check security
   IF (IS_MEMBER(''db_ssisltduser'')<>1) AND (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
   BEGIN
       RAISERROR (14591, -1, -1, @name)
       RETURN 1  -- Failure
   END

   --// Security check passed, INSERT now
   UPDATE sysssispackagefolders
   SET [foldername] = @name
   WHERE [folderid] = @folderid
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_setpackageroles]'))
DROP PROCEDURE [dbo].[sp_ssis_setpackageroles]
GO

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_setpackageroles]
  @name sysname,
  @folderid uniqueidentifier,
  @readrole nvarchar (128),
  @writerole nvarchar (128)
AS
  SET NOCOUNT ON
  DECLARE @sid varbinary(85)
  --// Determine if we should INSERT or UPDATE
  SELECT @sid = ownersid FROM sysssispackages WHERE name = @name AND folderid = @folderid
  IF @sid IS NOT NULL
  BEGIN
      --// The row exists, check security
      IF (IS_MEMBER(''db_ssisadmin'')<>1) AND (IS_SRVROLEMEMBER(''sysadmin'')<>1)
      BEGIN
          IF (@sid<>SUSER_SID())
          BEGIN
              RAISERROR (14307, -1, -1, @name)
              RETURN 1  -- Failure
          END
      END
      --// Security check passed, UPDATE now
      DECLARE @readrolesid varbinary(85)
      DECLARE @writerolesid varbinary(85)
      SELECT @readrolesid = [sid] FROM sys.database_principals WHERE [type] = ''R'' AND [name] = @readrole
      SELECT @writerolesid = [sid] FROM sys.database_principals WHERE [type] = ''R'' AND [name] = @writerole
      IF @readrolesid IS NULL AND @readrole IS NOT NULL
      BEGIN
          RAISERROR (15014, -1, -1, @readrole)
          RETURN 1
      END
      IF @writerolesid IS NULL AND @writerole IS NOT NULL
      BEGIN
          RAISERROR (15014, -1, -1, @writerole)
          RETURN 1
      END
      UPDATE sysssispackages
      SET
          [readrolesid] = @readrolesid,
          [writerolesid] = @writerolesid
      WHERE
          name = @name AND folderid = @folderid
  END
  ELSE
  BEGIN
      RAISERROR (14307, -1, -1, @name)
      RETURN 1  -- Failure
  END
  RETURN 0    -- SUCCESS
'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ssis_getpackageroles]'))
DROP PROCEDURE [dbo].[sp_ssis_getpackageroles]
GO

execute sp_executesql 
 N'CREATE PROCEDURE [dbo].[sp_ssis_getpackageroles]
  @name sysname,
  @folderid uniqueidentifier
AS
  DECLARE @readrolesid varbinary(85)
  DECLARE @writerolesid varbinary(85)
  DECLARE @readrole nvarchar(128)
  DECLARE @writerole nvarchar(128)
  SELECT
      @readrolesid = [readrolesid],
      @writerolesid = [writerolesid]
  FROM
      sysssispackages
  WHERE
      [name] = @name AND
      [folderid] = @folderid
  SELECT @readrole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @readrolesid
  SELECT @writerole = [name] FROM sys.database_principals WHERE [type] = ''R'' AND [sid] = @writerolesid
  SELECT @readrole AS readrole, @writerole AS writerole
'
GO


GRANT  EXECUTE  ON [dbo].[sp_ssis_addlogentry]     TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_addlogentry]     TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletepackage]   TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletepackage]   TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletepackage]   TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackage]      TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackage]      TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackage]      TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listpackages]    TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listpackages]    TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listpackages]    TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_putpackage]      TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_putpackage]      TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_putpackage]      TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_checkexists]     TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_checkexists]     TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_checkexists]     TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listfolders]     TO [db_ssisadmin]  
GRANT  EXECUTE  ON [dbo].[sp_ssis_listfolders]     TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_listfolders]     TO [db_ssisoperator] 
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletefolder]    TO [db_ssisadmin]  
GRANT  EXECUTE  ON [dbo].[sp_ssis_deletefolder]    TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_addfolder]       TO [db_ssisadmin]  
GRANT  EXECUTE  ON [dbo].[sp_ssis_addfolder]       TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_renamefolder]    TO [db_ssisadmin]  
GRANT  EXECUTE  ON [dbo].[sp_ssis_renamefolder]    TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getfolder]       TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getfolder]       TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getfolder]       TO [db_ssisoperator]
GRANT  EXECUTE  ON [dbo].[sp_ssis_setpackageroles] TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_setpackageroles] TO [db_ssisltduser]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackageroles] TO [db_ssisadmin]
GRANT  EXECUTE  ON [dbo].[sp_ssis_getpackageroles] TO [db_ssisltduser]
GO

GRANT  ALL      ON [dbo].[sysssislog]            TO [db_ssisadmin]
GRANT  INSERT   ON [dbo].[sysssislog]            TO [db_ssisltduser]
GRANT  SELECT   ON [dbo].[sysssislog]            TO [db_ssisltduser]
GRANT  INSERT   ON [dbo].[sysssislog]            TO [db_ssisoperator]
GRANT  SELECT   ON [dbo].[sysssislog]            TO [db_ssisoperator]
GO

-- Maintenance Plans
 -- Allow SQLAgent on target servers to gather information about
 -- maintenance plans from the master.
GRANT EXECUTE ON sp_maintplan_subplans_by_job  TO SQLAgentUserRole
GRANT EXECUTE ON sp_maintplan_subplans_by_job  TO TargetServersRole


/**************************************************************/
/*                                                            */
/*  D  A  T  A     C  O  L  L  E  C  T  O  R                  */
/*                                                            */
/**************************************************************/
USE msdb
GO

---------------------------------------------------------------
-- Data Collector: Security: Database Principals
---------------------------------------------------------------

PRINT ''
PRINT 'Create dc_operator role...'
IF ( NOT EXISTS (SELECT * FROM sys.database_principals 
                    WHERE name = N'dc_operator' AND type = 'R'))
BEGIN
    CREATE ROLE [dc_operator]
END
ELSE -- if the role exists check to see if it has members
BEGIN
    IF NOT EXISTS (SELECT rm.member_principal_id
                FROM sys.database_principals dp 
                INNER JOIN sys.database_role_members rm ON rm.role_principal_id = dp.principal_id
                WHERE name = N'dc_operator' AND type = 'R')
    BEGIN
        -- if the role has no members drop and recreate it
        DROP ROLE [dc_operator]
        CREATE ROLE [dc_operator]
    END
END
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisltduser' , 
                   @membername = 'dc_operator' 
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisoperator' , 
                   @membername = 'dc_operator' 
GO

EXECUTE sp_addrolemember @rolename = 'SQLAgentUserRole' , 
                   @membername = 'dc_operator' 
GO

PRINT ''
PRINT 'Create dc_admin role...'
IF ( NOT EXISTS (SELECT * FROM sys.database_principals 
                    WHERE name = N'dc_admin' AND type = 'R'))
BEGIN
    CREATE ROLE [dc_admin]
END
ELSE -- if the role exists check to see if it has members
BEGIN
    IF NOT EXISTS (SELECT rm.member_principal_id
                FROM sys.database_principals dp 
                INNER JOIN sys.database_role_members rm ON rm.role_principal_id = dp.principal_id
                WHERE name = N'dc_admin' AND type = 'R')
    BEGIN
        -- if the role has no members drop and recreate it
        DROP ROLE [dc_admin]
        CREATE ROLE [dc_admin]
    END
END
GO

EXECUTE sp_addrolemember @rolename = 'dc_operator' , 
                   @membername = 'dc_admin' 
GO

PRINT ''
PRINT 'Create dc_proxy role...'
IF ( NOT EXISTS (SELECT * FROM sys.database_principals 
                    WHERE name = N'dc_proxy' AND type = 'R'))
BEGIN
    CREATE ROLE [dc_proxy]
END
ELSE -- if the role exists check to see if it has members
BEGIN
    IF NOT EXISTS (SELECT rm.member_principal_id
                FROM sys.database_principals dp 
                INNER JOIN sys.database_role_members rm ON rm.role_principal_id = dp.principal_id
                WHERE name = N'dc_proxy' AND type = 'R')
    BEGIN
        -- if the role has no members drop and recreate it
        DROP ROLE [dc_proxy]
        CREATE ROLE [dc_proxy]
    END
END
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisltduser' , 
                   @membername = 'dc_proxy' 
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisoperator' , 
                   @membername = 'dc_proxy' 
GO

PRINT ''
PRINT 'Create loginless user that has ownership of data collector agent-related securables...'
IF (NOT EXISTS(SELECT * FROM sys.database_principals WHERE NAME = 'MS_DataCollectorInternalUser'))
BEGIN
    CREATE USER [MS_DataCollectorInternalUser] WITHOUT LOGIN
END
GO

EXECUTE sp_addrolemember @rolename = 'db_ssisoperator' , 
                   @membername = 'MS_DataCollectorInternalUser' 
GO

EXECUTE sp_addrolemember @rolename = 'SQLAgentUserRole' , 
                   @membername = 'MS_DataCollectorInternalUser' 
GO

EXECUTE sp_addrolemember @rolename = 'dc_admin' , 
                   @membername = 'MS_DataCollectorInternalUser' 
GO

GRANT IMPERSONATE ON USER::[MS_DataCollectorInternalUser] TO [dc_admin];
GO

---------------------------------------------------------------
-- Configuration store
---------------------------------------------------------------
IF (OBJECT_ID(N'[dbo].[syscollector_config_store_internal]', 'U') IS NULL)
BEGIN
    PRINT 'Creating table [dbo].[syscollector_config_store_internal]...'
