Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    22

    Unanswered: Sending out a mass e-mail from SQL Server at a certain time

    Hi there. Hopefully someone can help me out. Let me try and explain as best as I can what I want to do, and then if someone could point me in the right direction, I'd be grateful.

    Our internal-use website has a section called "Alert E-mails." In this ASP.NET/C# page, we create entries in an SQL table. The entries in this table make up the contents of an e-mail. One field is called "Subject", another is called "Body", and so on. Then there is an intersection table between this AlertEmails table and our Employees table, called AlertRecipients.

    So to paint a picture (abbreviated down to necessary fields only)...

    ---ALERTEMAILS table---

    AlertID | Body | Subject | SendTime
    1 | hi | hello | 2011/05/20 14:00:00

    ---ALERTRECIPIENTS table---

    AlertID | EmployeeID
    1 | 14
    1 | 19
    1 | 25

    ---EMPLOYEES table---

    EmployeeID | Name | EmailAddress
    14 | Bob | bob@data.com
    19 | Jane | jane@data.com
    25 | Judy | judy@data.com

    So basically, when the server hits 2011/05/20 14:00:00, I want the server to send out an e-mail to bob@data.com, jane@data.com, and judy@data.com with the subject "hello" and the body "hi".

    ----------------------------------------

    Now what our CURRENT version of this whole system does from the previous guy is rely on a scheduled task on another machine to check the table every hour to make sure an alert hasn't come up yet. If an alert has come up, that task reads the data, compiles it into a proper e-mail and sends it out.

    I want to streamline this down so that we're just relying on this one SQL Server to detect the alerts AND send the e-mails, all on its own, when the time is right --- no external processes, etc. As it is now, if that other machine goes down or reboots and misses its hourly check for instance, any alert that should have been sent out on that hour then just gets skipped over and lost.

    That's the story. In addition to general knowledge on how to accomplish this, I am also totally open to new suggestions or even revisions to the table structure/etc, as I'm still relatively new to all of this. It's been almost a year since I started using databases with no background and I still feel like I know absolutely nothing Any and all advice is welcome.

    It's SQL Server 2005 and the software installed on that machine is Microsoft SQL Server Management Studio.

    Thanks for your time.

  2. #2
    Join Date
    Sep 2010
    Posts
    22
    Alright, I've been looking at this throughout the day, and the bulk of my answer lies in SQL Server Agent (which I wasn't previously familiar with) to run a scheduled task every hour or so. I think I can just have it execute a Stored Procedure to get any records with the current date and time. That far I think is fine, I think I can handle that.

    Question that remains is the e-mail part: how to pull the contents of the Body field, the Title field, the e-mail addresses from the Employees table, and so on, and put them into an e-mail, and then send that e-mail from our company's e-mail address. I'm having a hard time wrapping my head around the concept of e-mail coming from an SQL Server all together --- totally clueless!

    I'll keep self-researching in the meantime while I wait for you pros.

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Since no one seems to be bitting, I'll take a stab at this.

    I have an application which activates according to a schedule stored in a table, so I have some familiarity with this issue.

    SQL Server Agent (SSA) is a great scheduling agent, but you can't schedule something from SSA based upon data in one of your tables.

    So, what I do is I have a SSA job that runs every day at midnight and looks at my table for any activities that require activitation during the next 24 hours (since the activities I need to activate can be scheduled weeks in advance, and there are few of them, this works fine for my purposes).

    If this SSA job finds a qualifying activity, I have code which creates and schedules a new SSA job for the purpose of executing that activity.

    So, I have one SSA job which creates and schedules the actual SSA jobs that accomplish the action I require.

    Works great.

    I'm not sure why I feel this way, but I might be concerned if you are looking at using this kind of system to schedule 100s or 1,000s of jobs.

    And, it sounds to me like your requirments would require that code that created the actual jobs be run off of a trigger, rather than the once-day, midnight logic that I use.

    Not sure if this helps, but if you need more details, give me a call.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Sep 2010
    Posts
    22
    Thanks for your time! Fortunately at any given time I think there will ever only be like 5-10 alerts in queue, and they are alerts in place to remind us of something at least a week in advance, so I think the way you're doing it of checking once a day is adequate enough for our purposes as well.

    Just from fiddling around with it, I figured out how to send e-mail from the server using sp_send_dbmail, and I got it to send out from the server just fine. This problem is slowly coming together.

Posting Permissions

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