473,321 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

coordinate sql post with smtp mail

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
9 1259
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mark Carter | last post by:
I'm trying to create a mail server in Twisted. I either get SMTPSenderRefused or SMTPException: SMTP AUTH extension not supported by server. What do I need to do to get it to work?
2
by: RandRace | last post by:
I'm having some problems with a little script i wrote using net::smtp. I originally wrote it in linux where it works perfectly. I tried to use it from windows the other day and it doesn't work. It...
15
by: Steven Burn | last post by:
My server has POP but only has SMTP if sending to my domain, and not other domains (such as hotmail). I'm therefore wondering, if anyone knows of any scripts etc, that will allow me to have a sort...
3
by: dale zhang | last post by:
Hi, I write an asp.net web application. It has a “Contact Us” page, where users fill in their email, subject and text and hit send. Then the email will go to my hard coded yahoo email...
5
by: Charlie | last post by:
Hi: I'm working on an e-commerce site. Using the SMTP class, my site sends out confirmation messages. It works most of the time, but sometimes raises an error. I need a way of making sure it...
3
by: RN | last post by:
I am tired of sending mail from the built-in SMTP service for so many reasons (errors are nondescriptive in the event log, it doesn't let me control which IP address it sends from, and it...
34
by: antonyliu2002 | last post by:
I've set up the virtual smtp server on my IIS 5.1 like so: 1. Assign IP address to "All Unassigned", and listen to port 25. 2. Access Connection granted to "127.0.0.1". 3. Relay only allow...
4
by: RSoIsCaIrLiIoA | last post by:
while i read bugtraq i see this post: >Mailing-List: contact bugtraq-help@securityfocus.com; run by ezmlm >Precedence: bulk >List-Id: <bugtraq.list-id.securityfocus.com> >List-Post:...
5
by: Chris | last post by:
I am trying to send email in C#. I wrote 2 pieces of code: 1. MailMessage mail = new MailMessage(); mail.From = "from_address"; mail.To = "to_address"; mail.Subject = "subject"; mail.BodyFormat...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.