|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
problems sending email from a triggerMS Sql Server 2005 My trigger below isn't sending emails, the problem is in this code "@recipients= 'select userEmail from inserted', ", I replaced that select statement with a real email address and then it works Any suggestions on what I should do make the select statement work is very welcome create trigger Send_Email on Test_Table for update as set nocount on; exec msdb.dbo.sp_send_dbmail @profile_name='newbie', @recipients= 'select userEmail from inserted', @subject='test', @body='some text here', @body_format='TEXT'; go Jeff wrote:
Show quote > Hey Operations such as this are just asking for trouble when placed into a > > MS Sql Server 2005 > > My trigger below isn't sending emails, the problem is in this code > "@recipients= 'select userEmail from inserted', ", I replaced that select > statement with a real email address and then it works > > Any suggestions on what I should do make the select statement work is very > welcome > > create trigger Send_Email on Test_Table for update > as > set nocount on; > exec msdb.dbo.sp_send_dbmail > @profile_name='newbie', > @recipients= 'select userEmail from inserted', > @subject='test', > @body='some text here', > @body_format='TEXT'; > go > > trigger. Ignoring your actual problem for a minute, what will happen if DBMail hangs? Your trigger will be hung, resulting in a hung transaction with locks that don't get released. If DBMail performs poorly and takes 2 minutes to send a message, your trigger will take 2 minutes to complete, resulting in a transaction that hold locks open for 2 minutes. The proper way to do what you're trying to do is to stuff the list of email addresses (SELECT userEmail FROM inserted) into a staging table. That's all your trigger needs to do. A seperate process, running from a scheduled job, will monitor that staging table. When it sees a new record in the table, it grabs that record, sends an email to the address contained within, then deletes the record. Rinse, repeat. > scheduled job, will monitor that staging table. When it sees a new record Or marks the row as processed, in case you want to keep the rows around for > in the table, it grabs that record, sends an email to the address > contained within, then deletes the record. auditing purposes. If these are going out to end users outside of your balance sheet, with the level of spam and phishing complaints around e-mail, it might not be a bad idea to keep track of it... A wow, thanks for that great advice.
Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:44FEE7BB.5080205@realsqlguy.com... > Jeff wrote: >> Hey >> >> MS Sql Server 2005 >> >> My trigger below isn't sending emails, the problem is in this code >> "@recipients= 'select userEmail from inserted', ", I replaced that select >> statement with a real email address and then it works >> >> Any suggestions on what I should do make the select statement work is >> very welcome >> >> create trigger Send_Email on Test_Table for update >> as >> set nocount on; >> exec msdb.dbo.sp_send_dbmail >> @profile_name='newbie', >> @recipients= 'select userEmail from inserted', >> @subject='test', >> @body='some text here', >> @body_format='TEXT'; >> go > > Operations such as this are just asking for trouble when placed into a > trigger. Ignoring your actual problem for a minute, what will happen if > DBMail hangs? Your trigger will be hung, resulting in a hung transaction > with locks that don't get released. If DBMail performs poorly and takes 2 > minutes to send a message, your trigger will take 2 minutes to complete, > resulting in a transaction that hold locks open for 2 minutes. > > The proper way to do what you're trying to do is to stuff the list of > email addresses (SELECT userEmail FROM inserted) into a staging table. > That's all your trigger needs to do. A seperate process, running from a > scheduled job, will monitor that staging table. When it sees a new record > in the table, it grabs that record, sends an email to the address > contained within, then deletes the record. Rinse, repeat. > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Or have the trigger write the mail into a Service Broker queue (although
DBMail does this already to avoid the problems you mentioned). -- Show quoteThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:44FEE7BB.5080205@realsqlguy.com... > Jeff wrote: >> Hey >> >> MS Sql Server 2005 >> >> My trigger below isn't sending emails, the problem is in this code >> "@recipients= 'select userEmail from inserted', ", I replaced that select >> statement with a real email address and then it works >> >> Any suggestions on what I should do make the select statement work is >> very welcome >> >> create trigger Send_Email on Test_Table for update >> as >> set nocount on; >> exec msdb.dbo.sp_send_dbmail >> @profile_name='newbie', >> @recipients= 'select userEmail from inserted', >> @subject='test', >> @body='some text here', >> @body_format='TEXT'; >> go > > Operations such as this are just asking for trouble when placed into a > trigger. Ignoring your actual problem for a minute, what will happen if > DBMail hangs? Your trigger will be hung, resulting in a hung transaction > with locks that don't get released. If DBMail performs poorly and takes 2 > minutes to send a message, your trigger will take 2 minutes to complete, > resulting in a transaction that hold locks open for 2 minutes. > > The proper way to do what you're trying to do is to stuff the list of > email addresses (SELECT userEmail FROM inserted) into a staging table. > That's all your trigger needs to do. A seperate process, running from a > scheduled job, will monitor that staging table. When it sees a new record > in the table, it grabs that record, sends an email to the address > contained within, then deletes the record. Rinse, repeat. > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Hi,
Sample changes to your code below. I added a check for @@ROWCOUNT at the top so the trigger doesn't do anything if there are no rows updated (could happen if a constraint aborts the UPDATE.) Also added a [TOP 1] to prevent an error in case there were multiple row updates. However, only one email will be sent in that case. CREATE TRIGGER Send_Email ON Test_Table FOR UPDATE AS IF @@ROWCOUNT = 0 RETURN SET NOCOUNT ON; DECLARE @UserEmail varchar(200) SELECT TOP 1 @UserEmail = UserEmail FROM inserted ORDER BY UserEmail EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'newbie', @recipients = @UserEmail, @subject ='test', @body ='some text here', @body_format ='TEXT'; GO -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Jeff" <it_consultant1@NO_SPAM.hotmail.com> wrote in message news:uxvhHJc0GHA.772@TK2MSFTNGP05.phx.gbl... > Hey > > MS Sql Server 2005 > > My trigger below isn't sending emails, the problem is in this code > "@recipients= 'select userEmail from inserted', ", I replaced that select > statement with a real email address and then it works > > Any suggestions on what I should do make the select statement work is very > welcome > > create trigger Send_Email on Test_Table for update > as > set nocount on; > exec msdb.dbo.sp_send_dbmail > @profile_name='newbie', > @recipients= 'select userEmail from inserted', > @subject='test', > @body='some text here', > @body_format='TEXT'; > go > > @recipients= 'select userEmail from inserted',
1) you can't have a query as a string like that and expect it to be magically interpreted and executed. 2) what happens if your update affects four rows? Contrary to popular misconception, a trigger fires once per statement, not once per row. First, you will need something like this: IF @@ROWCOUNT = 1 BEGIN DECLARE @to NVARCHAR(4000); SELECT @to = userEmail FROM inserted; EXEC msdb.dbo.sp_send_dbmail @recipients = @to, ...; END IF @@ROWCOUNT > 1 BEGIN -- now you need something here -- to loop and either concatenate -- or send an e-mail for each row -- affected, UGH UGH UGH UGH END However, in a more general sense, sending an email from a trigger is a very bad idea. I would rather insert the e-mail addresses into a queue table and have a job wake up every minute and send an e-mail for each row. This would prevent all the contention this trigger is going to have, and solves the multi-row update problem quite cleanly. A Show quote "Jeff" <it_consultant1@NO_SPAM.hotmail.com> wrote in message news:uxvhHJc0GHA.772@TK2MSFTNGP05.phx.gbl... > Hey > > MS Sql Server 2005 > > My trigger below isn't sending emails, the problem is in this code > "@recipients= 'select userEmail from inserted', ", I replaced that select > statement with a real email address and then it works > > Any suggestions on what I should do make the select statement work is very > welcome > > create trigger Send_Email on Test_Table for update > as > set nocount on; > exec msdb.dbo.sp_send_dbmail > @profile_name='newbie', > @recipients= 'select userEmail from inserted', > @subject='test', > @body='some text here', > @body_format='TEXT'; > go >  |
|||||||||||||||||||||||
Other interesting topics