Muitas pessoas me perguntam: “É possível enviar e-mails pelo SQL Server?” ou “Como enviar e-mails pelo SQL Server?”. Neste post irei lhes mostrar como habilitar o envio de e-mails no seu servidor SQL Server e como enviar e monitorar os e-mails enviados.
Um recurso que é muito utilizado na maioria dos sistemas, é o envio de e-mails automáticos contendo notificações, promoções, newsletter e muito mais. Sendo assim, seria muito interessante enviar esses e-mails diretamente de nossas SP’s no banco de dados, que podem ser agendadas pelo SQL Server Agent para automatizar sua execução.
Habilitando o envio de e-mails no servidor usando T-SQL
Visualizar como ativar e configurar o Database mail via linha de comando (usando T-SQL)Habilitando o envio de e-mails no servidor pelo SSMS
Visualizar como configurar o Database Mail pelo Management StudioTestando o envio de e-mail
Após configurar o Database Mail, vamos testar se o servidor está permitindo o envio normalmente. Para isso, abra o Object Explorer no Management Studio, navegue até Management > Database Mail e clique com o botão direito na opção “Send Test E-mail…”
Basta selecionar o perfil que você criou (No exemplo, é o MSSQLServer), preencher o e-mail do destinatário que irá receber o teste e clicar no botão “Send Test E-mail”
Após enviar o teste, será aberta uma tela confirmando que o e-mail foi para a fila do Database Mail.
Caso você tenha recebido o e-mail de teste, basta clicar no botão “OK”. Se você não recebeu, clique no botão “Trobleshooting” para ser direcionado para a página de ajuda da Microsoft (https://technet.microsoft.com/pt-br/library/ms187540(v=sql.105))
Exemplo de e-mail de teste enviado pelo SQL Server Database Mail:
Para visualizar o log do Database Mail e verificar se os seus e-mails foram enviados ou a mensagem de erro ao tentar enviar, veja mais na parte “Monitorando o envio de e-mails” deste post, logo abaixo.
Enviando o e-mail
Agora que configuramos o servidor, estamos prontos para realizar nossos envios de e-mails. A sp que iremos utilizar é a sp_send_dbmail, do próprio SQL Server, que permite o envio de e-mails no formato texto ou HTML e anexos.
Enviando e-mail no formato HTML
1 2 3 4 5 6 7 | EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileEnvioEmail', @subject = 'Assunto do E-mail', @body = 'Olá! <strong>Teste</strong>', @body_format = 'html', |
Enviando e-mail concatenando o resultado de uma query ao corpo da mensagem
1 2 3 4 5 6 7 8 | EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileEnvioEmail', @subject = 'Assunto do E-mail', @body = 'Olá! <strong>Teste</strong>', @body_format = 'html', @query = 'SELECT TOP 10 * FROM sys.sysobjects' |
Enviando e-mail com o resultado de uma query como anexo (CSV)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileEnvioEmail', @subject = 'Assunto do E-mail', @body = 'Olá! <strong>Teste</strong>', @body_format = 'html', @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 |
Enviando e-mail com 2 arquivos em anexo
1 2 3 4 5 6 7 8 | EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileEnvioEmail', @subject = 'Assunto do E-mail', @body = 'Olá! <strong>Teste</strong>', @body_format = 'html', @file_attachments = 'C:\Imagem.jpg;C:\Teste.txt' |
Monitorando o envio de e-mails
Monitorar ações sempre é importante na TI. O envio de e-mails não foge à regra. Por mais que seja possível monitorar os e-mails pelo servidor de e-mails, nem sempre é tão rápido conseguir validar isso, principalmente porque isso foge um pouco do escopo do DBA/Programador, onde esse controle geralmente é controlado pela equipe de Infraestrutura.
Além disso, o servidor de e-mails guarda logs de todo o tráfego de e-mails, de todas as contas, sendo mais difícil extrair essas informações do que simplesmente consultando tabelas de catálogo do SQL Server, que são a msdb.dbo.sysmail_mailitems e a msdb.dbo.sysmail_event_log.
Para facilitar a extração das informações, eu geralmente costumo criar essa view, e depois apenas faço consultas nela:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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 |
Uma outra forma é utilizando a interface do SQL Server Management Studio
Após selecionar a opção “View Database Mail Log”, você verá a tela dos logs do Database Mail, em uma interface parecida com o do SQL Server Activity Monitor
Ajuda rápida para resolver problemas (Troubleshooting Database Mail)
Caso você esteja com problemas para configurar ou enviar e-mails, seguem algumas dicas que podem te ajudar a resolver esses problemas.
Valide os dados de acesso
Esse tópico pode parecer bobo, mas garanta que você digitou corretamente o endereço do servidor SMTP, porta, usuário, senha e se você marcou a opção de utilizar SSL caso seu servidor SMTP solicite isso.
Recentemente tive uma grande batalha com o Database Mail para configurar uma conta do Yahoo e outra hospedada na Hostgator. Apesar da documentação indicar que eu deveria utilizar a porta 465 (SSL) em ambos os casos, eu ter utilizado um cliente de e-mail (Outlook) configurado utilizando a porta 465 e enviado e-mail normalmente e ter feito um teste de envio utilizando a porta 465 em um VBscript com sucesso, no Database Mail eu só consegui enviar quando coloquei a porta 587 (TLS). Na porta 465 o e-mail não era enviado de jeito nenhum, provavelmente por ser do protocolo SSL.
Sendo assim, consulte a documentação do seu provedor de e-mail e em caso de problemas ao configurar o Database Mail, tente utilizar outras portas alternativas (Geralmente os provedores disponibilizam duas portas para envio de SMTP seguro, uma SSL e outra TLS).
Verificar se o Service Broker está ativado para o database msdb (deve retornar 1):
1 | SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' |
Se o resultado da query for 0, isso significa que o Service Broker não está ativo. Com isso, as mensagens enviadas não serão enfileiradas por ele e o envio não ocorrerá. Para corrigir isso, execute o comando abaixo:
1 2 3 4 5 | USE master; GO ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; GO |
Já vi vários relatos de pessoas que tentaram ativar o Service Broker no MSDB, mas o processo travou ou simplesmente não ativou porque o Broker não conseguiu obter os locks no MSDB para realizar as alterações necessárias. Se isso acontecer com você, elimine todas as conexões do MSDB ou reinicie o serviço do SQL Service e tente novamente.
Verificar se o Database Mail está executando no database MSDB:
1 2 3 4 | 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 |
Verificar o status da fila de mensagens:
1 | EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail' |
Verificar os logs do Database Mail:
1 | SELECT * FROM msdb.dbo.sysmail_event_log |
Verifica as informações sobre os itens enviados e erros das mensagens:
1 | SELECT * FROM msdb.dbo.sysmail_allitems |
Firewall e Antivírus
Muitas vezes o envio do E-mail pode acabar não acontecendo pelo fato do Firewall/Antivírus bloquear a tentativa de envio de mensagens pelo protocolo SMTP por parte do processo do Database Mail. Uma forma de se garantir que o problema não seja o Firewall é liberar a porta utilizada pela conexão (Geralmente 25, 465 ou 587) ou mesmo desativar o Firewall/Antivírus temporariamente, apenas para testar se eles que estão bloqueando o envio ou é alguma outra coisa.
Enviar um teste de e-mail utilizando script VBscript:
Uma outra alternativa de verificar se o problema está no SQL Server ou no servidor é tentando enviar um e-mail por uma outra forma. Para isso, você pode utilizar o script VBscript abaixo:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | 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.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 _ ' 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" |
Após criar o script, basta executá-lo pelo Prompt de Comando (CMD.exe):
Valide os protocolos de rede
Uma outra validação que pode ser feita, é se os protocolos de rede Named Pipes e TCP/IP estão habilitados para a sua instância. Caso não estejam, habilite e reinicie o serviço do SQL Server da sua instância.
Analise a conta de serviço do SQL Server
Um outro ponto que você deve avaliar caso não esteja conseguindo enviar seus e-mails é verificar se a conta de serviço que está sendo utilizada para executar o SQL Server possui permissões para acessar a rede externa. Não precisa necessariamente ser uma conta de Administrador local ou Domain Admin, mas precisa ter as permissões básicas de acesso à rede para conseguir enviar o e-mail SMTP.
Para mais detalhes sobre contas de serviço, acesse este link.
Instale a última versão do Service Pack e Cumulative Updates
Microsoft SQL Server 2016 SP1
Microsoft SQL Server 2016 Latest Cumulative Update
Para outras edições, acesse este link.
Instale a última versão do Service Pack e Cumulative Updates
Caso você esteja utilizando o SQL Server 2016 RTM ou SP1, fique atento a um problema comum do Database Mail, que é a necessidade de ter que instalar o Microsoft .NET Framework 3.5 no seu servidor para evitar que as suas mensagens fiquem presas na fila do Service Broker e fiquem sempre com o status de unsent (não enviadas). Caso contrário, o Database Mail não irá funcionar devido a um bug do produto, que foi corrigido no Cumulative Update 2 (Link de referência), voltou a ocorrer no Service Pack 1 CU1 e foi novamente corrigido no Service Pack 1 CU2 (Link de referência).
Link da Microsoft para ajudar a solucionar problemas do Database Mail
Solucionando problemas de Database Mail
Como enviar e-mail mail email pelo sql server, how to send email mail from within sql server
Como enviar e-mail mail email pelo sql server, how to send email mail from within sql server
Ola Dirceu,
Consigo enviar para mais de um email na mesma consulta?
Boa tarde novamente,
Após esgotar as opções lógicas parti para as não lógicas.
Usamos alias em nossos Servers para facilitar a conexão entre eles, existia um alias com o nome do próprio servidor, removi este e os e-mails voltaram a ser enviados normalmente.
Atenciosamente,
Jeverson Marcon
Dirceu, boa tarde, tudo bem?
Aprendi a realizar a configuração de e-mails no SQL com os seus posts a um certo tempo, e utilizava o envio de e-mails através de JOBs do SQLServer normalmente, porém, por questões de padronização, alteramos no nome dos nossos servidores, e após isso os e-mails deixaram de ser enviados, se eu voltar o nome antigo do servidor, os e-mails voltam a ser enviados, mas esta não é uma opção “aceitável”, já vasculhei inúmeros sites, posts, tutoriais e afins sobre assuntos relacionados mas de nenhuma maneira consegui resolver esta questão e voltar a enviar os e-mails.
Nesta parte do seu post “EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = ‘Mail'” noto que a fila fica sempre como “INACTIVE” e os e-mails ficam na situação “0 – Aguardando envio”.
Você ja passou por algo semelhante / sabe o que pode ser feito para resolver este problema ?
Atenciosamente,
Jeverson Marcon
É possível reenviar os e-mails que falharam?
Grande artigo, me ajudou demais! Obrigado.
Boa tarde Dirceu!
muito obrigado pela contribuição.
quando tento criar a conta de e-mail no final recebo a seguinte mensagem: Não é possível inserir o valor NULL na coluna “servername”. tabela ‘msdb.dbo.sysmail_server’
segue seu passo a passo configurando pelo SSMS, verifiquei e o serviço de e-mail esta ativo.
abraços.
Ótimo, DIRCEU sabe algum recurso do SQLMAil que possa ser usado para enviar e-mail para todos de um SELECT Mail de uma tabela ? tentei aqui mas não achei nada.
Luciano, boa noite.
Você vai ter que criar um loop while concatenando os destinatários em uma string ou enviando o email individualmente, um por um.
Quase deu…
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2017-01-27T14:56:47). Exception Message: Cannot send mails to mail server. (Serviço não disponível, fechando o canal de transmissão. A resposta do servidor foi: Cannot connect to SMTP server 64.233.186.108 (64.233.186.108:587), connect error 10060).
)
Leonardo, existem várias coisas que podem estar te impedindo o envio do e-mail, como:
– Firewall bloqueando o envio
– Configurações de porta, SSL incorretas na configuração do profile
– Alguns provedores de e-mail bloqueiam o uso do SMTP/POP por padrão, como o Yahoo e Gmail, e você deve ativá-los, caso deseje utilizar.
– Problema de conexão com a Internet
Você pode tentar fazer um teste de conexão utilizando PowerShell ou Telnet para verificar se é algo na sua rede que está impedindo o envio.
Esse link também pode ajudar: https://technet.microsoft.com/pt-br/library/ms187540(v=sql.105).aspx
Se ainda estiver com problemas após verificar todos esses ítens, me envia um e-mail pela página de contato que eu vou tentar te ajudar.