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

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!

Lijunguo

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

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

Rejo

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?

Thanks,
Lijunguo

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!

Lijunguo