Link to home
Start Free TrialLog in
Avatar of dzirkelb
dzirkelbFlag for United States of America

asked on

Auto Reports Sending two Emails

Every morning we run a series of reports.  When the report is finished, it redirects to the next report, and so on.

These reports are ran on classic asp, server 2008 web server, sql 2000 server.

Up until a month ago they were ran on an IE 6.0 session from Windows XP.  We are however migrating the process to be ran on a Server 2012 r2 using IE 11.

Since day one of the switch, random emails in the report chain will send two emails instead of one.  What is baffling is, according to the logs, the page is physically running twice.  However, this simply can not be the case because if the page was running twice, then two separate redirects would happen, causing duplicate reports on every single report after, but it doesn't.

So, it sort of looks like this:

Report 1 runs like normal
Report 2 runs like normal
Report 3 sends two emails
Report 4 runs normal

It appears almost all reports in our list are affected, but only randomly.

I placed code in to see if the page itself is running code twice, and sure enough, timsestamps show the code being ran twice.  But again, only one redirect is happening, so I am completely baffled.

Here is an example piece of code:


<%
set rs = Server.CreateObject ("adodb.Recordset")
ssql = "sql statement"
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly

if not rs.eof then
      Set obJMail = Server.CreateObject("CDO.Message")
      Set objConfiguration = Server.CreateObject("CDO.Configuration")
      Set strFields = objConfiguration.Fields
      
      With strFields
            .Item(cdoSendUsingMethod) = 2
            .Item(cdoSMTPServer) = "server"
            .Item(cdoSMTPServerPort) = 25
            .Item(cdoSMTPconnectiontimeout) = 10
            .Update
      End With
     
      Set obJMail.Configuration = objConfiguration
      obJMail.From = "xyz@123.com"
      obJMail.To = obJMail.To & "; abc@123.com"
      obJMail.Subject = "subject"
      obJMail.HTMLBody = obJMail.HTMLBody & "body"
      
      do until rs.EOF
            obJMail.HTMLBody = obJMail.HTMLBody & looped body"
      rs.MoveNext
      loop

      obJMail.Send
end if

ssql = "INSERT INTO TempReportTimestamps (ReportName) VALUES ('"&Request.ServerVariables("SCRIPT_NAME")&"')"
dbc.execute(ssql)

rs.Close
set rs = nothing

ssql = "UPDATE MorningReports SET ReportRan = 1, LastReportDate = '"&now()&"' WHERE (ReportName = 'webpage')"
dbc.execute(ssql)

dbc.close
set dbc=nothing

if request.querystring("ManualRun") = "Yes" then
      response.redirect("homepage")
else
      Response.Redirect "next email in loop"
end if
%>

As you can see, if the page runs twice, it would redirect to the next page in the loop twice, but it doesn't.
Avatar of Big Monty
Big Monty
Flag of United States of America image

how are you running the reports? thru Scheduled Tasks?
Avatar of dzirkelb

ASKER

Yes.  But, I run one scheduled task, and that task runs the first page in the series of all the reports.
At the bottom, can you create a log or run the page manually with some testing

set rs = Server.CreateObject ("adodb.Recordset")
ssql = "sql statement"
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly

' ***** testing  *****
response.write "Before Loop<br>"  ' *
dim loop '*
loop = 0 '*

if not rs.eof then
      loop = loop+1 '*
     response.write "Start Loop #"&loop&"<br>"  ' *

      Set obJMail = Server.CreateObject("CDO.Message") 
      Set objConfiguration = Server.CreateObject("CDO.Configuration")
      Set strFields = objConfiguration.Fields 
      
      With strFields 
            .Item(cdoSendUsingMethod) = 2
            .Item(cdoSMTPServer) = "server" 
            .Item(cdoSMTPServerPort) = 25 
            .Item(cdoSMTPconnectiontimeout) = 10 
            .Update 
      End With
     
      Set obJMail.Configuration = objConfiguration
      obJMail.From = "xyz@123.com"
      obJMail.To = obJMail.To & "; abc@123.com"
      obJMail.Subject = "subject"
      obJMail.HTMLBody = obJMail.HTMLBody & "body"
      
     dim mail_body_loop '*
     mail_body_loop = 0 '*
      do until rs.EOF
       mail_body_loop = mail_body_loop + 1 '*
       response.write "mail_body_loop = "&mail_body_loop&"<br>"
            obJMail.HTMLBody = obJMail.HTMLBody & looped body"
      rs.MoveNext
      loop

      response.write "send mail <br>" '*
      obJMail.Send
end if

ssql = "INSERT INTO TempReportTimestamps (ReportName) VALUES ('"&Request.ServerVariables("SCRIPT_NAME")&"')"
dbc.execute(ssql)

response.write "Update TempReportTimestamps" '*

rs.Close
set rs = nothing

ssql = "UPDATE MorningReports SET ReportRan = 1, LastReportDate = '"&now()&"' WHERE (ReportName = 'webpage')"
dbc.execute(ssql)

response.write "Update MorningReports" '*
dbc.close
set dbc=nothing

response.write "request.querystring ManualRun = |"&request.querystring("ManualRun)&"|<br>" '  *

if request.querystring("ManualRun") = "Yes" then
     response.write "if ManualRun = Yes" ' *
     response.end ' *
      response.redirect("homepage")
else
    
   response.write "ManualRun = Not Yes" '*
   response.end '*

      Response.Redirect "next email in loop"
end if

Open in new window

here are the logs from this morning, a report that ran twice, but only directed to the next page in the loop once:
scheduled task IP, -, 4/10/2015, 0:59:39, W3SVC3, server, 10.3.1.18, 530, 1140, 393, 302, 0, GET, /webpage, -,
scheduled task IP, -, 4/10/2015, 0:59:39, W3SVC3, server, 10.3.1.18, 15, 1140, 393, 302, 0, GET, /webpage, -,

and the timestamps from my code dumping into an sql table:

/webpage      2015-04-10 00:59:39.683
/webpage      2015-04-10 00:59:39.793
I wonder if it is a log in issue?

scheduled task IP, -, 4/10/2015, 0:59:39, W3SVC3, server, 10.3.1.18, 530, 1140, 393, 302, 0, GET, /webpage, -,
scheduled task IP, -, 4/10/2015, 0:59:39, W3SVC3, server, 10.3.1.18, 15, 1140, 393, 302, 0, GET, /webpage, -,

https://support.microsoft.com/en-us/kb/943891

500.15	Direct requests for Global.asax are not allowed.	A direct request for the Global.asa file or for the Global.asax file is made. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
942030 Error message when you visit a website that is hosted on IIS 7.0: "HTTP Error 500.15 - Direct request for global.asa are not allowed

Open in new window

https://support.microsoft.com/en-us/kb/942030
You think the log issue could be causing the email to be sent twice?  Because, the email for certain is sent twice, duplicate emails are definitely going out.
I'm not great at reading logs but it looks like there is  is a 302 redirect and probably the 530 error code (530.15).

When I send out auto emails like this, I will use a flag in the db to make sure I don't send out twice.    As example, if I it is to a group of people based on some criteria, I will first send the contact data to a transaction table, then  use that transaction table to send out the mail.  As each contact is sent something, I will update a field in that transaction table with some flag like the number 1 or a date.   The query in that transaction table will be something like, "SELECT email, name FROM tMailTrans WHERE cast(ISNULL(TimeStamp,0) as varchar) ='0'"
That to me is a workaround and not a root cause solution to this issue, which stemmed directly when I moved the process to a server 2012 / IE 11 browser.

That solution would be fine if I had one or two pages to change, but I have hundreds.
I wouldn't call it a work around, it's a safety valve and it is how I set things up from the start.  What if that error caused many more emails and not just 2?

In any case, you have an error that looks like it is either stemming from some type of login required, permissions, something invalid  

https://www.google.com/search?q=classic+asp+error+530.15&oq=classic+asp+error+530.15&aqs=chrome..69i57.7378j0j7&sourceid=chrome&es_sm=119&ie=UTF-8#q=browser+error+530

I would try and trap the error by running the page directly.  At the top of the page

On Error Resume Next

Open in new window


At the bottom or inside a loop

If Err.number<>0 then
   response.write Err.number
   response.end
end if

Open in new window

I'm not able to generate any sort of error after running the page roughly 100 times directly, and I was unable to duplicate the double sending email / page running at all.
Here are this morning duplicates:

Originating IP, -, 4/14/2015, 2:37:34, W3SVC3, server, IP Of Page, 405, 670, 393, 302, 0, GET, /page1.asp, -,
Originating IP, -, 4/14/2015, 2:37:34, W3SVC3, server, IP Of Page, 15, 670, 393, 302, 0, GET, /page1.asp, -,

Originating IP, -, 4/14/2015, 2:37:44, W3SVC3, server, IP Of Page, 249, 670, 373, 302, 0, GET, /page2.asp, -,
Originating IP, -, 4/14/2015, 2:37:44, W3SVC3, server, IP Of Page, 124, 670, 373, 302, 0, GET, /page2.asp, -,

Originating IP, -, 4/14/2015, 2:41:10, W3SVC3, server, IP Of Page, 251, 678, 379, 302, 0, GET, /page3.asp, -,
Originating IP, -, 4/14/2015, 2:41:10, W3SVC3, server, IP Of Page, 138, 678, 379, 302, 0, GET, /page3.asp, -,

Originating IP, -, 4/14/2015, 2:42:33, W3SVC3, server, IP Of Page, 2940, 680, 409, 302, 0, GET, /page4.asp, -,
Originating IP, -, 4/14/2015, 2:42:33, W3SVC3, server, IP Of Page, 115, 680, 409, 302, 0, GET, /page4.asp, -,

Originating IP, -, 4/14/2015, 2:38:12, W3SVC3, server, IP Of Page, 300, 664, 395, 302, 0, GET, /page5.asp, -,
Originating IP, -, 4/14/2015, 2:38:12, W3SVC3, server, IP Of Page, 71, 664, 395, 302, 0, GET, /page5.asp, -,
What is the top row of the log file have for field names?

What is the script you are hitting with the schedule task?  Are you hitting a vbs script for instance that posts to this page?

What is the complete asp code for this page?

Do you know how to write to a text file in asp?  What I would like you to do is create a file you can write to and then follow instructions from here http:/Q_28653621.html#a40717660 to get the error number.  Instead of outputting the error to screen with response.write, send a line to you custom log file.

http://www.4guysfromrolla.com/webtech/faq/FileSystemObject/faq3.shtml
There is no top row on the log, it is just one large text file created by IIS.

The scheduled task simply runs an internet explorer web page with a specified address.  That web page runs, then redirects to the next, and so on and so forth until all are done, around 45 or so in total.

I'll work on posting to a text file, but here is an example entire page of code:

<!-- #include file="SQLDataConnection.asp" -->
<%
set rs = Server.CreateObject ("adodb.Recordset")
ssql = "SELECT * FROM Table"
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly

if not rs.eof then
	Set obJMail = Server.CreateObject("CDO.Message") 
	Set objConfiguration = Server.CreateObject("CDO.Configuration")
	Set strFields = objConfiguration.Fields 

	With strFields 
		.Item(cdoSendUsingMethod) = 2
		.Item(cdoSMTPServer) = "server"
		.Item(cdoSMTPServerPort) = port 
		.Item(cdoSMTPconnectiontimeout) = 10 
		.Update 
	End With
	     
	Set obJMail.Configuration = objConfiguration
	obJMail.From = "from@mail.com"
	obJMail.To = obJMail.To & "; to@mail.com"
	obJMail.Subject = "Transactions With More Than 60 Percent Margin For "& rs("InvDate")
	obJMail.HTMLBody = obJMail.HTMLBody & "<html><body><table cellpadding=5 border=1><tr><th><font face=""verdana"" size=""1"">Cust Name</th><th><font face=""verdana"" size=""1"">Inside Sales</th><th><font face=""verdana"" size=""1"">Account Level</th><th><font face=""verdana"" size=""1"">Invoice #</th><th><font face=""verdana"" size=""1"">Part #</th><th><font face=""verdana"" size=""1"">Mfg Name</th><th><font face=""verdana"" size=""1"">Qty</th><th><font face=""verdana"" size=""1"">Price</th><th><font face=""verdana"" size=""1"">Cost</th><th><font face=""verdana"" size=""1"">GP</th><th><font face=""verdana"" size=""1"">GP Margin</th><th><font face=""verdana"" size=""1"">Margin Notes</th></tr>"
	 		
	do until rs.EOF
		sMargin = ""
			
		if isnumeric(rs("Margin")) then
			sMargin = FormatPercent(rs("Margin"),2)
		end if
				
		obJMail.HTMLBody = obJMail.HTMLBody & "<tr><td><font face=""verdana"" size=""1""><a href=""site?Cust="&rs("CustNum")&""">" & rs("CustName") & "</a></td><td><font face=""verdana"" size=""1"">" & rs("Inside") & "</td><td><font face=""verdana"" size=""1"">" & rs("AccountLevel") & "</td><td><font face=""verdana"" size=""1""><a href=""site?Invoice="&rs("InvNum")&""">" & rs("InvNum") & "</a></td><td><font face=""verdana"" size=""1""><a href=""site?radioSearch=ID&txtSearch="&rs("ID")&""">" & rs("PartNum") & "</a></td><td><font face=""verdana"" size=""1"">" & rs("MfgName") & "</td><td><font face=""verdana"" size=""1"">" & rs("QTY") & "</td><td><font face=""verdana"" size=""1"">" & rs("PRICE") & "</td><td><font face=""verdana"" size=""1"">" & rs("COST") & "</td><td><font face=""verdana"" size=""1"">" & rs("GP") & "</td><td><font face=""verdana"" size=""1"">" & sMargin & "</td><td><font face=""verdana"" size=""1"">"&rs("MarginNotes")&"</td></tr>"
	rs.MoveNext
	loop
		
	obJMail.Send
end if

	ssql = "INSERT INTO TempReportTimestamps (ReportName) VALUES ('"&Request.ServerVariables("SCRIPT_NAME")&"')"
	dbc.execute(ssql)

rs.Close
set rs = nothing

ssql = "UPDATE MorningReports SET ReportRan = 1, LastReportDate = '"&now()&"' WHERE (ReportName = 'email_autotranshighmargin.asp')"
dbc.execute(ssql)

dbc.close
set dbc=nothing

if request.querystring("ManualRun") = "Yes" then
	response.redirect("site?TypeOfReports=MorningReports&Complete=Yes")
else
	Response.Redirect "nextreport.asp"
end if
%>

Open in new window

I put in some code to trap the error into a text file.  So far, however, I am simply not able to duplicate the issue on demand, but it happens every morning on a couple pages.  I've tried to run the page before, which goes to the page that duplicates, and let that page go to the next then stop it but simply can not replicate.

I will know more tomorrow when the page runs and dumps in any sort of error code.
just a shot in the dark, but have you had any Windows Updates installed since before this took place? I ask because I've run into scenarios where these updates can wreak havoc on a site. My specific instance when this occurred was I couldn't connect to a database because of a driver issue. I know that's not you're issue, but maybe the Windows Update screwed up something else.
It started happening when I moved it from IE 6.0 to IE 11 since day one.
The text file is a bust as it shows an error code of 0 for the past two days on my file.  However, it does show 0 twice on each day, when the report only ran once.
Any other ideas?  I'd really like to move these email processes away from an xp / IE 6.0 machine to the server that runs other auto processes.
I pulled one email out that was duplicating form the chain of emails and set it to run by itself, and it ran with no duplicates.  I'd prefer to not do this as I have about a hundred I'd have to create a task for.
The problem still exists, when the email is in a chain, it duplicates at random, and the page is physically being ran twice, but only one page redirects to the next in the loop.
in the schedule task, what do you have set to do when the job fails? does it terminate or try to run through to the next record?
How are you running your scheduled task to hit your asp page?

I will typically write a vbs to do an xmlhttpost to the asp page.   I will add some parameters in the post for the asp page to read and authenticate.
The scheduled task runs a web page, that's it.  Once it loads up internet explorer, the tasks's job is complete, it does nothing else.  It does not know if a page fails, so if it terminates is irrelevant.

The scheduled task is created using the wizard.

The scheduled task is not the issue here.  All it does is load up one page, MorningReportsStart.asp.  That page runs, redirecets to the next page, which directs to the next page, etc etc.

All the task does is load up MorningReportsStart.asp, it loads nothing else.
I seem to remember a long time ago doing something similar and having issues.  The redirects may be the gotcha.

Instead of your scheduled task running an asp page, create a vbs that does an xmlhttppost to the asp page.  Then instead of subsequent redirects, I would first try and rewrite code so it can all be done at once.  

My second option would be to replace the redirects with xmlhttpost.

Additionally,  many times I write to my database or log file to track what is going on.  Pick some spots in your code and either add a line to your log or db row to know it ran successfully and perhaps use some error trapping https://www.experts-exchange.com/questions/28660324/showing-asp-class-errors-in-asp-net-website.html.

I do think  you will find the crux of your problem will be the redirects. Either get everything to run on one page or try to replace redirects with xmlhttppost's https://support.microsoft.com/en-us/kb/290591.  This may be quicker, but the first way may get better results.

<%
' run report one

	DataToSend = "id=1"
	dim xmlhttp 
	set xmlhttp = server.Createobject("MSXML2.ServerXMLHTTP")
	xmlhttp.Open "POST","http://mysite.com/report2.asp",false
	xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
	xmlhttp.send DataToSend
	Response.ContentType = "text/xml"

        Set xmlhttp = nothing

' do the same at the end of report 2
%>

Open in new window

If I'm reading your code correctly, I could put the pages into one large loop to run all 50 or so reports, like so:



<%
set rs = Server.CreateObject ("adodb.Recordset")
ssql = "SELECT ReportName FROM ReportTable"
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly

do while not rs.eof
	DataToSend = "id=1"
	dim xmlhttp 
	set xmlhttp = server.Createobject("MSXML2.ServerXMLHTTP")
	xmlhttp.Open "POST","http://mysite.com/"&rs("ReportName")&".asp",false
	xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
	xmlhttp.send DataToSend
	Response.ContentType = "text/xml"
	Set xmlhttp = nothing
rs.moveNext
Loop
%>

Open in new window



that look right?  Also, i'm not sure what the DataToSend = "id=1" means.

I do agree that the redirects could very well be the problem, it could get confused and redirect to itself somehow before it moves to the next report.  I have some code in place that basically fires a record into a database, so I can see the page is being ran twice, but only redirects once to the next page.

I am anxious to try yoru method, barring my code is right.

I won't be able to combine them all into one page, that will just be too massive and the page will time out too often.

With this approach of using the xmlhttp, if, say, report 45 fails, how will I know report 45 fails?  Right now, it says what page, what line, etc fails.  With this method, the page actually never changes, so how do I know what page fails with what line of code?
ASKER CERTIFIED SOLUTION
Avatar of Scott Fell
Scott Fell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok, on the datatosend, this is sort of irrelevant for what we are doing then, correct?  Because, I'm not sending any data to the report page, I simply need it to run, am I right on that?  It is just becomes a placeholder so the page does not error out, right?

You have listed request.form("authcode").  I'm assuming this authcode is something I'd have to put on every page of my loop, and give it a value of "123", and response.write it to the screen, correct?  Then, if anything else is displayed than 123, that indicates an error on the page itself, and I'd write that value to a database, send it in an email, display it on the screen, etc.  Am I on the right line of thinking here?
Correct, if you are not sending any data, then you do not need to send data as form input.  However, think about the fact that you have a page possibly in a public accessible url. It would be nice to have a safeguard where the page can not be run just by surfing to it.  

By posting some type of authcode and requiring that authcode on the report page as a request.form, it can't be run by mistake.   That bit is up to you.

If you have just several reports to run then the looping like this is probably fine.  If you have a hundred, you can potentially run 100 reports within a few seconds and could that overload your resources?  

That is why my original suggestion is to run one report.  At the bottom of that page, do another xmlhttppost to hit the next report and so on.
I have about 40 reports to run, and they run at around 1 am or so, there are no resources being used at that time, so all is well on that front.

Also, the pages are not accessible from the outside world, so the security isn't much needed.

Thank you for all of your help, I plan to start doinking with this today, starting with 1-5 test pages as tests, then go from there.
I think you should just be able to comment out the line for data to send if you don't want to send any data.

'xmlhttp.send DataToSend

Open in new window

I have dabbled with this quite a bit and I just cant' quite get it to work, I can get one to run fine, but not a loop, nor can I run 3 on one page as a test outside a loop.

However, your ideas triggered my thought process and I am going to test server.execute()

so, I plan do do the following of sorts:

<%
set rs = Server.CreateObject ("adodb.Recordset")
ssql = "SELECT ReportName FROM ReportTable"
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly

do while not rs.eof
          server.execute(rs("ReportName"))
rs.moveNext
Loop
%>

Open in new window


One thing I like about this is when a page errors out, the script stops and tells me where it errored, what page, what line, etc.

I'll test this more and see if that seems to solve the problem of the duplicate emails being sent.
Although I didn't use this solution exactly, it led me in a different direction, basically the same thing you are suggesting, but more server side, older code, which works with our existing systems / knowledge a little better.

I haven't pulled the plug and switched yet, but I will in the next month.  Thanks for your help, and if it continues after I make the switch, then I'll be back posting again!