auto send of email

adamhw · July 18th, 2012, 03:48 AM

Not sure if this is the right category but here goes!

I'm wanting to send out an email from sql immediately after a date has passed.

In my sql server table I have a datetime field called enddate - immediately after that date I need to auto send an email out from sql

Is there a way to do this? I'm using asp as my scripting language. Perhaps I need to be able to have some kind of script that checks the datetime every 15 minutes or something?

If someone could point me in the right direction that would be great.

thanks

Adam

Imar · July 19th, 2012, 03:19 AM

Hi Adam,

A common way to do this is to schedule an application on the server that does this. Could be a command line tool written in .NET that accesses the database and sends out the e-mail. It could also be a simple vb script or something similar that requests a URL of a web page or web service that executes the code for you. Finally, SQL Server has powerful scheduling and package execution options as well (with SSIS, SQL Server Integration Services) but that might be a bit overkill.

Cheers,

Imar

adamhw · July 19th, 2012, 07:14 AM

thanks as always for your response Imar.

I'm using sql express actually so not sure it handles tasks.

How would I go about writing a vb script could I use to keep checking the date on the server to check the date field in my database table?

thanks

Adam

Imar · July 19th, 2012, 07:34 AM

Quote:

could I use to keep checking the date on the server to check the date field in my database table?

Not sure what that means.

I mentioned the VB script as a means to call a URL (a web page, a service maybe) that would execute the code at the server. You could also do what you need to do directly in the VB Script file; it enables you to do stuff similar to classic ASP, such as access a database using ADO.
Cheers,

Imar

adamhw · July 19th, 2012, 07:49 AM

Hi Imar

thanks for that - sorry, what I mean't was - how would one go about writing a piece of asp scripting that would continuously post to the server to check the database to see if that date had been reached?

thanks

Adam

Imar · July 19th, 2012, 08:32 AM

You wouldn't necessarily post to the server, nor would you do this continuously. Here's what I would do:

1. If you control the server, write a vb script file that accesses the database and does whatever you need it to do. The code you need is similar to the classic ASP code you're already using. Then use Windows Scheduler on the server to execute this script file every X minutes.

2. If you don't control the server, write an ASP file that accesses the database and does whatever you need it to do. Save this file inside your web site. Then, on another server that you do control, write a VB script that requests this page. Then use Windows Scheduler on the server to execute this VB script file every X minutes. The script then in turn calls your classic ASP page which does the database work.

There are many other solutions available as well, including Scheduler controls for ASP.NET. However, I find option 1 the easiest to do. Rather than a vb script file you can of course also use a .NET Command Line tool or any other type of app. I just mentioned vb script as it enables you use your existing classic ASP skills.

Cheers,

Imar