如何从 SQL Server 发送电子邮件? [英] How to send email from SQL Server?

问题描述

如何使用 T-SQL 发送电子邮件，但电子邮件地址存储在表中?我想遍历表格并能够发送电子邮件.到目前为止，我找不到这样做的好例子.

解决方案

Step 1) Create Profile and Account

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

步骤 2)

运行:

sp_CONFIGURE '显示高级', 1走重新配置走sp_CONFIGURE '数据库邮件 XPs', 1走重新配置走

步骤 3)

使用 msdb走EXEC sp_send_dbmail @profile_name='yourprofilename',@recipients='[e-mail]',@subject='测试消息',@body='这是测试消息的正文.恭喜您成功收到数据库邮件.

遍历表格

DECLARE @email_id NVARCHAR(450)、@id BIGINT、@max_id BIGINT、@query NVARCHAR(1000)SELECT @id=MIN(id), @max_id=MAX(id) FROM [email_adresses]而@id<=@max_id开始选择@email_id=email_id发件人 [email_addresses]set @query='sp_send_dbmail @profile_name=''yourprofilename'',@recipients='''+@email_id+''',@subject=''测试消息'',@body=''这是测试消息的正文.恭喜您成功收到数据库邮件.'''执行@querySELECT @id=MIN(id) FROM [email_adresses] where id>@id结束

将此发布到以下链接 http://ms-sql-queries.blogspot.in/2012/12/how-to-send-email-from-sql-server.html

How can I send an email using T-SQL but email address is stored in a table? I want to loop through the table and be able to send email. I cannot find a good example of doing this so far.

解决方案

Step 1) Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings.

Step 2)

RUN:

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

Step 3)

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.'

To loop through the table

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

Posted this on the following link http://ms-sql-queries.blogspot.in/2012/12/how-to-send-email-from-sql-server.html

这篇关于如何从 SQL Server 发送电子邮件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！