2.0 How to send bulk HTML email to 5,000 addresses stored in a SQL db


I would like to send HTML newsletter emails to about  5,000 - 10,000 email addresses collected through an opt-in newsletter signup on my website.

I know how to use BCC in ASP.NET 2.0 to send emails. But I want to minimise the impact of our SMTP mail server. The email addresses are stored in SQL SERVER 2005.

So this is a tough question, it's about the performance issue. And I know how to send email, what I want to know is how to improve the performance and minimise the impact of SMTP server.
LVL 11
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

if the format of the mail is the same for all the users, one of the option is to concatenate the mail address in a group of say multiples of 100 each and send the mail using the database server and not the web server ..

u might want to add a unique identity column to table if not already present .. then u cld have a Stored procedure like this

declare @MailIds Varchar(4000)
declare @Start int

@Start = 1
---Start loop for all recordcount
set @MailIds = ''
select @MailIds = @MailIds +';' + mailid
from TableName
where UniqueColumn between @Start and @Start + 100
-- send mail using sp_SendMail
@Start = @Start + 101
-- end loop

lijunguoAuthor Commented:
Hi Rejojohny,
Thank you for you reply! For Db option, about sp_sendMail, we still need to use SMTP server to send email. Or we have other options.

And I did not get your group email address. Could you explain more about your ideas? Can we set up a service with some time interval to send email?

And do you know other options? So we may have some choices.

Thanks a lot!

>>use SMTP server to send email.
yes .. read on sp_sendmail in books online and it will tell u how to set things up

>>Can we set up a service with some time interval to send email
u can have a "job" in the database to call a Stored procedure which contains the code to send the mail ..

>>And I did not get your group email address
its just concatenating multiple mail ids together with a semi-colon ";"  and sending one mail ..
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

lijunguoAuthor Commented:
Now I have a questin about the logic. We only send 100 emails a time, then what's the following line for?
@Start = @Start + 101

Is it going to be a output parameter of sp for return value? So we know how to  start the next 100.
How do we set the time interval?
>>then what's the following line for?
thats the reason i said u first have to find the total count of the email ids ..

Anyway a better option would be to use DTS (Data transformation services) .. is a good site for this .. u could create a DTS package in SQL server .. create a stored procedure (SP) which will call this DTS package .. and from ur front end call this SP ...

or u could create a SQL job which calls a SP which looks for mail ids in a particular table .. if found, it will send the mail to all those mail ids (maybe one by one or first 100 at one time)  and delete those mail id from that table .. this table could be filled whenever u need .. this job could be scheduled to run with the interval as per ur requirement ..

lijunguoAuthor Commented:
Hi Rejo,
Glad to see you back, if DTS is a better way, I could do it. I did DTS programming before with ASP.NET.  Could you please explain a little more about send email logic with DTS. I don't need help with DTS, but the logic implement sending email . Because I did not get it. So you want me to use DTS to send email with some time interval, is that right?

yes .. create a DTS package to do the following
  scan a table (lets say the name of the table is Emails) to get first 100 email ids
  Concatenate them using semi colons ";"
  send the mail
  Delete the first 100 mail ids

Create a job which will call this DTS package at specific intervals

So when u need to send mails, just add the mailids to this table "Emails". Just take care that the mail ids are inserted at the last. This can be ensured by creating a clustered index on a "Id" field (Identity column) .. So the structure of the table could be

Id              int (identity)
EmailIds     Varchar(500)

Pls feel free to ask if u have any more queries about this and I will try to help as much as I can ...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lijunguoAuthor Commented:
Hi Rejo,

I really appreciate your help. I think I got your idea.

1. create a email talbe, talbe structure:
id  int(identity)
EmailAddress varchar(200)

2. populate that table from customer DB, so identity is generated automatically, and all email addresses are in that table.

3. create a DTS package, send first 100 email, then deleted first 100 record which email is being sent from email table.

4. create a job at which will call this DTS package at specific intervals.

That's all.

Thanks a lot!


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.