January 5, 2009 at 7:15 am
Hi,
I need to send an email to a dynamic user, with dynamic body text, when new records are add to a table or when some records are updated. So I thought to create a Integration Service with an Email Task and some init on global variables for the dynamics entries. Is it possible to call the service from a trigger? Or from a SP? If so, where would it be better? In the trigger or the SP (it's a StoredProc who will insert the data in the tables) ?
thanks a lot for your time and hints,
Dominic Gagné,
Montréal, Canada
January 5, 2009 at 7:22 am
As sp_send_dbmail is already geared up to accept variables, it would make most sense to send the email directly from the trigger and not go to the extra overhead of calling another process.
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
January 5, 2009 at 8:07 am
You definitely do not want to try to start an SSIS package from a trigger. If that fails the entire transaction will be rolled back. Since DB Mail is now asynchronous I'd use Carolyn's suggestion. If that does not meet the need look into Service Broker so you can do it asynchronously or do it in the SP.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 8:41 am
Thanks a lot folks for your great help. I didn't knew about SQL Mail. I configured it, called the sp_send_dbmail and it worked great!!
thanks again!!!!
January 5, 2009 at 9:02 am
This of course assumes that you are using sql 2005.
Sorry to go this basic... but if you made a tiny posting error and are using sql 2000, you need to send the mail outside of the trigger or risk massive failures when the mail starts failing and rolling back the changes... or making the users wait for ages.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply