在 SQL Server 2008 R2 中发送电子邮件 [英] Sending Email in SQL Server 2008 R2

问题描述

如果我公司的员工没有通过 Intranet Web 应用程序填写证明表格，我的任务是向他们发送电子邮件提醒.

I am tasked with a feature to send e-mail reminders to employees in my company if they haven't completed an attestation form via an intranet Web application.

我正在考虑编写一个在夜间数据库作业中调用的存储过程(SQL Server 2008 R2).proc 将选择员工电子邮件地址值并通过光标循环遍历它们，以便对于找到的每封电子邮件，都会使用 msdb.dbo.sp_send_dbmail 发送一封电子邮件.

I was thinking of writing a stored procedure that gets called in a nightly database job (SQL Server 2008 R2). The proc would select employee e-mail address values and loop through them via cursor, so that for each e-mail found an e-mail is sent using msdb.dbo.sp_send_dbmail.

我担心的是，这是一家大型公司，每晚可能会发送数万封电子邮件.发送如此大量的电子邮件时，有没有办法减轻性能问题?还是数万级就不用担心了?

The concern I have is that this is for a large company and tens of thousands of e-mail could go out nightly. Is there a way to mitigate performance concerns when sending out such a volume of e-mail? Or at the tens of thousands of level it shouldn't be a concern?

推荐答案

我认为在您的过程中，您可以创建一个临时表/表变量并用您要发送电子邮件的电子邮件填充它.

I reckon Inside your procedure you could create a Temp table/Table Variable and populate it with the emails you want to send email to.

一旦您将所有电子邮件都放在一个表中，您就可以将电子邮件地址与 ; 连接起来，并将其存储到一个变量中，并将该变量作为参数传递给 msdb.dbo 的 @recipients 参数.sp_send_dbmail 进程.

Once you have all the emails in a table then you could concatenate the email addresses with ; and store it to a variable and pass that variable as a parameter to @recipients parameter of msdb.dbo.sp_send_dbmail proc.

这样的……

假设您在过程中填充了一个名为 Emails 的表变量

Say you have populated a table variable called Emails inside your procedure

DECLARE @Emails TABLE(Email NVARCHAR(1000))
INSERT INTO @Emails VALUES
('[e-mail]'),('[e-mail]'),('[e-mail]')  --<-- Three emails you want to send email

电子邮件的串联

DECLARE @Email_List NVARCHAR(MAX);   --<-- Variable to store emails List

SELECT @Email_List = STUFF((SELECT ';' + Email [text()]
                            FROM @Emails
                            FOR XML PATH(''),TYPE)
                            .value('.','NVARCHAR(MAX)'),1,1, '')                   
FROM @Emails e 

-- Test SELECT @Email_List
-- RESULT:  [e-mail];[e-mail];[e-mail]

现在将此变量传递给@recipients 参数

Now pass this variable to @recipients parameter

EXECUTE msdb.dbo.sp_send_dbmail  @profile_name = 'ProfileName'  
                               , @recipients   = @Email_List
                               , @subject      = 'Some_Subject'

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