如何从SQL Server发送电子邮件？

Question

如何使用T-SQL发送电子邮件，但是电子邮件地址存储在表中？我想循环浏览表并能够发送电子邮件。到目前为止，我还没有找到一个好的示例。

- moe

---

不应将SQL Server用作应用服务器。设置电子邮件以提醒您是一回事，但将其用作电子邮件分发器则完全不同。虽然您可以这样做，但您需要考虑是否应该这样做。 - SQLMason

@SQLMason，微软写道：“Database Mail是从SQL Server数据库引擎或Azure SQL托管实例发送电子邮件消息的企业解决方案。您的应用程序可以向用户发送电子邮件消息...”。来源：https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail 阅读此内容，我认为微软确实打算将Database Mail用作电子邮件分发系统。您是否有具体的理由（例如性能、可维护性等）不建议以这种方式使用它？或者可能有背景信息的链接吗？ - Beat Nideröst

Answer 1

第一步) 创建个人资料和账户

您需要使用“配置数据库邮件向导”创建个人资料和账户，该向导可以从管理节点中的“数据库邮件”节点的“配置数据库邮件”上下文菜单中访问。此向导用于管理帐户、个人资料和数据库邮件全局设置。

第二步)

运行:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

第三步）

USE msdb
GO
EXEC sp_send_dbmail @profile_name='yourprofilename',
@recipients='[e-mail]',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

循环遍历表格

DECLARE @email_id NVARCHAR(450), @id BIGINT, @max_id BIGINT, @query NVARCHAR(1000)

SELECT @id=MIN(id), @max_id=MAX(id) FROM [email_adresses]

WHILE @id<=@max_id
BEGIN
    SELECT @email_id=email_id 
    FROM [email_adresses]

    set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                        @recipients='''+@email_id+''',
                        @subject=''Test message'',
                        @body=''This is the body of the test message.
                        Congrates Database Mail Received By you Successfully.'''

    EXEC @query
    SELECT @id=MIN(id) FROM [email_adresses] where id>@id

END

在以下链接中发布了这篇文章http://ms-sql-queries.blogspot.in/2012/12/how-to-send-email-from-sql-server.html

- Ruzbeh Irani

---

谢谢Ruzbeh，但我需要从我的表格中获取电子邮件列表。我该如何遍历表格并从中获取每个电子邮件并发送电子邮件？你展示的方法只使用一个固定电子邮件。期待尽快收到您的回复。谢谢。 - moe

我已经编辑了答案，请检查一下。这里的"ID"是您地址表中的标识列。 - Ruzbeh Irani

@max_id=MIN(id) 应该改为 @max_id=MAX(id) 吗？否则似乎不会发送任何电子邮件。 - Jimmy

继续这个出色的答案，电子邮件默认只能从 MSDB 发送。当我使用这种方法时，花了我几分钟才弄清楚为什么我的存储过程失败了。 - jitendragarg

由于没有人提到，我想说一下，DB mail的目的是向DBA发送状态更新等信息，而不是用于大规模邮件分发。是的，在小范围内它可以工作，并且我已经将其用于向小团队发送报告和更新，但是循环遍历表并向每个人发送电子邮件可能会给您的服务器带来压力。 - BilliD

Answer 2

你可以使用数据库邮件在SQL Server中原生地发送电子邮件。这是一个通知系统管理员有关错误或其他数据库事件的好工具。您也可以使用它向最终用户发送报告或电子邮件消息。

基本语法如下：

EXEC msdb.dbo.sp_send_dbmail  
@recipients='[e-mail]',
@subject='Testing Email from SQL Server',
@body='<p>It Worked!</p><p>Email sent successfully</p>',
@body_format='HTML',
@from_address='Sender Name <[e-mail]>',
@reply_to='[e-mail]'

在使用之前，必须使用“Database Mail Configuration Wizard”或“sp_configure”启用“数据库邮件”。您可能需要数据库或Exchange管理员帮助您进行配置。 有关更多信息，请参见http://msdn.microsoft.com/en-us/library/ms190307.aspx 和http://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server。

- Salman Lone

Answer 3

您也可以使用光标来完成此操作。假设您已经创建了一个帐户和一个档案，例如“profile”，并且已准备好保存电子邮件的表格，例如“EmailMessageTable”，您可以执行以下操作：

USE database_name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE mass_email AS
declare @email nvarchar (50) 
declare @body nvarchar (255)  

declare test_cur cursor for             
SELECT email from [dbo].[EmailMessageTable]

open test_cur                                        

fetch next from test_cur into   
@email     
while @@fetch_status = 0       
begin                                    

set @body = (SELECT body from [dbo].[EmailMessageTable] where email = @email)
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile',
    @recipients = @email,
    @body = @body,
    @subject = 'Credentials for Web';
fetch next from test_cur into  
@email 
end    
close test_cur   
deallocate test_cur

之后你需要做的就是执行存储过程。

EXECUTE mass_email
GO

- pancy1

Answer 4

以下是一个示例，说明如何将来自表格的电子邮件地址连接成单个@recipients参数：

CREATE TABLE #emailAddresses (email VARCHAR(25))

INSERT #emailAddresses (email) VALUES ('[e-mail]')
INSERT #emailAddresses (email) VALUES ('[e-mail]')
INSERT #emailAddresses (email) VALUES ('[e-mail]')

DECLARE @recipients VARCHAR(MAX)
SELECT @recipients = COALESCE(@recipients + ';', '') + email 
FROM #emailAddresses

SELECT @recipients

DROP TABLE #emailAddresses

生成的@收件人将是：

[e-mail];[e-mail];[e-mail]

- Lars

---

这完全没有回答问题。 - TylerH

Answer 5

为了使SQL Server发送电子邮件通知，您需要从管理中创建邮件配置文件，即数据库邮件。

1）用户右键单击以获取邮件配置文件菜单，并选择配置数据库邮件。

2）选择第一个打开的选项（按照以下任务设置数据库邮件），然后按下“下一步”。 注意：如果SMTP未配置，请参考下面的URL。

http://www.symantec.com/business/support/index?page=content&id=TECH86263

3) 在第二个屏幕中填写配置文件名称并添加SMTP帐户，然后按下一步

4) 选择邮件帐户类型（公共或私人），然后按下一步

5) 更改与发送邮件选项相关的参数，然后按下一步6）按完成

现在，要使SQL服务器在发生动作X时发送电子邮件，您可以通过触发器或作业来实现（这是常见的方式之一，而不是唯一的方式）。

1) 您可以从SQL服务器代理创建作业，然后右键单击操作员并检查邮件（例如填写您的电子邮件），然后在此之后右键单击作业并选择新作业，并填写所需的信息以及来自步骤，名称等，从通知选项卡中选择您创建的配置文件。

2) 对于触发器，请参考以下示例。

AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'your subject'
SET @results = 'your results'
-- write the Trigger JOB
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQLAlerts',
 @recipients = '[e-mail]',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1 --Suppress 'Mail Queued' message
GO

- Ahmad Abuhasna

Answer 6

有时候无法直接找到 sp_send_dbmail。您可以尝试使用 'msdb.dbo.sp_send_dbmail' （在 Windows Server 2008 R2 上工作正常并已经测试过）。

- Marc Zeroc

Answer 7

要通过SQL Server发送邮件，我们需要设置DB邮件配置文件，可以使用T-SQl或SQL数据库邮件选项在SQL Server中创建配置文件。在使用以下代码通过查询或存储过程发送邮件之后。

请使用以下链接创建DB邮件配置文件

http://www.freshcodehub.com/Article/42/configure-database-mail-in-sql-server-database

http://www.freshcodehub.com/Article/43/create-a-database-mail-configuration-using-t-sql-script

--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestProfile', 
@recipients = 'To Email Here', 
@copy_recipients ='CC Email Here',             --For CC Email if exists
@blind_copy_recipients= 'BCC Email Here',      --For BCC Email if exists
@subject = 'Mail Subject Here', 
@body = 'Mail Body Here',
@body_format='HTML',
@importance ='HIGH',
@file_attachments='C:\Test.pdf';               --For Attachments if exists

- Kamal Pratap