May 8, 2003 at 3:39 am
I have a SQL database with the following fields:
reminder_datetime (date type)
reminder_sent (bit)
reminder_to_whom (nvarchar)
notes (long text)
What I would like to do is using the SQL Server Agent / Jobs, schedule a SQL script to run every hour that looks at this table and if the date matches and a reminder has not already been sent out, send the contents of the notes within the body of an email to that person.
I can send emails using xp_sendmail with my Exchange Server sucessfully but I'm unsure on how to only send mail messages if the above condition matches !!!
Can anybody help me ?
Thanks
Mike
May 8, 2003 at 4:21 am
This might give you some ideas:
declare @to_whom varchar(255), @notes varchar(1000)
declare remider_cursor cursor for
select reminder_to_whom, notes
from <table>
where reminder_datetime <= getdate()
and (reminder_sent is null or reminder_sent = 0)
open reminder_cursor
fetch next from reminder_cursor into @to_whom, @notes
while @@fetch_status = 0 begin
/* Send email */
exec master..xp_sendmail @to_whom, @notes
/* update table */
update table
set reminder_sent = 1
where current of reminder_cursor
fetch next from reminder_cursor into @to_whom, @notes
end
close reminder_cursor
deallocate reminder_cursor
Basically, this will loop round for every record where you have not sent an email, send and email and update the table. Depending on the performance and the size of the table, you might want to include a primary key in the cursor to speed up the update statement.
You could put this code directly into a job and schedule it to run every hour or put it in a procedure and execute the procedure with the job.
Hope this gives you some ideas.
Jeremy
May 8, 2003 at 5:44 am
Jeremy,
Works like a dream....
Thanks very much for that, greatly appreciated.
Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply