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

Question

提问by moe

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

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

Thanks for your help

谢谢你的帮助

Answer 1

回答by Ruzbeh Irani

Step 1) Create Profile and Account

步骤 1) 创建个人资料和帐户

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)

第2步）

RUN:

跑：

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

Step 3)

步骤 3)

USE msdb
GO
EXEC sp_send_dbmail @profile_name='yourprofilename',
@recipients='[email protected]',
@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

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

Answer 2

回答by Salman Lone

You can send email natively from within SQL Server using Database Mail. This is a great tool for notifying sysadmins about errors or other database events. You could also use it to send a report or an email message to an end user. The basic syntax for this is:

您可以使用数据库邮件从 SQL Server 内部发送电子邮件。这是通知系统管理员有关错误或其他数据库事件的绝佳工具。您还可以使用它向最终用户发送报告或电子邮件。其基本语法是：

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

Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure. A database or Exchange admin might need to help you configure this. See http://msdn.microsoft.com/en-us/library/ms190307.aspxand http://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Serverfor more information.

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

Answer 3

回答by pancy1

You can do it with a cursor also. Assuming that you have created an Account and a Profile e.g. "profile" and an Account and you have the table that holds the emails ready e.g. "EmailMessageTable" you can do the following:

您也可以使用游标来完成。假设您已经创建了一个帐户和一个配置文件，例如“配置文件”和一个帐户，并且您拥有准备好电子邮件的表，例如“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

After that all you have to do is execute the Stored Procedure

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

EXECUTE mass_email
GO

Answer 4

回答by Lars

Here's an example of how you might concatenate email addresses from a table into a single @recipients parameter:

下面是一个示例，说明如何将表中的电子邮件地址连接到单个 @recipients 参数中：

CREATE TABLE #emailAddresses (email VARCHAR(25))

INSERT #emailAddresses (email) VALUES ('[email protected]')
INSERT #emailAddresses (email) VALUES ('[email protected]')
INSERT #emailAddresses (email) VALUES ('[email protected]')

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

SELECT @recipients

DROP TABLE #emailAddresses

The resulting @recipients will be:

结果@recipients 将是：

[email protected];[email protected];[email protected]

Answer 5

回答by Ahmad Abuhasna

In-order to make SQL server send email notification you need to create mail profile from Management, database mail.

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

1) User Right click to get the mail profile menu and choose configure database mail

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

2)choose the first open (set up a database mail by following the following tasks) and press next Note: if the SMTP is not configured please refer the the URL below

2）选择第一个打开（按照下面的任务设置一个数据库邮件），然后按下一步注意：如果没有配置SMTP请参考下面的网址

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

3) in the second screen fill the the profile name and add SMTP account, then press next

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

4) choose the type of mail account ( public or private ) then press next

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

5) change the parameters that related to the sending mail options, and press next 6) press finish

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

Now to make SQL server send an email if action X happened you can do that via trigger or job ( This is the common ways not the only ones).

现在要让 SQL 服务器在发生操作 X 时发送电子邮件，您可以通过触发器或作业来执行此操作（这是常用的方法，而不是唯一的方法）。

1) you can create Job from SQL server agent, then right click on operators and check mails (fill the your email for example) and press OK after that right click Jobs and choose new job and fill the required info as well as the from steps, name, ...etc and from notification tab select the profile you made.

1）您可以从SQL服务器代理创建作业，然后右键单击操作员并检查邮件（例如填写您的电子邮件），然后在右键单击作业并选择新作业并填写所需信息以及来自步骤后按确定、姓名、...等，然后从通知选项卡中选择您创建的个人资料。

2) from triggers please refer to the example below.

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 = '[email protected]',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1 --Suppress 'Mail Queued' message
GO

Answer 6

回答by Marc Zeroc

sometimes while not found sp_send_dbmail directly. You may use 'msdb.dbo.sp_send_dbmail' to try (Work fine on Windows Server 2008 R2 and is tested)

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

Answer 7

回答by Kamal Pratap

To send mail through SQL Server we need to set up DB mail profile we can either use T-SQl or SQL Database mail option in sql server to create profile. After below code is used to send mail through query or stored procedure.

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

Use below link to create DB mail profile

使用以下链接创建数据库邮件配置文件

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

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

提问by moe

回答by Ruzbeh Irani

回答by Salman Lone

回答by pancy1

回答by Lars

回答by Ahmad Abuhasna

回答by Marc Zeroc

回答by Kamal Pratap

相关推荐

最近更新

标签

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

提问by moe

回答by Ruzbeh Irani

回答by Salman Lone

回答by pancy1

回答by Lars

回答by Ahmad Abuhasna

回答by Marc Zeroc

回答by Kamal Pratap

相关推荐

SQL 选择列的一部分

SQL 设计表格时如何实现一对一、一对多和多对多的关系？

SQL 选择总和（）从（选择（选择（））

SQL 将 postgresql 数组解包成行

相关推荐

最近更新

标签