I am about to work on a new project and i need some insight and maybe some
directional advice.
Working on SQL server 2000. Its a chain process im designing, a user submits
data via web(asp) into db. I want some kind of automation that would, say
check each half day if the the form/request has been accepted by the person
it was assigned to. If he/they didn't accept/decline the reuqest i.e its
still pending - i need an email to be sent to him/them as a reminder.
The email will contain data/summary of the request i.e. data needs to be
queried from db.
So first do the check all data to see which request need reminder emails,
create mails for each request with data in it as summary addressed to the
assigned person per request, run this every half day.....

So my questions:
-how should i do this?
-stored procedure? if so can i schedule it?
-dts(havnt used much but all forums results on google have to do something
with dts)
-how do i schedule this? sqlagent, a servcie on running on the server?
-what should i use as email? i have a mail server running too on the iis
server.

plz help...give me a flow and technologies to use at each state

thx

Whilst you could use DTS to do this I think maybe a windows service is
more the order of the day.

In DTS

Using SQL Server Agent to Schedule
You would need to create a Global Variable Rowset containing the details
of the people to whom you need to send a reminder (This would be through
a WHERE clause)
Iterate over this and change the details of the send mail task.

2 articles will do this for you

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

Changing the DTS Send Mail Task
(http://www.sqldts.com/default.aspx?235)



"Jatin" <Jatin (AT) discussions (DOT) microsoft.com> wrote