Say I have a record in a table, and this record has a "DATETIME" column; It is a DUE DATE and TIME. Whenever the server date reaches that date and time, it should notify the user (execute procedure that sends email notification for example, to the corresponding user). How would I do that? Some sites mentioned Windows Service scheduling, and others mentioned Maintenance Job.
Can you please suggest the best solution for this?
If you have to show notification to user for starting service than you can use Window services , which notify user to start the script to send mail by triggring click of button or some event.. Simple Windows Service which sends auto Email alerts
If you want to send email to users without notifying admin on perticular date time than you should go for the SQL JOBS which do work for you. SQL Server jobs
You'd have to regularly poll the table somehow, from a client, windows service or SQL Server Agent. Of course, you don't know if it will be picked up or picked up twice: it is possible
I'd consider creating a SQL Server Agent job when the row is inserted to pick this up if you don't have too many (for managability rather then any upper limit)