Automating email with sql server

• gamaz

  Valued Member

  Points: 59

  More actions

  January 12, 2009 at 9:50 am

  #97122

  :blink:

  Hi,

  The following is a sample table of my application

  Name EmailAddress CaseID StartDate DueDate CloseDate

  Jane Doejane.doe@test.com 2122 1/1/09 1/4/09

  Tim Jones tim.jones@test.com 3267 1/3/08 1/5/09

  Robert Stil robert.still@test.com 1324 1/2/09 1/4/09

  Jane Doejane.doe@test.com 3345 1/1/09 1/4/09 1/4/09

  Jane Doejane.doe@test.com 2454 1/1/09 1/2/09

  Robert Stil robert.still@test.com 7867 1/2/09 1/3/09

  Tim Jones tim.jones@test.com 2345 1/3/08 1/5/09 1/3/09

  Jane Doejane.doe@test.com 2989 1/1/09 1/4/09

  Now I have the following rule:

  If CloseDate is not null and CurrentDate - DueDate > 5 then each of the person should receive an email stating the

  corresponding case is still not closed.

  After three days from an email being sent if the closedate is still null for a particular case then another email need to be

  sent stating that CloseDate is still open.

  Now I need to automate this email notification process via SQL Server.

  I would like to know what is the best way to handle this in sql as there are differnt ways to handle this.

  Also, what is the best way to handle the above business rule for email automation - stored proc, query etc.

  I appreciate any resolution for this. Thanks

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  January 12, 2009 at 3:13 pm

  #925612

  Create one stored procedure and encapsulate the code in it. Use xp_sendmail for sending out mails.

  MJ