Run stored procedure in a trigger (I think)
I have a table which hold records that represent invitations to answer questions in a survey. When the record is created it creates an email and sends it out using ASP. The recepient has 14 days to complete the survey then the invite no longer works. In the record is a column for date created.
What I want is a reminder email to be sent out after 7 days, then with 3 days left, and then 1 day left. I am assuming I would do this with a trigger and the stored procedure "msdb.dbo.sp_send_dbmail".
What I want is a reminder email to be sent out after 7 days, then with 3 days left, and then 1 day left. I am assuming I would do this with a trigger and the stored procedure "msdb.dbo.sp_send_dbmail".
Who is Participating?
OK.
I am assuming that during the creation of this job for the first step I would set it up as Transact-SQL script. Could you give me started on what I would put in the command box, based on the situation I listed above.
I am assuming that during the creation of this job for the first step I would set it up as Transact-SQL script. Could you give me started on what I would put in the command box, based on the situation I listed above.
I understand that I want to call my stored procedure like this:
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo
@subject = 'My Mail Test',
@body = 'This is my mail test',
@body_format = 'HTML' ;
But I only want to effect records returned by this query:
select * from franr.riskassessment
where date_created + 7 = getdate()
How do I combine them together?
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo
@subject = 'My Mail Test',
@body = 'This is my mail test',
@body_format = 'HTML' ;
But I only want to effect records returned by this query:
select * from franr.riskassessment
where date_created + 7 = getdate()
How do I combine them together?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month5 days, 8 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
