Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gamaz
Posting Yak Master
104 Posts |
 Posted - 2009-01-12 : 11:19:00
|
| Hi,The following is a sample table of my applicationName EmailAddress CaseID StartDate DueDate CloseDateJane Doe jane.doe@test.com 2122 1/1/09 1/4/09Tim Jones tim.jones@test.com 3267 1/3/08 1/5/09Robert Stil robert.still@test.com 1324 1/2/09 1/4/09Jane Doe jane.doe@test.com 3345 1/1/09 1/4/09 1/4/09Jane Doe jane.doe@test.com 2454 1/1/09 1/2/09Robert Stil robert.still@test.com 7867 1/2/09 1/3/09Tim Jones tim.jones@test.com 2345 1/3/08 1/5/09 1/3/09 Jane Doe jane.doe@test.com 2989 1/1/09 1/4/09Now 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-12 : 12:14:11
|
| the email can be sent using xp_sendmail extended stored procedure or by using send mail task in DTSthe best way to handle the business rule is to write an procedure. inside that call xp_sendmail to send mails |
 |
|
|
gamaz
Posting Yak Master
104 Posts |
Posted - 2009-01-12 : 12:26:17
|
| Thanks visakh16 for your helpful reply. So you are advising to use sqlmail instead of using sqlagentmail.Could you send me any helpful link with some concept/example to get me started. Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Automating email in sql server