October 18, 2005 at 11:15 pm
I have a Column in a Table called TraceDate of DATETIME DataType. I also have another Column in the same table called TRStatus (VARCHAR(10). This value can only be 'Open' or 'Closed'
Lets say a value in the TraceDate Column is 2005-10-19.
What I need to do is send an email 7 days later if the TRStatus Column is still displaying 'Open'. In the above example, an email will be sent out on the 26th (2005-10-26).
I have no problems in sending the email via xp_sendmail however, how do I program it to send the email 7 days later? Keep in mind that there could be x amount of records not just 1.
I'm very confused on how to proceed with this challenge!
October 19, 2005 at 12:53 am
A job that runs once a day would work.
Job checks for all records where datediff(dd,TraceDate,GETDATE())=7 AND TRStatus='Closed' The =7 ensures that the same record won't get flagged two days in a row.
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 20, 2005 at 5:54 am
A bit risky IMHO... what if the mail fails one day and sends nothing? The user won't get this info any more unless you run a special query. We monitor several things in this way and always send ALL overdue rows until someone does what he/she is supposed to do :-)). So that would mean not =7 but >=7 in this particular case.
You can put at the beginning of your mail-sending job "IF EXISTS...." in case you only want to send the mail if there are any rows that are overdue... sometimes it is better to send mail only if there is a problem, sometimes sending it daily (even when the body is empty) is preferable solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply