Duplicate email using CDO for Windows 2000 Type Library

Posted on 2006-11-09
Medium Priority
Last Modified: 2008-01-09
My program sends a newsletter to about 2000 verified addresses take from a database; some users get two mails, though there is only one record with their account and though the program runs once (we controlled the web log)
The program runs on a Windows Server 2003 standard

Here is the code :

<!--METADATA TYPE="typelib" UUID="CD000000-8B95-11D1-82DB-00C04FB1625D" NAME="CDO for Windows 2000 Type Library" -->
<!--METADATA TYPE="typelib" UUID="00000205-0000-0010-8000-00AA006D2EA4" NAME="ADODB Type Library" -->

<%@ Language=VBScript %>

Server.ScriptTimeout=7200  'it takes about 1 hour to run

cURL = Request("URL")
cSubject = Request("SUBJECT")
cMailFrom = Request("MAILFROM")
cMailTo = Request("MAILTO")
cTipoSpedizione = Request("TIPOSPEDIZIONE")
cDbConf = Request("DBCONF")
cIdRedazione = Request("IDREDAZIONE")
cIdGruppo = Request("IDGRUPPO")

Dim iMsg
Dim iConf
Dim Flds
set iMsg = CreateObject("CDO.Message")
set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields
Flds(cdoSendUsingMethod) = cdoSendUsingPort
Flds(cdoSMTPServer) = "   ...    "  'here there was the ip addesss of the mail server
Flds(cdoSMTPServerPort) = 25
Flds(cdoSMTPAuthenticate) = cdoAnonymous ' 0
With iMsg
Set .Configuration = iConf

.From = cMailFrom
.Subject = cSubject

.CreateMHTMLBody cURL

Dim cConnectionString, nSpedizioni
nSpedizioni = 0
cConnectionString = " ... "  ' cancelled
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open cConnectionString
sql= "SELECT email FROM newsletter ORDER BY email DESC"
set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open sql, Conn, 1 , 1
Do While not(rs.eof)
      nSpedizioni = nSpedizioni + 1
      .To = trim(rs("email"))
Set Conn = Nothing


The email server is a Red Hat Linux release 6.2 (Zoot).
Controlling the email server logs,it seems that only about the first 400 users of the database list get 2 mails.
The mail server records a second distinct send request 10 minutes after the first, for the first user of the list.
Here are 2 log lines of one of the last duplicated emails, with the second sent  about 18 minutes later:

Nov  8 12:07:34 control postfix/smtp[14507]: F35E7133ED: to=<nnn@libero.it>, relay=mx2.libero.it[], delay=695, status=sent (250 Ok: queued as 82BBC28402F)
Nov  8 12:25:47 control postfix/smtp[14763]: 3FCAD133A4: to=<nnn@libero.it>, relay=mx1.libero.it[], delay=1148, status=sent (250 Ok: queued as 496131000217)

(I modified the email address)
The last (correctly sent once) email was sent at 12:48, that is later than the second ones: the program takes more than an hour to execute.

This is my first question in this site: please let me know if I wrote something wrong ...
Thanks in advance for your help

Question by:apici

Expert Comment

ID: 17905245
a quick check for duplicates - sort your sql query by email address ascending, and each time through your loop set the current email address to a variable which stands for "last email sent". Then at the start of each loop compare the new email to the previous email and only send if they don't match - that will precent duplicates being sent out provided that you sorted the list alpabetically.

that doesn't fix your problem, but might hopefully useful in preventing the dupes going out?

my other initial thought is to wonder if there is a possibility of the page refreshing or reloading?
although if that happened you'd think the whole list would resent.
okay, how about the email queue - email queues generally send out an email and under some circumstances (ie the email doesn't immediately get accepted at the other end) it may resend. can you check or monitor the email queue while it's in action?

another preventative idea, you could mark records as each email is sent and use that marker to ensure an email is not sent twice to the same record. but that will not help if the problem is in the email queue.

1 hour to send out 2000 emails sems quite slow. I send approx 6000 and it processes the script in about 4 minutes. Are the emails queueing up on your inhouse mail server? And if yes, how fast is it?


Author Comment

ID: 17905290
there are not duplicates in the table: we controlled the number of records of some people who received duplicate mails.
Inserting an internal array with the already sent email might be of use, if it is the programm to send the same mail twice, though ti simply should not. I will probably try tomorrow.
If possible, I would not mark the records in the database, though it will probably be the next step.

I do not think there might be a refreshing of the page: in the web log this program file is called once a day.

In the email queue, the duplicated mails have different identification numbers, as if they were really distinct mails, not a retry. The mail server is probably rather slow, but the lan might have its share in delaying processes.


Expert Comment

ID: 17905309
I'm tending to think it's probalby the mail serfver, not your code, as I can't see anything in your code that might cause a resend of the first x emails.

Are you able to run some tests, possibly on a smaller scale?
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.


Expert Comment

ID: 17905329
The first test I would suggest is very simple.
Modify your script so that the actual send is disabled, and instead, write to screen the email address and other meaningful data.
This should run pretty fast I'd think? So you ought to be able to run it 5 or 10 times in succession and hopefully demonstrate that the  error does not occur in this part of things.

Expert Comment

ID: 17905348
A second test I'd suggest is to modify your script to run loops within the loop, ie to send batches to the mail queue.
If you could, for example, send 100 emails and then pause or sleep the script for 5 minutes and then send the next 100...
That might lessen the load on the mail server and reduce the chance of a hiccup. And I suspect this will not take any longer to get the emails out.

( I should say again, this is testing speculatively, and something to do until anyone can pinpoint the cause of your problem more precisely... )

Author Comment

ID: 17905481
The newsletter has just been sent today, without duplicates ...

It must be something dealing with the load of the servers.
I found this message on the yesterday event log of the windows server

"Time 11:58:56
A process serving application pool '..._scripts' exceeded time limits during shut down. The process id was '21680'. "

It might refer to something else, but 2 seconds later the first duplicated mail was sent.

I thought that when exceeded time limits (and it should have not been this case since only 1 hour had passed after the beginning) the process would have been stopped; on the contrary it seems to have continued going on with another istance of itself starting sending mails: is it possible?

Thanks for your help


Expert Comment

ID: 17905496
that's good to know.
so maybe focus on reducing the mail server load?
and/or explore alternatatives to CDO if that's a viable option for you?

Author Comment

ID: 17905739
We are using CDO because it seems to be working fine with sending html mails, and it is very easy. Unfortunately it might be the cause of those troubles, but I would like to be sure about it before changing (again).

Reducing the server load is not that easy, and might not be that useful.
It is supposed to be a good (and expensive) server, and it hosts a number of rather busy sites (about 30) without load problems.
On the contrary, I was thinking of using the same program for other newsletters (now sent through linux servers, with programs using javax.mail or PHPmailer) which might run simultaneously.

I would rather find out why it is so slow and what happened yesterday (and not only yesterday).

Sorry, no reducing load forecasted for the windows server.

Expert Comment

ID: 17905773
Okay, in terms of why is it slow, lets talk over what happens in the send process and look at areas where it might be bottlenecking.

It takes an hour to run the script, so I'm assuming that the script sends each email one by one, and can't progress until each email is sent. And 2000 emails, 1 hour - that's about 1.8 seconds per email?

Are the emails large?


Author Comment

ID: 17905817

Expert Comment

ID: 17905910
slightly off-topic since you're happy with cdo and don't want to change, but see http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21600724.html - some informal testing of jmail and aspemail components - 500 (small) emails, approx 10 seconds for aspemail and approx 60 seconds for jmail - using similar loop to yours, ie those times are for sending the emails not just dumping to a queue.

based on that i think you should be aiming to get 2000 emails out using cdo within 10 minutes rather than 1 hour.
and/or you could look at ways to enqueue the emails, which will finish your script process faster.

Author Comment

ID: 17914619
Well, eventually we will try another component, but I would like to know what happened, and if CDO is really not reliable.

Daily newsletter: all ok today, no duplicates

Expert Comment

ID: 17923485
I think CDO is reliable, the main improvement you'd see by switching to aspemail or jmail would be in speed.
My best guess is that your limitations right now are with the mail server or the connection, that possibly one or both are bottlenecking. If you can clear that up then cdo will probably do you just fine for a long time to come...
LVL 29

Expert Comment

ID: 18407096
In your SQL, try:

sql= "SELECT email FROM newsletter GROUP BY email ORDER BY email DESC"

that should filter out duplicates ;-)

Accepted Solution

AnnieMod earned 0 total points
ID: 19389146
PAQed with no points refunded (of 250)

Cleanup Admin

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question