setting a schedule

[Cullen411]

Is there a way to schedule SQL server to send an email.

For instance if I had a smalldatetime column and wanted to send an email to all customers whose subscription was over a year from the value in the smalldatetime could this be done?

 

[SQLDenis]

create a job (schedule it daily) that will check the table and send out an email
use xp_sendmail

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[fredericofonseca]

One way is to have a daily DTS task that select all records from that table where the field value + 1 year is equal to system date.

The resulting recordset could then be used to send an email to each recipient.

You would need to cater for situations where the SQL server was down for one or more days. (e.g. set another table with "sent emails" and check if a particular ID has already been sent.

This is just an idea. If you search for email on this forum you will find some thread on how to email stuff. For more information regarding DTS you can also search the forums, but also search your copy of Books Online and

http://www.microsoft.com.

http://www.sqldts.com/

will also be of great use.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com