database reminders

[aish]

Hi,
I have a database table which has a Field called birthday.I want to write SQL script which automatically search database every day and if someones birthday equal today then display a reminder(alert).how can I do this? can anyone help me?

Thanks,
aish. :o

[Elohir]

Hi Aish,

There are several questions you would need to consider and answer before anyone can really help you get going in the right direction....

1) How do you want to be notified... e-mail? pop-up window? Text mail to your phone?

2) How do you want this triggered? In other words, Do you want the date check to run at 12AM every day? Or would you rather it only run when you log on to your workstation?

3) What machine is going to trigger it... the SQL server or your workstation? (Or do you have only one machine that is always on?) If you run it on the server, what do you want it to do with your notification if your workstation is not online (not an issue if you are emailing the notice)?

There are two sides of this see-saw:

If you run it on the server, triggering the event is easy, but sending the notice out gets a bit more complicated, whether using SQLMail or coding your own CDO or CDONTS (or other object) script .....

If you run it on your local workstation, especially in your login or startup script, then triggering the check and posting the notice is very easy, but getting the information from the database becomes the more difficult part requiring ODBC connections and some local scripting.

The prefered method would be to have the server trigger it at a specified time each day, and send an email to a email address that you can change as necessary.

If you can answer the above questions, I'll see how much more I can help.

:mrgreen:

Elohir

[rishit]

Hi,
I have a database table which has a Field called birthday.I want to write JSP using My SQL which automatically search database every day and if someones birthday equal today then display a reminder(alert).how can I do this? can anyone help me?

Thanks,
rishit

[aish]

Ok Elohir, these are my answers,

1) How do you want to be notified... e-mail? pop-up window? Text mail to your phone?
I woluld like to send email

2) How do you want this triggered? In other words, Do you want the date check to run at 12AM every day? Or would you rather it only run when you log on to your workstation?
I want to date check to run at 12AM every day if I log or not

3) What machine is going to trigger it... the SQL server or your workstation? (Or do you have only one machine that is always on?) If you run it on the server, what do you want it to do with your notification if your workstation is not online (not an issue if you are emailing the notice)?
My project is webbased project I use web server and the sql server

[pty]

Ok Elohir, these are my answers,

1) How do you want to be notified... e-mail? pop-up window? Text mail to your phone?
I woluld like to send email

2) How do you want this triggered? In other words, Do you want the date check to run at 12AM every day? Or would you rather it only run when you log on to your workstation?
I want to date check to run at 12AM every day if I log or not

3) What machine is going to trigger it... the SQL server or your workstation? (Or do you have only one machine that is always on?) If you run it on the server, what do you want it to do with your notification if your workstation is not online (not an issue if you are emailing the notice)?
My project is webbased project I use web server and the sql server

i think the best way to tackle this is to make a stored proc that does the check and put the details of the birthday people in a temp table, once you've got them all use xp_sendmail to send the email.

then in enterprise manager find 'jobs' (in management|sql server agent) and make a job that is run once per night and have it execute your stored proc

you could do this by just puttin the body of your stored proc in the job but its neater to do it that way