coordinate sql post with smtp mail

**Keith G Hicks** · Nov 18 '08, 01:35 AM

Re: coordinate sql post with smtp mail

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.

"Keith G Hicks" <krh@comcast.ne twrote in message
news:%23Vq3UPRS JHA.5056@TK2MSF TNGP04.phx.gbl. ..

>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
>

**Michael Cole** · Nov 18 '08, 01:45 AM

Re: coordinate sql post with smtp mail

Keith G Hicks <krh@comcast.ne twrote in message
<news:OfKG2wRSJ HA.1184@TK2MSFT NGP03.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

**Keith G Hicks** · Nov 18 '08, 02:05 AM

Re: coordinate sql post with smtp mail

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" <none@invalid.c omwrote in message
news:uDq615RSJH A.5164@TK2MSFTN GP05.phx.gbl...

Keith G Hicks <krh@comcast.ne twrote in message
<news:OfKG2wRSJ HA.1184@TK2MSFT NGP03.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
>

**J.B. Moreno** · Nov 18 '08, 04:55 AM

Re: coordinate sql post with smtp mail

In article <#Vq3UPRSJHA.50 56@TK2MSFTNGP04 .phx.gbl>, Keith G Hicks
<krh@comcast.ne twrote:

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.

--
J.B. Moreno

**James Hahn** · Nov 18 '08, 07:55 AM

Re: coordinate sql post with smtp mail

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" <krh@comcast.ne twrote in message
news:ugR3bBSSJH A.3584@TK2MSFTN GP06.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" <none@invalid.c omwrote in message
news:uDq615RSJH A.5164@TK2MSFTN GP05.phx.gbl...

>Keith G Hicks <krh@comcast.ne twrote in message
><news:OfKG2wRS JHA.1184@TK2MSF TNGP03.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
>>

>
>
>
>

**Keith G Hicks** · Nov 18 '08, 02:55 PM

Re: coordinate sql post with smtp mail

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" <jhahn@yahoo.co mwrote in message
news:e5ju5FVSJH A.588@TK2MSFTNG P06.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" <krh@comcast.ne twrote in message
news:ugR3bBSSJH A.3584@TK2MSFTN GP06.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" <none@invalid.c omwrote in message
>news:uDq615RSJ HA.5164@TK2MSFT NGP05.phx.gbl.. .

>>Keith G Hicks <krh@comcast.ne twrote in message
>><news:OfKG2wR SJHA.1184@TK2MS FTNGP03.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
>>>

>>
>>
>>
>>

>

**Andrew Morton** · Nov 18 '08, 04:25 PM

Re: coordinate sql post with smtp mail

Keith G Hicks wrote:

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** · Nov 18 '08, 05:55 PM

Re: coordinate sql post with smtp mail

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.

"Andrew Morton" <akm@in-press.co.uk.inv alidwrote in message
news:6og8gdF3h9 nqU1@mid.indivi dual.net...

Keith G Hicks wrote:

>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
>

**James Hahn** · Nov 21 '08, 05:45 AM

Re: coordinate sql post with smtp mail

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" <krh@comcast.ne twrote in message
news:%23vO$OxYS JHA.5348@TK2MSF TNGP02.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" <jhahn@yahoo.co mwrote in message
news:e5ju5FVSJH A.588@TK2MSFTNG P06.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" <krh@comcast.ne twrote in message
>news:ugR3bBSSJ HA.3584@TK2MSFT NGP06.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" <none@invalid.c omwrote in message
>>news:uDq615RS JHA.5164@TK2MSF TNGP05.phx.gbl. ..
>>>Keith G Hicks <krh@comcast.ne twrote in message
>>><news:OfKG2w RSJHA.1184@TK2M SFTNGP03.phx.gb l>
>>>>
>>>>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
>>>>
>>>
>>>
>>>
>>>

>>

>
>

coordinate sql post with smtp mail

coordinate sql post with smtp mail

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment