SQL Server and Automated Emails

[SueHales]

Hi, folks. I'm fairly new to SQL Server, so forgive any stupidity
inherent in this question. I work at a university and a certain
faculty member would like to have her students create a simple auction
web site as an exercise. As part of the assignment, she wants the
auction site to be able to send an email to a bidder when they have
been outbid.

So she has asked me if this is possible, and how to do it. I've read a
bit about sending an automated email from SQL Server, but I can't find
anything about how to trigger the email based upon a field comparison
in the database. I also don't know how to incorporate a field value as
a variable into the body of the email.

Of course, I don't expect anyone to solve my entire problem in this
forum. I was just hoping that someone could point me in the right
direction. Can anyone give me any advice about where I should be
looking for such info? Can you recommend a web page, or some other
reference material? Is there a specific terminology I should be using
when I search? I just don't seem to be able to find what I need.

Please reply to se***@psu.edu -- I don't read newsgroups regularly.
Many thanks in advance!

--Sue

[Thomas R. Hummel]

Hi Sue,

You can certainly accomplish this in SQL Server, but I'm not sure if
you really should or not. Typically, the database should be left to do
database things. Since the users have to enter their bids into the
website, you may want to look into generating the email at that point,
using code in the middle tier of your application. This of course
depends on your programming language there - ASP.Net, ASP, Java, etc.

If you have your mind set on doing it in SQL Server, then you will want
to look into xp_sendmail or xp_smtp_sendmail. You can pass in the email
message, so inserting column values is just a matter of concatenating
your message together correctly before calling the mail sending
procedure.

Since you want to send this email out whenever a person is outbid, then
you *could* include the send mail code in the appropriate trigger(s)
where bids are tracked. However, it is strongly advised that you do not
use code that calls external applications from within triggers. There
are a lot of potential problems there. Another method would be to have
the trigger merely insert a record into a table that serves as the
email queue. You could then have a job that runs every so often (every
hour, every 5 minutes, whatever) that checks the queue for any
unprocessed emails and sends them out.

HTH,
-Tom.

[Andy O'Neill]

"Thomas R. Hummel" <to********@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

<< Flippin new google! >>
So...
Coupla million emails in some teachers in-box?

If you have your mind set on doing it in SQL Server, then you will want
to look into xp_sendmail or xp_smtp_sendmail. You can pass in the email
message, so inserting column values is just a matter of concatenating
your message together correctly before calling the mail sending
procedure.

Since you want to send this email out whenever a person is outbid, then
you *could* include the send mail code in the appropriate trigger(s)
where bids are tracked. However, it is strongly advised that you do not
use code that calls external applications from within triggers. There
are a lot of potential problems there. Another method would be to have
the trigger merely insert a record into a table that serves as the
email queue. You could then have a job that runs every so often (every
hour, every 5 minutes, whatever) that checks the queue for any
unprocessed emails and sends them out.

HTH,
-Tom.

Definitely go for the write to table and job processes table thing.
Could perhaps become rather more complex if you want an e-bay style
automatically upping of bids.

[Susan Hales]

>Since the users have to enter their bids

into the website, you may want to look
into generating the email at that point,
using code in the middle tier of your application.

I thought of this after I posted my message, and your advice confirms
for me that this would be the best approach. I feel dumb for not having
thought of it earlier! Thanks so much, everyone, for the input. I
greatly appreciate it.

-Sue

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!