coordinate sql post with smtp mail

[Keith G Hicks]

I have a vb app that runs on a server. It periodically checks for rows in a
table that are ready to have data mailed out to clients. After it finds
row(s) that are ready, it emails the info out and then marks the rows as
having been sent. It all works fine except for one thing. There have been a
couple of times when the 2nd part chokes due to a conflict on the server.
The mail is sent out but it never gets around to marking the rows as sent.
So then 5 minutes later it sends them again. I've thought this over and
can't come up with a good solution. What is the best way to be sure that
BOTH events occur - the mail and the marking of the data?

Thanks,

Keith

 

[Keith G Hicks]

Sorry. Left something out. The data is in MS SQL 2000.

Here's the basic process:

1. I run an SQL statement on the table to find the rows that need to be
mailed out.
2. I read through those using ExecuteReader and Read. Each row gets mailed
out and I store the ID values of each row in a string as it's mailed.
3. After the mailing is done, I use the string that was created in #2 to
update the table so that the rows that were mailed are marked as having been
mailed. I'm doing this by passing that string to a stored procedure. This is
the step that has failed a couple of times.

 

[Michael Cole]

Sorry. Left something out. The data is in MS SQL 2000.

Here's the basic process:

1. I run an SQL statement on the table to find the rows that need to
be mailed out.
2. I read through those using ExecuteReader and Read. Each row gets
mailed out and I store the ID values of each row in a string as it's
mailed. 3. After the mailing is done, I use the string that was created in
#2
to update the table so that the rows that were mailed are marked as
having been mailed. I'm doing this by passing that string to a stored
procedure. This is the step that has failed a couple of times.

Why not update each row as you do it?

SELECT Top 1 ...
{Send Mail}
UPDATE ... WHERE ID = ...
Repeat...

 

[Keith G Hicks]

You're right. I thought of that too and definitely think that's a better way
to go but there could still be a problem on the SQL side that would cause
the update of that row not to happen but the email is still sent. I'm not
sure of the best way to handle that. I'm used to doing transaction
processing either in VB when making data updates or in SQL itself when
updating multiple tables but this is different and I'm a bit stuck. I know
that once the maile is sent out if the sql can't update I can't exactly yank
the mail back and start over so nto sure what to do really.

 

[J.B. Moreno]

I have a vb app that runs on a server. It periodically checks for rows in a
table that are ready to have data mailed out to clients. After it finds
row(s) that are ready, it emails the info out and then marks the rows as
having been sent. It all works fine except for one thing. There have been a
couple of times when the 2nd part chokes due to a conflict on the server.
The mail is sent out but it never gets around to marking the rows as sent.
So then 5 minutes later it sends them again. I've thought this over and
can't come up with a good solution. What is the best way to be sure that
BOTH events occur - the mail and the marking of the data?

Well, you're never going to be absolutely sure -- it's two processes
(three if you count delivery), that are independent of each other.

But what you could do is create a select/update statement inside a SQL
transaction, then complete the transaction when you're done sending the
email.

 

[James Hahn]

How are you discovering that the update hasn't worked?

Typical processing would be to not send the mail until the update is
confirmed correct. If there is some means of confirming the update, then
your procedure could leave the e-mailing until you have confirmed that the
update worked.

 

[Keith G Hicks]

I'm logging all the activity. All messages that are sent out are logged. I'm
also trapping for exceptions. The SQL error is in my log.

I thought about doing what you suggested but I could end up with the same
problem but the other way around. If I mark the row as having been sent
before it sends and then I get some sort of SMTP error then that's actually
worse in this case. I'd end up with a system that thinks it sent a message
when in fact it didn't. I know I could go back and unmark the row if there's
an SMTP error but then that could hang too and not get unmarked.

Quite a chicken and the egg situation I know but I figured this is something
that people do ocasionally so I was hoping there would be an elegan solution
out there that I haven't been able to think of.

 

[Andrew Morton]

Quite a chicken and the egg situation I know but I figured this is
something that people do ocasionally so I was hoping there would be
an elegan solution out there that I haven't been able to think of.

Why not get the SQL Server to send the email?

http://classicasp.aspfaq.com/email/how-do-i-send-e-mail-from-sql-server.html

Andrew

 

[Keith G Hicks]

That's a good sugggestion and I thought about that too but I'm not familiar
enough with that to set it up in the short amount of time we have left.
Also, they are not set up for MAPI. AFAIK that's necessary and they'd (my
client) have to change their mail system around which they're not goign to
be willing to do.

 

[James Hahn]

It's always an issue with any two dependant processes when one could fail.

The typical solution is to maintain two settings - a 'sent' flag and a
'confirmed' flag. The sent gets set when the mail operation is started and
the confirmed gets set when the mail operation completes successfully. So
the process would be:

Select items for sending
Set the sent flag and confirm
Send the mail and confirm
Set the confirmed flag.

The send operation won't be attempted if the send flagging fails and the
confirm flagging won't be done if the send operation fails. Any item in the
list without a sent or confirmed flag can be re-sent. Any item in the list
with a sent flag but no confirmed flag needs special processing to see where
the failure occurred. But your method of finding the update failures would
sugest this is not practical, as the update failures are being revealed
after the process has completed. Some form of after-the-event auditing may
be the only option.

 

[zarul me]

keith..im sorry for bugging you

i just need to know how you do the periodically check of your database..cause im doing a system which need to periodically run sql query..plz help me sir