We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now


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

lijunguo asked
Medium Priority
Last Modified: 2008-02-01

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

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



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


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) .. www.sqldts.com 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 ..



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


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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!


Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.