Fire trigger at certain time

I am using SQL Server v8.  In my web application and in the following scenario, is my solution possible?  If so, how would I do it?

A user wants to be notified 2 days before their membership expires.  I am gathering that I could fire a trigger at 1:00 am to search the DB, retrieve the users emails from the DB, and send an email through the DB.  I am assuming that SQL Server could fire the trigger at 1:00, run the query, and send a email.  Is this possible in SQL Server?  

I know the email part is possible in Oracle and I believe that Oracle can also fire a trigger at a certain time as well, but is it possible in SQL Server?  Is so, how?

I am not a DB wizard by any means, so have your answers fairly descriptive.

D524120Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mjm42Commented:
It is possible to do:

First you have to create a Job. Go to Management, SQL Server Agent, Jobs and select New Job (right mouse click)
In the Job properties you can set the following options:

general: yust a name and som info
Steps: Create a new step with the query to select the users followed by a call to the extended procedure xp_sendmail to send the mail.
Schedules: Set the schedule to run this job at certain dates and times
Notifications: Fill in what you like

To send mail form SQL Server you have to make a mapi profile on the server with outlook.
Use this mapi profile in SQL Mail (Go to Support Services, SQL Mail)
Use the same mapi profile in the properties in SQL Server Agent (first tab)

To get the mail to work it is very important that the the services SQL Server and SQL Server Agent use the same domain user to run as the Exchange mailbox you use in the mapi profile.

Please let me know if this is not descriptive enough or you have any problems.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
D524120Author Commented:
I discovered how to send a email http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech

Still looking for the answer to set a trigger to shoot off each day at a certain time.
0
D524120Author Commented:
To set the dates/times etc, one has to set the SQL Agent - Jobs.  Works great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.