SQL Server – Scripting Database Mail Configurations

Background

Need to make sure that a couple of SQL Server Instances have the same configuration.

 

Sample Code

Sought out sample code on the Net and found this one:

Mohammad Abdul Majeed
Script to Script out EXISTING database mail settings
Link

Code

Outline

What are we scripting…

  1. Database Mail Components
    • Mail Profile
    • Mail Account
    • Mail Profile & Account binding

 

Stored Procedures

email.sp_DatabaseMailSetupScript

Script


use master
go

/*

    email.sp_scriptDatabaseMailSetup

*/

if schema_id('email') is null
begin

    exec('create schema [email] authorization [dbo]');

end
go

if object_id('[email].[sp_scriptDatabaseMailSetup]') is not null
begin

    drop procedure [email].[sp_scriptDatabaseMailSetup]

end
go

if object_id('[email].[sp_DatabaseMailSetupScript]') is null
begin

    exec('create procedure [email].[sp_DatabaseMailSetupScript] as print ''shell'' ')

end
go

alter procedure [email].[sp_DatabaseMailSetupScript] 
(
      @profile  sysname = null
    , @account  sysname = null
)
as
begin

    /*

        Referenced Work

        a) Mohammad Abdul Majeed
           Script to Scipt out EXISTING database mail settings
           https://gallery.technet.microsoft.com/scriptcenter/Script-to-Scipt-out-14a19eda

    */

    set nocount on;
    set XACT_ABORT on;

    declare @CHAR_TAB               varchar(10)
    declare @CHAR_SPACE             varchar(10)
    declare @CHAR_COMMA             varchar(10)
    declare @CHAR_NEWLINE           varchar(10)
    declare @CHAR_NULL              varchar(10)
    declare @CHAR_QUOTE             varchar(10)
    declare @CHAR_ZERO              varchar(10)
    declare @CHAR_COMMENT           varchar(10)
    
    declare @MAIL_PROFILE_CREATE        nvarchar(600)
    declare @MAIL_ACCOUNT_CREATE        nvarchar(4000)
    declare @MAIL_PROFILE_ACCOUNT_BIND  nvarchar(600)

    declare @CHAR_BEGIN             varchar(10)
    declare @CHAR_END               varchar(10)

    set @CHAR_TAB = char(9);
    set @CHAR_NEWLINE = char(13) + char(10);

    set @CHAR_SPACE = ' ';
    set @CHAR_COMMA = ',';
    set @CHAR_NULL = ' null';
    set @CHAR_QUOTE = ''''
    set @CHAR_ZERO = 0
    set @CHAR_COMMENT = '--';

    set @CHAR_BEGIN = 'begin';
    set @CHAR_END = 'end';

    /*
        exec msdb.dbo.sysmail_help_profile_sp
    */
    set @MAIL_PROFILE_CREATE 
            = 'if not exists ( SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''%s'' ) '
                + @CHAR_BEGIN   
                + @CHAR_TAB
                + ' EXECUTE msdb.dbo.sysmail_add_profile_sp '   
                + @CHAR_TAB + @CHAR_TAB + '   @profile_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @description = ''%s'' '   
                --+ @CHAR_TAB
                + @CHAR_END             

    -- use [msdb]; exec sp_help 'dbo.sysmail_add_account_sp' 
    -- exec [msdb].dbo.sysmail_help_account_sp
    set @MAIL_ACCOUNT_CREATE 
            = 'if not exists ( SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''%s'' ) '
                + @CHAR_BEGIN       
                + @CHAR_NEWLINE 
                + ' EXECUTE msdb.dbo.sysmail_add_account_sp '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_SPACE + '@account_name = ''%s'' '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@email_address = ''%s'' '  
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@display_name  = ''%s'' '  
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@replyto_address  = ''%s'' '
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@description  = ''%s'' '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@mailserver_name  = ''%s'' '       
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@mailserver_type  = ''%s'' '   

                /*
                    Error: 50000, Severity: -1, State: 1. (Params:). 
                    The error is printed in terse mode because there was error during formatting. 
                    Tracing, ETW, notifications etc are skipped.  
                */
                --+ @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@port  = ''%s'' '        
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@port  = %d '  
            
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@username  = %s '  
            
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@use_default_credentials  = %d '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@enable_ssl  = %d '    

                /*
                    + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@account_id = %d ' 

                */

                + @CHAR_NEWLINE 
            
                + @CHAR_END 

    -- exec msdb..sysmail_configure_sp
    set @MAIL_PROFILE_ACCOUNT_BIND 
            = 'if not exists '
                + ' ( '
                + ' SELECT * '
                + ' FROM   msdb.dbo.sysmail_profileaccount tblSMPA '
                + ' inner join  msdb.dbo.sysmail_profile tblSMP '
                + '       on tblSMPA.[profile_id] = tblSMP.[profile_id] '
                + ' inner join msdb.dbo.sysmail_account tblSMA '
                + '       on tblSMPA.[account_id] = tblSMA.[account_id] '
                + ' WHERE tblSMP.[name] = ''%s'' '
                + ' and   tblSMA.[name] = ''%s'' '
                + ' ) '
                + @CHAR_BEGIN   
                + @CHAR_TAB
                + ' EXECUTE  msdb.dbo.sysmail_add_profileaccount_sp  '  
                + @CHAR_TAB + @CHAR_TAB + '   @profile_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @account_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @sequence_number = %d '   
                --+ @CHAR_TAB
                + @CHAR_END     

    SELECT 
               = 'Mail Profile'
            , [profileID] = tblSMP.[profile_id]
            , [profileName] = tblSMP.[name]
            , [description] = tblSMP.[description]
            , [profileModifiedBy] = tblSMP.[last_mod_user]
            , [profileModifyDate] = tblSMP.[last_mod_datetime]
            , [script] = FORMATMESSAGE
                            (
                                  @MAIL_PROFILE_CREATE
                                , tblSMP.[name] -- profile name
                                , tblSMP.[name]  -- profile name
                                , isNull(tblSMP.[description], '') -- profile description
                            )

    FROM   msdb.dbo.sysmail_profile tblSMP

    where  tblSMP.[name] = case

                                when ( @profile is null) then tblSMP.[name]
                                when ( @profile = '') then tblSMP.[name]
                                else @profile
                           end       

    -- select * from msdb.dbo.sysmail_server 
    SELECT 
               = 'Mail Account'
            , [accountID] = tblSMA.[account_id]

            , [account] = tblSMA.[name]
            , [emailAddress] = tblSMA.[email_address]

            , [displayName] = tblSMA.[display_name]
            , [replyToAddress] = tblSMA.[replyto_address]

            , [description] = tblSMA.[description]

            , [mailServerName] = tblSMS.[servername]
            , [mailServerType] = tblSMS.servertype
            , [mailServerPort] = tblSMS.[port]

            , [useDefaultCredentials] = tblSMS.use_default_credentials  
            

            , [accountModifiedBy] = tblSMA.[last_mod_user]
            , [accountModifyDate] = tblSMA.[last_mod_datetime]

            , [script] = FORMATMESSAGE
                            (
                                  @MAIL_ACCOUNT_CREATE
                                , tblSMA.[name] -- account name
                                , tblSMA.[name]  -- account name
                                , isNull(tblSMA.[email_address], @CHAR_SPACE) 
                                , isNull(tblSMA.[display_name], @CHAR_SPACE) 
                                , isNull(tblSMA.[replyto_address], @CHAR_SPACE) 
                                , isNull(tblSMA.[description], @CHAR_SPACE) 
                                , isNull(tblSMS.servername, @CHAR_SPACE) 
                                , isNull(tblSMS.servertype, @CHAR_SPACE)
                                , isNull(tblSMS.[port], @CHAR_SPACE)
                             
                                , case
                                    when tblSMS.[username] is null then @CHAR_NULL
                                    else @CHAR_QUOTE + tblSMS.[username] + @CHAR_QUOTE
                                  end

                                  /*
                                    Msg 2748, Level 16, State 1, Line 132
                                    Cannot specify bit data type (parameter 11) as a substitution parameter.
                                  */
                                , case 
                                    when tblSMS.[use_default_credentials] is null then @CHAR_ZERO
                                    else cast(tblSMS.[use_default_credentials] as tinyint)
                                  end
                                    
                                , case

                                    when tblSMS.[enable_ssl] is null then @CHAR_ZERO
                                    else cast(tblSMS.[enable_ssl] as tinyint)
                            
                                  end       

                                /*

                                    , case

                                        when tblSMA.[account_id] is null then @CHAR_ZERO
                                        else tblSMA.[account_id]
                            
                                      end                               

                                */
                                 
                            )

    FROM   msdb.dbo.sysmail_account tblSMA

    LEFT OUTER JOIN msdb.dbo.sysmail_server tblSMS
     
            ON tblSMA.account_id = tblSMS.account_id 

    where  tblSMA.[name] = case

                                when ( @account is null) then tblSMA.[name]
                                when ( @account = '') then tblSMA.[name]
                                else @account
                           end  
                            
    SELECT 
               = 'Mail Profile Account Binding'
            , [profileID] = tblMPA.profile_id
            , [profile] = tblSMP.[name]

            , [accountID] = tblMPA.account_id
            , [account] = tblSMA.[name] 

            , [sequenceNumber] = tblMPA.[sequence_number]
            , [script] = FORMATMESSAGE
                            (
                                 @MAIL_PROFILE_ACCOUNT_BIND

                                , tblSMP.[name] -- profile name
                                , tblSMA.[name] -- account name

                                , tblSMP.[name] -- profile name
                                , tblSMA.[name] -- account name
                             

                                  /*
                                    Msg 2748, Level 16, State 1, Line 132
                                    Cannot specify bit data type (parameter 11) as a substitution parameter.
                                  */
                                , case 
                                    when tblMPA.sequence_number is null then @CHAR_ZERO
                                    else cast(tblMPA.sequence_number as tinyint)
                                  end
                                 
                            )

    FROM msdb.dbo.sysmail_profileaccount tblMPA 
    
    INNER JOIN msdb.dbo.sysmail_profile tblSMP 
        ON tblMPA.profile_id = tblSMP.profile_id 
    
    INNER JOIN msdb.dbo.sysmail_account tblSMA
        ON tblMPA.account_id = tblSMA.account_id

    where  tblSMP.[name] = case

                                when ( @profile is null) then tblSMP.[name]
                                when ( @profile = '') then tblSMP.[name]
                                else @profile
                           end  

    and  tblSMA.[name] = case

                                when ( @account is null) then tblSMA.[name]
                                when ( @account = '') then tblSMA.[name]
                                else @account
                           end  

end
go


 

Invoke



declare @profile sysname
declare @account sysname
 
exec [email].[sp_DatabaseMailSetupScript] 
          @profile = @profile
        , @account = @account


Output

 

Source Control

Github

  1. Project
    • DanielAdeniji/SQLServerDatabaseMailConfiguration
      Link
  2. Files
    • email.sp_DatabaseMailSetupScript.sql
      Link

 

Dedicated

Dedicated to Mohammad Abdul Majeed

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s