How to implement a batch process or job in SQL Server?

Question

Hi All

Let me explain a little bit what I would like to do.

1. There is a SQL Server DB with some tables
2. Form an ASP.Net web application the user can interact with the data in the tables.
3. For the purpose of this post, in that DB there is a table where the user can add, delete and update some tasks.
4. Now, based on that table and those tasks, there is a requirement where I need to send some notifications mails when a task is going to expired (when the deadline of the task is first less than 10 days and second less than 5 days.

Based on the described above, how can I do it? I have been working with SQL Server 2005, however I haven't had the opportunity to do something like this.
In SQL Server 2005, will this be achieved using SSIS? or will I need to create the batch process in C#?

I know that this will be doing with Windows workflow Fundation, but at the momment I'm working with the Framework 2.0 and as mentioned with SQL Server 2005.

Any help, clue or suggestion would be very helpful
Thanks in advance

Answer 1

The simplest way (IMHO) will be scheduling a SQL Server job that will send e-mail notifications when the task is expired.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Adding to Naomi's note.

You can schedule the job to run every night at 2:10 am for example.

Best to put the logic into a stored procedure.  Put the stored procedure execution into a job step.

Send email article:

Sending SMTP Mail using a Stored Procedure

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 3

Please check http://blog.sqlauthority.com/2007/06/27/sql-server-running-batch-file-using-t-sql-xp_cmdshell-bat-file/

It may help you.