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

SQL Server Agent – Message – “Failed to notify…”

Background

I have been tending to a job that often fails, and wanted to add email notification, for the last couple of weeks.

It is bad that enough that the job itself fails, but worse no email.

 

Monday Morning

Monday Morning is often a good day of the week to make use of another carefree weekend and see where the error lies.

Error Message

Image

 

Textual

Message
The job succeeded. The Job was invoked by Schedule 25 (Every Day). The last step to run was step 12 (Say Completed). NOTE: Failed to notify ‘Daniel Adeniji’ via email.

 

TroubleShooting

SQL Server Agent Error Log

here is what is logged in log file for SQL Server Agent.

Please note we are referencing SQL Server Agent log files, and not SQL Server Engine.

Image

Textual

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail

 

Remediate

Access SQL Server Agent Properties and set SQL Mail…

GUI

Unset

When Unset

Here is how things look before configuring SQL Server Agent Mail Profile

 

Set

Us setting SQL Server Agent Mail Profile

Script

Read SQL Server Agent Properties

Code


declare @useDatabasemail     int
declare @DatabaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @useDatabasemail OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @DatabaseMailProfile OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @AgentMailType OUT
        , @no_output = N'no_output'



--NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
EXEC master.sys.xp_instance_regread 
          'HKEY_LOCAL_MACHINE'
        , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
        , N'Start'
        , @ServiceStartMode OUTPUT



--declare @ServiceAccount nvarchar(512)
EXEC master.sys.xp_instance_regread
           'HKEY_LOCAL_MACHINE'
         , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
         , N'ObjectName'
         , @ServiceAccount OUTPUT


exec master.dbo.xp_instance_regread 
              N'HKEY_LOCAL_MACHINE'
            , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
            , N'AGTGroup'
            , @AgtGroup OUTPUT

select 
          [@use_databasemail] = @useDatabasemail
        , [@DatabaseMailProfile] = @DatabaseMailProfile      
        , [@AgentMailType] = @AgentMailType
        , [@ServiceStartMode] = @ServiceStartMode
        -- NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
        , [@ServiceStartModeLiteral] 
                = case  
                        when @ServiceStartMode=2  then 'Auto Start'
                        when @ServiceStartMode=3  then 'Don''t Auto Start'
                  end   
        , [@ServiceAccount] = @ServiceAccount
        --, [@AgtGroup] = @AgtGroup

Output

Set SQL Server Agent Properties

Code


USE [msdb]
GO

set nocount on
set XACT_ABORT on
go

declare @sqlInstance                sysname
declare @databaseMailProfileDesired sysname

declare @FORMAT_MESSAGE_SET         nvarchar(600)
declare @message                    nvarchar(600)

declare @databaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

declare @commit              bit

set @commit =1
set @sqlInstance = cast(SERVERPROPERTY('servername') as sysname)
set @databaseMailProfileDesired = N'DBAMail'

set @FORMAT_MESSAGE_SET = 'On %s, setting database mail profile to %s'

/*
    Access Registry (SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent') 
        a) Read Database Mail Profile
*/
exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @databaseMailProfile OUT
        , @no_output = N'no_output'

print '@DatabaseMailProfile :' + isNull(@DatabaseMailProfile, '')

begin tran
    

    if  
        ( @DatabaseMailProfile is null )
    begin

        exec master.dbo.xp_sprintf      
                  @message output
                , @FORMAT_MESSAGE_SET
                , @sqlInstance
                , @databaseMailProfileDesired

        print @message

        /*
            Set SQL Sql Server Agent Properties
        */
        EXEC msdb.dbo.sp_set_sqlagent_properties 
                  @email_save_in_sent_folder=1
                , @databasemail_profile=@databaseMailProfileDesired
                , @use_databasemail=1
                , @alert_replace_runtime_tokens=1


    end

while (@@TRANCOUNT > 0)
begin

    if (@commit = 1)
    begin

        print 'commit tran'

        commit tran

    end
    else
    begin

        print 'rollback tran'

        rollback tran

    end
end

GO

Output

Reset SQL Server Agent Properties

To revert changes, please run the script below.


USE [msdb]
GO

EXEC msdb.dbo.sp_set_sqlagent_properties
          @email_profile=N'' 
        , @databasemail_profile=N''
        , @use_databasemail  =0
GO


Listening

Listening to …

Jazmine Sullivan – In Love With Another Man
Link

 

Sql Server – SQL Server Agent – Optimizing DB Traffic

Background

In our last post, we spoke about how MSDB is responsible for some of our Single use queries.

Let us dig more in by identifying some of those queries and see if we can change, modify, or reduce their frequency.

 

MSDB – Queries

We have identified a couple of queries and here they are:

  1. msdb.dbo.sp_sqlagent_get_perf_counters
  2. msdb.dbo.sysmail_help_profile_*

 

SQL – msdb.dbo.sp_sqlagent_get_perf_counters

SQL Server Profiler

SQL Server Profiler – Captured

Image

sqlserverprofiler-20160908-0334pm

 

Textual

  1. Text Data :- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
  2. Application Name: – SQLAgent – Alert Engine

 

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters”
    • Every 20 seconds

 

Remediation

Steps

  1. Script out all current SQL Server Agent Alerts
  2. Remove all the alerts
    • Please keep in mind that it is not enough to disable them
Before

sqlserveragentalerts-20160908-0254pm

 

SQL Server Agent – Mail

SQL Server Profiler

Image

sqlserverprofiler-20160908-0426pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N'Local Relay Server'
exec msdb.dbo.sysmail_help_profileaccount_sp @profile_id=1
exec msdb.dbo.sysmail_help_admin_account_sp @account_id=1

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help*” commands
    • Every 5 minutes

Remediation

Steps

  1. Access SQL Server Agent Properties
  2. Access the “Alert System” Tab
  3. Disable “Enable Email profile”
Before

alertsystem-before

 

After
alertsystem-after

 

SQL Server Profiler ( After)

Image

sqlserverprofiler-20160908-0437pm

Textual


exec msdb.dbo.sysmail_help_profile_sp @profile_name=N''

SQL Server Profiler – Explanation

  1. SQL Server Agent issues “EXECUTE msdb.dbo.sysmail_help_profile_sp” commands
    • Every 5 minutes
    • Captured Output
      • Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid 

Summary

If you are not using SQL Server Agent for alerts and email notification services, you might be able to get away with removing alerts and disabling the email profile setup for SQL Server Agent.

Again, please help in mind we are specifically targeting a v2005 SQL instance.