March 19th, 2015
13
Maintenance Database Monitoring SQL Server Query Development

SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail)

Views: 29.037 views

In this article

Reading Time: 11 minutes

Many people ask me, "Is it possible to send emails via SQL Server?" or “How do I send emails via SQL Server?”. In this post I will show you how to enable the sending of emails on your SQL Server and how to send and monitor the emails sent.

A feature that is widely used in most systems, is the sending of automatic emails containing notifications, promotions, newsletter and much more. Therefore, it would be very interesting to send these emails directly from our SP's in the database, which can be scheduled by the SQL Server Agent to automate their execution.

Do not forget that for the user to be able to send email through SQL Server, he must be in the database role DatabaseMailUserRole msdb database (or elevated permissions, such as msdb owner, sysadmin, etc.)

Enabling server e-mail sending using T-SQL

View how to enable and configure Database mail via command line (using T-SQL)

By default, SQL Server comes with email sending options disabled as a security measure. I will show below how to enable these options:

-----------------------------------------------------------------------------------------
-- Habilita o envio de e-mail no servidor
-----------------------------------------------------------------------------------------

sp_configure 'show advanced options', 1;
GO

RECONFIGURE
GO

sp_configure 'Database Mail XPs', 1;
GO

RECONFIGURE
GO


-----------------------------------------------------------------------------------------
-- Cria uma conta de envio de e-mail no banco de dados
-----------------------------------------------------------------------------------------

DECLARE
    @Account_Name SYSNAME = 'ContaEnvioEmail',
    @Profile_Name SYSNAME = 'ProfileEnvioEmail'
    

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_account WHERE name = @Account_Name) > 0)
    EXEC msdb.dbo.sysmail_delete_account_sp @account_name = @Account_Name


EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = @Account_Name,
    @description = 'Conta de e-mail para ser utilizada por todos os usuários do banco',
    @email_address = 'usuario@seudominio.com.br',
    @replyto_address = 'naoresponder@seudominio.com.br',
    @display_name = 'Sua Empresa',
    @mailserver_name = 'smtp.seudominio.com.br',
    @mailserver_type = 'SMTP',
    @port = '587',
    @username = 'usuario@seudominio.com.br',
    @password = 'senha',
    @enable_ssl = 1,
    @use_default_credentials = 0



-----------------------------------------------------------------------------------------
-- Cria o profile de e-mail
-----------------------------------------------------------------------------------------

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_profile WHERE name = @Profile_Name) > 0)
    EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = @Profile_Name



EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = @Profile_Name,
    @description = 'Profile Público para Envio de E-mail' ;


-----------------------------------------------------------------------------------------
-- Adiciona a conta ao perfil criado
-----------------------------------------------------------------------------------------

DECLARE 
    @profile_id INT = (SELECT profile_id FROM msdb.dbo.sysmail_profile WHERE name = @Profile_Name), 
    @account_id INT = (SELECT account_id FROM msdb.dbo.sysmail_account WHERE name = @Account_Name)
    

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_profileaccount WHERE account_id = @account_id AND profile_id = @profile_id) > 0)
    EXEC msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = @Profile_Name, @account_name = @Account_Name


EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @Profile_Name,
    @account_name = @Account_Name,
    @sequence_number = 1;


-----------------------------------------------------------------------------------------
-- Libera acesso no perfil criado para todos os usuários
-----------------------------------------------------------------------------------------

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_principalprofile WHERE profile_id = @profile_id) > 0)
    EXEC msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = @Profile_Name


EXEC msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = @Profile_Name,
    @principal_name = 'public', -- Aqui você pode dar acesso para um usuário específico, se quiser
    @is_default = 1;


-----------------------------------------------------------------------------------------
-- Define o tamanho máximo por anexo para 5 MB (O Padrão é 1 MB por arquivo)
-----------------------------------------------------------------------------------------

EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize', '5242880'; -- 1024 x 1024 x 5

-----------------------------------------------------------------------------------------

-- Habilita o envio de e-mail no servidor

-----------------------------------------------------------------------------------------

sp_configure 'show advanced options', 1;

RECONFIGURE

sp_configure 'Database Mail XPs', 1;

RECONFIGURE

-----------------------------------------------------------------------------------------

-- Cria uma conta de envio de e-mail no banco de dados

-----------------------------------------------------------------------------------------

DECLARE

@Account_Name SYSNAME = 'ContaEnvioEmail',

@Profile_Name SYSNAME = 'ProfileEnvioEmail'

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_account WHERE name = @Account_Name) > 0)

EXEC msdb.dbo.sysmail_delete_account_sp @account_name = @Account_Name

EXEC msdb.dbo.sysmail_add_account_sp

@account_name = @Account_Name,

@description = 'Conta de e-mail para ser utilizada por todos os usuários do banco',

@email_address = '[email protected]',

@replyto_address = '[email protected]',

@display_name = 'Sua Empresa',

@mailserver_name = 'smtp.seudominio.com.br',

@mailserver_type = 'SMTP',

@port = '587',

@username = '[email protected]',

@password = 'senha',

@enable_ssl = 1,

@use_default_credentials = 0

-----------------------------------------------------------------------------------------

-- Cria o profile de e-mail

-----------------------------------------------------------------------------------------

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_profile WHERE name = @Profile_Name) > 0)

EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = @Profile_Name

EXEC msdb.dbo.sysmail_add_profile_sp

@profile_name = @Profile_Name,

@description = 'Profile Público para Envio de E-mail' ;

-----------------------------------------------------------------------------------------

-- Adiciona a conta ao perfil criado

-----------------------------------------------------------------------------------------

DECLARE

@profile_id INT = (SELECT profile_id FROM msdb.dbo.sysmail_profile WHERE name = @Profile_Name),

@account_id INT = (SELECT account_id FROM msdb.dbo.sysmail_account WHERE name = @Account_Name)

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_profileaccount WHERE account_id = @account_id AND profile_id = @profile_id) > 0)

EXEC msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = @Profile_Name, @account_name = @Account_Name

EXEC msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @Profile_Name,

@account_name = @Account_Name,

@sequence_number = 1;

-----------------------------------------------------------------------------------------

-- Libera acesso no perfil criado para todos os usuários

-----------------------------------------------------------------------------------------

IF ((SELECT COUNT(*) FROM msdb.dbo.sysmail_principalprofile WHERE profile_id = @profile_id) > 0)

EXEC msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = @Profile_Name

EXEC msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = @Profile_Name,

@principal_name = 'public', -- Aqui você pode dar acesso para um usuário específico, se quiser

@is_default = 1;

-----------------------------------------------------------------------------------------

-- Define o tamanho máximo por anexo para 5 MB (O Padrão é 1 MB por arquivo)

-----------------------------------------------------------------------------------------

EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize', '5242880'; -- 1024 x 1024 x 5

Enabling SSMS to Send Mail on the Server

View how to configure Database Mail through Management Studio

Another way to enable and configure Database mail in SQL Server is through the SQL Server Management Studio interface.

1) Open the Object Explorer in Management Studio, right-click the mousse in the Management> Database mail option and select the option “Configure Database Mail”

2) You will see the screen below, which is just a presentation of Database Mail. If you prefer, you can even check the option “Skip this page in the future”, so that this screen no longer appears.

3) On this screen you will see 3 options. Initially select the first option (Set up Database Mail by performing the following tasks)

4) By default, the Database Mail feature is disabled on the SQL Server instance. When selecting the option “Set up Database Mail by performing the following tasks” on the previous screen, you will see this warning asking if you want to enable Database Mail. Click the “Yes” button

5) On this screen, you can define the name of the e-mail profile that will be needed to send the e-mails and add the SMTP accounts that you will use to perform the sending. Click on the “Add” button to add an SMTP account.

6) Here you will add your email account details, always remembering to check your email provider's SMTP address, port number, if the connection requires secure connection (SSL) and other authentication details.

7) Your SMTP account is now set up and you can now view it on the Profile SMTP accounts screen.

8) In this screen we will configure the security of the created email profile, ie define who can use it. You can mark this profile as public or private, where only a few users have access. I usually leave the profile public, but limit users' access to the sp_send_dbmail procedure via GRANT.

9) Here you can view and change Database Mail parameters such as maximum attachment size, prohibited attachment extensions, and email retry (which tries to send email again in case of failure)

10) And finally, you will see a summary of the changes that you have configured and that will be made in the instance after clicking on the “Finish” button.

11) Changes made successfully.

Testing email sending

After configuring Database Mail, we will test if the server is allowing sending normally. To do this, open Object Explorer in Management Studio, navigate to Management> Database Mail and right click on the option “Send Test E-mail…”

Just select the profile you created (In the example, it is the MSSQLServer), fill in the email of the recipient who will receive the test and click on the “Send Test E-mail” button

After submitting the test, a screen will open confirming that the email went to the Database Mail queue.

If you received the test email, just click on the “OK” button. If you haven't received it, click on the “Trobleshooting” button to be taken to the Microsoft help page (https://technet.microsoft.com/pt-br/library/ms187540(v=sql.105))

Sample test email sent by SQL Server Database Mail:

To view the Database Mail log and check if your emails were sent or the error message when trying to send, see more in the “Monitoring email sending” section of this post, below.

Sending Email

Now that we have set up the server, we are ready to send our emails. The sp we will use is sp_send_dbmail, from SQL Server itself, which allows sending text or HTML emails and attachments.

Sending Email in HTML Format

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = 'destinatario@seudominio.com.br',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = 'remetente@seudominio.com.br'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'ProfileEnvioEmail',

@recipients = '[email protected]',

@subject = 'Assunto do E-mail',

@body = 'Olá! Teste',

@body_format = 'html',

@from_address = '[email protected]'

Sending Email Concatenating the Result of a Query to the Message Body

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = 'destinatario@seudominio.com.br',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = 'remetente@seudominio.com.br',
    @query = 'SELECT TOP 10 * FROM sys.sysobjects'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'ProfileEnvioEmail',

@recipients = '[email protected]',

@subject = 'Assunto do E-mail',

@body = 'Olá! Teste',

@body_format = 'html',

@from_address = '[email protected]',

@query = 'SELECT TOP 10 * FROM sys.sysobjects'

Sending email with query result as attachment (CSV)

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = 'destinatario@seudominio.com.br',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = 'remetente@seudominio.com.br',
    @query = 'SET NOCOUNT ON; SELECT TOP 10 * FROM sys.sysobjects',
    @query_attachment_filename = 'anexo.csv',
    @attach_query_result_as_file = 1,
    @query_result_header = 1,
    @query_result_width = 256,
    @query_result_separator = ';',
    @query_result_no_padding = 1

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'ProfileEnvioEmail',

@recipients = '[email protected]',

@subject = 'Assunto do E-mail',

@body = 'Olá! Teste',

@body_format = 'html',

@from_address = '[email protected]',

@query = 'SET NOCOUNT ON; SELECT TOP 10 * FROM sys.sysobjects',

@query_attachment_filename = 'anexo.csv',

@attach_query_result_as_file = 1,

@query_result_header = 1,

@query_result_width = 256,

@query_result_separator = ';',

@query_result_no_padding = 1

Emailing 2 Files Attached

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileEnvioEmail',
    @recipients = 'destinatario@seudominio.com.br',
    @subject = 'Assunto do E-mail',
    @body = 'Olá! <strong>Teste</strong>',
    @body_format = 'html',
    @from_address = 'remetente@seudominio.com.br',
    @file_attachments = 'C:\Imagem.jpg;C:\Teste.txt'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'ProfileEnvioEmail',

@recipients = '[email protected]',

@subject = 'Assunto do E-mail',

@body = 'Olá! Teste',

@body_format = 'html',

@from_address = '[email protected]',

@file_attachments = 'C:\Imagem.jpg;C:\Teste.txt'

Monitoring the sending of emails

Tracking actions is always important in IT. Sending emails is no exception to the rule. As much as it is possible to monitor emails through the mail server, it is not always so fast to be able to validate this, mainly because it is a bit out of scope for the DBA / Scheduler, where this control is usually controlled by the Infrastructure team.

In addition, the mail server keeps logs of all email traffic from all accounts, making it harder to extract this information than simply querying SQL Server catalog tables, which are msdb.dbo.sysmail_mailitems and msdb.dbo.sysmail_event_log.

To make it easier to extract information, I usually create this view, and then just query it:

CREATE VIEW [dbo].[vwMonitoramento_Email] AS
SELECT
    A.send_request_date AS DataEnvio,
    A.sent_date AS DataEntrega,
    (CASE    
        WHEN A.sent_status = 0 THEN '0 - Aguardando envio'
        WHEN A.sent_status = 1 THEN '1 - Enviado'
        WHEN A.sent_status = 2 THEN '2 - Falhou'
        WHEN A.sent_status = 3 THEN '3 - Tentando novamente'
    END) AS Situacao,
    A.from_address AS Remetente,
    A.recipients AS Destinatario,
    A.subject AS Assunto,
    A.reply_to AS ResponderPara,
    A.body AS Mensagem,
    A.body_format AS Formato,
    A.importance AS Importancia,
    A.file_attachments AS Anexos,
    A.send_request_user AS Usuario,
    B.description AS Erro,
    B.log_date AS DataFalha
FROM 
    msdb.dbo.sysmail_mailitems                  A    WITH(NOLOCK)
    LEFT JOIN msdb.dbo.sysmail_event_log        B    WITH(NOLOCK)    ON A.mailitem_id = B.mailitem_id

CREATE VIEW [dbo].[vwMonitoramento_Email] AS

SELECT

A.send_request_date AS DataEnvio,

A.sent_date AS DataEntrega,

(CASE

WHEN A.sent_status = 0 THEN '0 - Aguardando envio'

WHEN A.sent_status = 1 THEN '1 - Enviado'

WHEN A.sent_status = 2 THEN '2 - Falhou'

WHEN A.sent_status = 3 THEN '3 - Tentando novamente'

END) AS Situacao,

A.from_address AS Remetente,

A.recipients AS Destinatario,

A.subject AS Assunto,

A.reply_to AS ResponderPara,

A.body AS Mensagem,

A.body_format AS Formato,

A.importance AS Importancia,

A.file_attachments AS Anexos,

A.send_request_user AS Usuario,

B.description AS Erro,

B.log_date AS DataFalha

FROM

msdb.dbo.sysmail_mailitems A WITH(NOLOCK)

LEFT JOIN msdb.dbo.sysmail_event_log B WITH(NOLOCK) ON A.mailitem_id = B.mailitem_id

Another way is by using the SQL Server Management Studio interface.

After selecting the “View Database Mail Log” option, you will see the Database Mail logs screen, in an interface similar to the SQL Server Activity Monitor

Quick Troubleshooting Help (Troubleshooting Database Mail)

If you're having trouble setting up or sending emails, here are some tips that can help you resolve these issues.

Validate access data

This topic may sound silly, but make sure that you correctly entered the SMTP server address, port, user, password, and if you checked the option to use SSL if your SMTP server requests it.

I recently had a big battle with Database Mail to set up one Yahoo account and one hosted on Hostgator. Although the documentation indicates that I should use the 465 (SSL) port in both cases, I have used an email client (Outlook) configured using the 465 port and sent email normally and made a push test using the 465 port on a VBscript successfully, in Database Mail I could only send when I put the 587 (TLS) port. On port 465 the email was not sent at all, probably because it was SSL protocol.

Therefore, consult your email provider's documentation, and if you have problems setting up Database Mail, try using other alternate ports (Providers generally provide two ports for secure SMTP sending, one SSL and one TLS).

Verify that Service Broker is enabled for database msdb (must return 1):

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

1	SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

If the query result is 0, then Service Broker is not active. As a result, sent messages will not be queued by it and sending will not occur. To fix this, run the command below:

USE master;
GO

ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

USE master;

ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

I have seen several reports of people trying to activate Service Broker in MSDB, but the process crashed or simply did not activate because Broker could not get the locks in MSDB to make the necessary changes. If this happens to you, either drop all MSDB connections or restart the SQL Service service and try again.

Check if Database Mail is running on MSDB database:

EXECUTE msdb.dbo.sysmail_help_status_sp

-- Se não retornar "STARTED", executar o comando abaixo para iniciar o Database Mail:
EXECUTE msdb.dbo.sysmail_start_sp

EXECUTE msdb.dbo.sysmail_help_status_sp

-- Se não retornar "STARTED", executar o comando abaixo para iniciar o Database Mail:

EXECUTE msdb.dbo.sysmail_start_sp

Check message queue status:

EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail'

1	EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail'

Check Database Mail Logs:

SELECT * FROM msdb.dbo.sysmail_event_log

1	SELECT * FROM msdb.dbo.sysmail_event_log

Check information about sent items and message errors:

SELECT * FROM msdb.dbo.sysmail_allitems

1	SELECT * FROM msdb.dbo.sysmail_allitems

Firewall and Antivirus

Often the sending of E-mail may end up not happening because Firewall / Antivirus blocks the attempt of sending messages by SMTP protocol by the Database Mail process. One way to ensure that the problem is not Firewall is to either release the port used by the connection (usually 25, 465 or 587) or even disable Firewall / Antivirus temporarily just to test if they are blocking the sending or is it some other way. thing.

Send an email test using VBscript script:

Another alternative of checking if the problem is with SQL Server or the server is by trying to send an email in another way. For this you can use the VBscript script below:

Const cdoAnonymous = 0 'Sem autenticação
Const cdoBasic = 1 'Autenticação Básica (Base64)
Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message") 
objMessage.Subject = "Teste de E-mail" 
objMessage.From = """Me"" <seu_email@seudominio.com>" 
objMessage.To = "destinatario@email.com" 
objMessage.TextBody = "Teste de Mensagem.." & vbCRLF & "Foi enviada utilizando autenticação Base64 e SSL."

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 

' Endereço do servidor SMTP
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.seudominio.com"

' Tipo de autenticação
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

' Usuário de autenticação
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "usuario@seudominio.com"

' Senha de autenticação
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "sua_senha"

' Porta do SMTP
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 

' Utiliza SSL ?
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

' Timeout
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update
objMessage.Send

If err.number = 0 then Msgbox "Email enviado com sucesso"

Const cdoAnonymous = 0 'Sem autenticação

Const cdoBasic = 1 'Autenticação Básica (Base64)

Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message")

objMessage.Subject = "Teste de E-mail"

objMessage.From = """Me"" <[email protected]>"

objMessage.To = "[email protected]"

objMessage.TextBody = "Teste de Mensagem.." & vbCRLF & "Foi enviada utilizando autenticação Base64 e SSL."