• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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.

0
D524120
Asked:
D524120
  • 2
1 Solution
 
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
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now