October 16, 2014 at 8:55 am
We have a process(stored procedure based) that we use to send out emails with varying content to our contract employees. Currently the developers will modify a stored procedure to change the actual 'Content' of the email, and loop through a table of recipients, We use EXEC @RtnCode = msdb.dbo.sp_send_dbmail to send the mail and update the 'sent' column to 'Y' when the return code = 0.
I don't believe this very efficient and I'd like to automate this process so we don't have to modify the SP with 'content' each time.
So i am asking for Advice on the best method to use to send hundreds of emails based on a changing paragraph of content that wouldn't require changes to SP each time the mail goes out. I don't think this is what SSRS would best for? and creating a GUI to pass the paragraph seems like a lot of work?
Thanks
October 16, 2014 at 11:17 am
Is the content "static"? Do you have a set number of content entries that are always used? If so, put them into a table, have a column to identify each row:
1 Content entry one
2 Content entry two
Then have the stored procedure grab the appropriate content based on a parameter passed to the stored proc. EXEC sp_sendmyemail @contentid = 2
The identifying column can be a number (but then you have to remember the numbers) or some text that can be easily remembered.
OOO J Smith is out of office today
HOL The office is closed due to holiday
Then you just have to call it like EXEC sp_sendmyemail @contentid = 'HOL'.
-SQLBill
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply