Web Development

Ask something
Discussion Locked

SQL Server 2000: Notify on time

0 helpful
3 replies

by bgibilaro

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

My company has an online employment application. When submitted, the contents of the application are submitted to a SQL Server 2000 database. What is the easiest way to do the following:

There is a "reviewed" column on our table. I want an email to be sent if after a certain amount of time, say 24 hours, that field is still null. On the flip side, I would also like an email to be sent once that field has been updated.

Does anyone have any ideas. This will be a huge help.

Bob GibilaroNetwork Administrator
V. Alexander & Co., Inc.

3 replies

by alterego2

For the longer time period (eg 24 hours) why not add a timestamp field to the table (initialized with a default value a row is inserted). Add a stored proc. which compares the timestamp with the current date. If 24 hours has passed then add 24 hoursto the timestamp value and email to send notification of the time value. (set the stored proc. up as a job).
To email when the field is updated either add this to the above check (if instantaneous notification is not neccessary) or set the application to email upon insertion. If your inserting via a stored proc. then you could either email via the application using cdonts etc.(might be an idea to use a return value to validate the field data before sending the mail) or you could email via sql using the stored proc.

Hope this helps.

This discussion is closed to new replies.