Solved

Duplicate email using CDO for Windows 2000 Type Library

Posted on 2006-11-09
17
281 Views
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
Flds.Update
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"))
      .Send
      rs.MoveNext
Loop
Conn.Close
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[193.70.193.96], 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[193.70.193.95], 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

0
Comment
Question by:apici
17 Comments
 
LVL 6

Expert Comment

by:hc0904pcd
ID: 17905245
hi,
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?


0
 

Author Comment

by:apici
ID: 17905290
hi,
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.







0
 
LVL 6

Expert Comment

by:hc0904pcd
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?
0
 
LVL 6

Expert Comment

by:hc0904pcd
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.
0
 
LVL 6

Expert Comment

by:hc0904pcd
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... )
0
 

Author Comment

by:apici
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



0
 
LVL 6

Expert Comment

by:hc0904pcd
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?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:apici
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.
0
 
LVL 6

Expert Comment

by:hc0904pcd
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?



0
 

Author Comment

by:apici
ID: 17905817
57kb
0
 
LVL 6

Expert Comment

by:hc0904pcd
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.
 
0
 

Author Comment

by:apici
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
0
 
LVL 6

Expert Comment

by:hc0904pcd
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...
0
 
LVL 29

Expert Comment

by:Badotz
ID: 18407096
In your SQL, try:

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

that should filter out duplicates ;-)
0
 

Accepted Solution

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

AnnieMod
Cleanup Admin
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now