在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?
推荐答案
我认为在您的过程中,您可以创建一个Temp表/表变量,并在其中添加您要向其发送电子邮件的电子邮件.
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.sp_send_dbmail proc的@recipients参数.
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.
类似这样的事情...
Something like this...
假设您在过程中填充了一个名为Emails的表变量
Say you have populated a table variable called Emails inside your procedure
DECLARE @Emails TABLE(Email NVARCHAR(1000))
INSERT INTO @Emails VALUES
('aaa@aaa.com'),('bbb@aaa.com'),('ccc@aaa.com') --<-- 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: aaa@aaa.com;bbb@aaa.com;ccc@aaa.com
现在将此变量传递给@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屋!