?
Solved

Run stored procedure in a trigger (I think)

Posted on 2008-06-18
5
Medium Priority
?
831 Views
Last Modified: 2009-07-29
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". Can anybody get me started in the right direction. Is a trigger the right direction? I might need some serious hand holding on this.
0
Comment
Question by:phishyman2
  • 3
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21816244
You can schedule a jon to do this, thats the best thing ..
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21816246
I meant, a sql Job
0
 

Author Comment

by:phishyman2
ID: 21816381
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.
0
 

Author Comment

by:phishyman2
ID: 21816570
I understand that I want to call my stored procedure like this:

EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
    @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?
0
 

Accepted Solution

by:
phishyman2 earned 0 total points
ID: 22521654
This issue has been resolved by another outside source that was beyond my control.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question