Looking for a real-time solution

[wbodger]

When customers register a trial product in our system (we have an old version of Onyx running on SQL2K Ent that I am working on upgrading to SQL2K8 hopefully) we would like to send them an email in real-time. I think. I have been thinking about this for a while and honestly the only solutions I can come up with are: 1) a trigger on the customer table that would have to look at the product table to determine if each insert is a trial; 2) a batch process that runs every x minutes or 3) attach code to send the email onto either the sproc or the ASP page that calls the sproc.

So, does anybody out there have any thoughts on the pros and cons of these ideas and/or having any of their own to share?

Thanks,
Willie

 

[RiverGuy]

I assume your #1 means that you want to write a trigger that will send the email from SQL Server. I think this is the worst idea of the three. I don't like the idea of turning database servers into email servers.

#2 sounds ok.

#3 sounds the best to me. If it were my project, I'm not sure I would have the ASP page send the email itself. I might write a service that sends the emails that is called by the ASP page. The service wouldn't block your ASP code while the email is being sent. And by the service sending the email, I mean just that -- not a service that calls SQL Server to use database mail. You would also code it so that the call to the email send is only executed if the database transactions succeed.

 

[wbodger]

Thanks for the input. I tend to agree, so option 3 it is! Not sure why this notification ended up in my Junk mail folder...

wb