Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34

    Smile Unanswered: Scheduled Stored Procedure

    Can someone help me find a source of information or give me an example of a scheduled strored procedure ... is it even possible?

    Here's the scenario:

    I have a field in one of my tables "date_due," once a day I need to check this field and send an email to the owner of any record (thier email address is also stored in this table) where "date_due" is equal to today.

    Any help would be greatly appreciated ... thanks!
    -Wigz

    ------------------------------------------------
    docendo discimus.

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Write a DTS Package. Use an ActiveX object and write some VB code to connect to the DB, run the SP, then send out the necessary emails based upon the results of your SP (remember to close your DB connection at the end). Then schedule the DTS Package.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Write a procedure to check the table. Use xp_sendmail to email to the appropriate recipients. Use SQL Server Agent to schedule a job that runs the procedure once a day. All this can be done easily and is easily researched in Books Online.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    derrickleggett,

    Is xp_sendmail native to SQL Server 7.0+ or does it need to be set up seperately? Let me know ... thanks!
    -Wigz

    ------------------------------------------------
    docendo discimus.

  5. #5
    Join Date
    May 2004
    Posts
    1

    xp_sendmail

    xp_sendmail is part of SQL Server (including 7).

    exec @Severity = master..xp_sendmail
    @recipients = @Recipients
    , @subject = @Subject
    , @message = @Message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •