Setup Database Mail

2019-07-25 · by Hannah Vernon · in configuration, SQL Server Agent

SQL Server includes a great feature known as Database Mail. This post provides a quick script showing how to setup Database Mail without requiring you to click through a cumbersome user interface. The first part of the script needs to be customized by you so emails will be sent to the correct address.

The Fair Ophelia by Benjamin West – more direct than a database mail setup!

The @EmailUsername and @EmailPassword variables should be set to NULL if your email server doesn’t require you to sign in to send an email. If sign-in is required, ensure you use a valid email address and password. The @MailServerPort is typically either 25 or 465.

/*
    Sets up Database Mail
    Hannah Vernon - https://www.sqlserverscience.com/configuration/setup-database-mail
*/
DECLARE @DatabaseMailAcct sysname = N'DBA';
DECLARE @DBAEmailAddress sysname = N'your_email_address@server.domain';
DECLARE @DBAEmailName sysname = N'DBA';
DECLARE @MailServerName sysname = N'mailserver.server.domain';
DECLARE @MailServerType sysname = N'SMTP';
DECLARE @MailServerPort int = 25;
DECLARE @MailServerUsesSSL bit = 0;
DECLARE @EmailUsername sysname = N'user@org';
DECLARE @EmailPassword sysname = N'password';

--no need to modify anything below this point.
DECLARE @ProfileID int;
DECLARE @AccountID int;
DECLARE @SequenceNumber int;
DECLARE @DatabaseMailEnabled bit;

SET @DatabaseMailEnabled = (
    SELECT CONVERT(bit, c.value_in_use)
    FROM sys.configurations c
    WHERE c.name = N'Database Mail XPs'
    );
IF @DatabaseMailEnabled = 0
BEGIN
    EXEC sys.sp_configure @configname = 'Show Advanced Options', @configvalue = 1;
    RECONFIGURE
    EXEC sys.sp_configure @configname = 'Database Mail XPs', @configvalue = 1;
    RECONFIGURE
END

IF NOT EXISTS (
    SELECT 1
    FROM msdb.dbo.sysmail_profile sp
    WHERE sp.name = N'DBA'
    )
BEGIN
    EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = N'DBA'
        , @description = N'DBA Mail Profile'
        , @profile_id = @ProfileID OUTPUT;
END
ELSE
BEGIN
    SET @ProfileID = (
        SELECT profile_id 
        FROM msdb.dbo.sysmail_profile smp 
        WHERE smp.name = N'DBA'
        );
END

IF NOT EXISTS (
    SELECT 1
    FROM msdb.dbo.sysmail_account sma
    WHERE sma.name = N'DBA'
    )
BEGIN
    EXEC msdb.dbo.sysmail_add_account_sp @account_name = @DatabaseMailAcct
        , @email_address = @DBAEmailAddress
        , @display_name = @DBAEmailName
        , @mailserver_name = @MailServerName
        , @mailserver_type = @MailServerType
        , @port = @MailServerPort
        , @username = @EmailUsername
        , @password = @EmailPassword
        , @enable_ssl = @MailServerUsesSSL
        , @account_id = @AccountID OUTPUT;
END
ELSE
BEGIN
    SET @AccountID = (
        SELECT account_id
        FROM msdb.dbo.sysmail_account sma
        WHERE sma.name = N'DBA'
        );
END

SET @SequenceNumber = COALESCE(
    (
        SELECT count(1)
        FROM msdb.dbo.sysmail_profileaccount spa
        WHERE spa.profile_id = @ProfileID
    )
    , 0) + 1;

IF NOT EXISTS (
    SELECT 1
    FROM msdb.dbo.sysmail_profileaccount spa
    WHERE spa.account_id = @AccountID
        AND spa.profile_id = @ProfileID
    )
BEGIN
    EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_id = @ProfileID
        , @account_id = @AccountID
        , @sequence_number = 1;
END

IF NOT EXISTS (
    SELECT 1
    FROM msdb.dbo.sysoperators so
    WHERE so.name = N'DBA'
    )
BEGIN
    EXEC msdb.dbo.sp_add_operator @name = N'DBA'
        , @enabled = 1
        , @email_address = @DBAEmailAddress;
END

EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_start_sp;

DECLARE @body nvarchar(max) = N'This is a test email to verify that Database Mail is setup correctly on ' + @@SERVERNAME;
EXEC msdb.dbo.sp_send_dbmail @profile_name = N'DBA'
    , @recipients = @DBAEmailAddress
    , @subject = N'This is a test email'
    , @body = @body;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

Sets up Database Mail

Hannah Vernon - https://www.sqlserverscience.com/configuration/setup-database-mail

DECLARE @DatabaseMailAcct sysname = N'DBA';

DECLARE @DBAEmailAddress sysname = N'your_email_address@server.domain';

DECLARE @DBAEmailName sysname = N'DBA';

DECLARE @MailServerName sysname = N'mailserver.server.domain';

DECLARE @MailServerType sysname = N'SMTP';

DECLARE @MailServerPort int = 25;

DECLARE @MailServerUsesSSL bit = 0;

DECLARE @EmailUsername sysname = N'user@org';

DECLARE @EmailPassword sysname = N'password';

--no need to modify anything below this point.

DECLARE @ProfileID int;

DECLARE @AccountID int;

DECLARE @SequenceNumber int;

DECLARE @DatabaseMailEnabled bit;

SET @DatabaseMailEnabled = (

SELECT CONVERT(bit, c.value_in_use)

FROM sys.configurations c

WHERE c.name = N'Database Mail XPs'

);

IF @DatabaseMailEnabled = 0

BEGIN

EXEC sys.sp_configure @configname = 'Show Advanced Options', @configvalue = 1;

RECONFIGURE

EXEC sys.sp_configure @configname = 'Database Mail XPs', @configvalue = 1;

RECONFIGURE

END

IF NOT EXISTS (

SELECT 1

FROM msdb.dbo.sysmail_profile sp

WHERE sp.name = N'DBA'

)

BEGIN

EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = N'DBA'

, @description = N'DBA Mail Profile'

, @profile_id = @ProfileID OUTPUT;

END

ELSE

BEGIN

SET @ProfileID = (

SELECT profile_id

FROM msdb.dbo.sysmail_profile smp

WHERE smp.name = N'DBA'

);

END

IF NOT EXISTS (

SELECT 1

FROM msdb.dbo.sysmail_account sma

WHERE sma.name = N'DBA'

)

BEGIN

EXEC msdb.dbo.sysmail_add_account_sp @account_name = @DatabaseMailAcct

, @email_address = @DBAEmailAddress

, @display_name = @DBAEmailName

, @mailserver_name = @MailServerName

, @mailserver_type = @MailServerType

, @port = @MailServerPort

, @username = @EmailUsername

, @password = @EmailPassword

, @enable_ssl = @MailServerUsesSSL

, @account_id = @AccountID OUTPUT;

END

ELSE

BEGIN

SET @AccountID = (

SELECT account_id

FROM msdb.dbo.sysmail_account sma

WHERE sma.name = N'DBA'

);

END

SET @SequenceNumber = COALESCE(

(

SELECT count(1)

FROM msdb.dbo.sysmail_profileaccount spa

WHERE spa.profile_id = @ProfileID

)

, 0) + 1;

IF NOT EXISTS (

SELECT 1

FROM msdb.dbo.sysmail_profileaccount spa

WHERE spa.account_id = @AccountID

AND spa.profile_id = @ProfileID

)

BEGIN

EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_id = @ProfileID

, @account_id = @AccountID

, @sequence_number = 1;

END

IF NOT EXISTS (

SELECT 1

FROM msdb.dbo.sysoperators so

WHERE so.name = N'DBA'

)

BEGIN

EXEC msdb.dbo.sp_add_operator @name = N'DBA'

, @enabled = 1

, @email_address = @DBAEmailAddress;

END

EXEC msdb.dbo.sysmail_stop_sp;

EXEC msdb.dbo.sysmail_start_sp;

DECLARE @body nvarchar(max) = N'This is a test email to verify that Database Mail is setup correctly on ' + @@SERVERNAME;

EXEC msdb.dbo.sp_send_dbmail @profile_name = N'DBA'

, @recipients = @DBAEmailAddress

, @subject = N'This is a test email'

, @body = @body;

This query shows the Database Mail Log. Use it to check if the test email was sent correctly.

SELECT 
      LogDate = sl.log_date
    , EventType = CASE 
        WHEN sl.event_type = 0 THEN 'Success' 
        WHEN sl.event_type = 1 THEN 'Information' 
        WHEN sl.event_type = 2 THEN 'Warning' 
        WHEN sl.event_type = 3 THEN 'Error'
    END 
    , [Description] = sl.[description]
FROM msdb.dbo.sysmail_log sl
ORDER BY sl.log_id DESC;

SELECT

LogDate = sl.log_date

, EventType = CASE

WHEN sl.event_type = 0 THEN 'Success'

WHEN sl.event_type = 1 THEN 'Information'

WHEN sl.event_type = 2 THEN 'Warning'

WHEN sl.event_type = 3 THEN 'Error'

END

, [Description] = sl.[description]

FROM msdb.dbo.sysmail_log sl

ORDER BY sl.log_id DESC;

Check out the rest of our posts on configuring SQL Server. Click here for details from Microsoft about Database Mail.

Setup Database Mail

Related

Leave a ReplyCancel reply

Search

Categories

Pages

Meta