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.
"Keith G Hicks" <kr*@comcast.netwrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
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.
"James Hahn" <jh***@yahoo.comwrote in message
news:e5*************@TK2MSFTNGP06.phx.gbl...
>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" <kr*@comcast.netwrote in message
news:ug**************@TK2MSFTNGP06.phx.gbl...
>>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.
"Michael Cole" <no**@invalid.comwrote in message
news:uD**************@TK2MSFTNGP05.phx.gbl...
Keith G Hicks <kr*@comcast.netwrote in message
<news:Of**************@TK2MSFTNGP03.phx.gbl>
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...
--
Regards
Michael Cole