0

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.

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yoav
  • 150
  • 1
  • 2
  • 15
  • I think this would be more of a concern for the mail server and this question would perhaps be better placed on server fault or database administrators. – T I Feb 25 '14 at 20:37

1 Answers1

2

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.

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

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

Concatenation of emails 

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

Now pass this variable to @recipients parameter

EXECUTE msdb.dbo.sp_send_dbmail  @profile_name = 'ProfileName'  
                               , @recipients   = @Email_List
                               , @subject      = 'Some_Subject'
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • This is a very intriguing idea. I know that @recipients (and @blind_copy_recipients) is a varchar(max) so it can hold a ton of addresses. That said, per you other note, is this really a question for the server folks? – Yoav Feb 25 '14 at 21:48
  • @Yoav Well you have a solution here, I dont see any reason why you need to get "server folks" involved :) – M.Ali Feb 25 '14 at 21:51
  • You're correct, it's a great solution and marked it as such. – Yoav Feb 25 '14 at 21:54