By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,677 Members | 1,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,677 IT Pros & Developers. It's quick & easy.

coordinate sql post with smtp mail

P: n/a
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
Nov 18 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <kr*@comcast.netwrote in message
news:%2****************@TK2MSFTNGP04.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

Nov 18 '08 #2

P: n/a
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
Nov 18 '08 #3

P: n/a
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

Nov 18 '08 #4

P: n/a
In article <#V**************@TK2MSFTNGP04.phx.gbl>, Keith G Hicks
<kr*@comcast.netwrote:
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
Nov 18 '08 #5

P: n/a
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

Nov 18 '08 #6

P: n/a
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


Nov 18 '08 #7

P: n/a
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/h...ql-server.html

Andrew
Nov 18 '08 #8

P: n/a
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" <ak*@in-press.co.uk.invalidwrote in message
news:6o************@mid.individual.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/h...ql-server.html

Andrew


Nov 18 '08 #9

P: n/a
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



Nov 21 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.