[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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

Hi,

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.
0
lijunguo
Asked:
lijunguo
  • 4
  • 4
1 Solution
 
RejojohnyCommented:
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

0
 
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!

Lijunguo
0
 
RejojohnyCommented:
>>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 ..
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
lijunguoAuthor Commented:
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?
0
 
RejojohnyCommented:
>>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
0
 
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?

Thanks,
Lijunguo
0
 
RejojohnyCommented:
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 ...

Rejo
0
 
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!

Lijunguo

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now