Fire trigger at certain time

Posted on 2004-11-17
Last Modified: 2012-08-13
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.

Question by:D524120
    LVL 1

    Accepted Solution

    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.

    Author Comment

    I discovered how to send a email;en-us;312839&sd=tech

    Still looking for the answer to set a trigger to shoot off each day at a certain time.

    Author Comment

    To set the dates/times etc, one has to set the SQL Agent - Jobs.  Works great

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now