Email escalation

• mirravi

  Mr or Mrs. 500

  Points: 530

  More actions

  August 6, 2010 at 8:06 am

  #98773

  Hi ,

  i woote a trigger that will fire when an record inserted onto a table and the message will be send to the recipient.

  I need to put a CC (@copy_recipients) to multi ID's.

  but mails are received to only the @recipients and first id in the @copy_recipients ignoring the second one.

  please help how can i send to multiple recipients.?

  ALter TRIGGER ibolt_integration_err_Mailer ON dbo.ibolt_integration_error AFTER INSERT AS

  DECLARE @DataObj int

  DECLARE @ErrorMsg varchar(50)

  DECLARE @ListValues nvarchar(255)

  DECLARE @ProfitCenter nvarchar(255)

  DECLARE @FromDbName nvarchar(255)

  SET @DataObj = (SELECT Rec_ID FROM inserted)

  SET @ErrorMsg = (SELECT ErrorMsg FROM inserted)

  SET @ListValues = (SELECT ListValues FROM inserted)

  SET @ProfitCenter = (SELECT ProfitCenter FROM inserted)

  SET @FromDbName = (SELECT FromDbName FROM inserted)

  --IF @price >= 1000

  BEGIN

  DECLARE @msg varchar(500)

  SET @msg = 'Record has been inserted onto table ibolt_integration_error with DataObject "' + cast(@DataObj as nvarchar) + '" Error Message as "' + @ErrorMsg + '" of listvalue "' + @ListValues + '" for the profit center "' + isnull(@ProfitCenter,'Null')+ '" and of database "' + isnull(@FromDbName,'Null') + '".'

  --'Record has been inserted onto table ibolt_integration_error with Record-ID "' + cast(@Rec_ID as nvarchar) + '" and Error Message as "' + @ErrorMsg + '.'

  --// CHANGE THE VALUE FOR @recipients

  EXEC msdb.dbo.sp_send_dbmail @recipients=N'123@abc.com',@copy_recipients = '345@abc.com;444@abc.com', @body= @msg, @subject = 'Mail from error table', @profile_name = 'sssprofile'

  END

  GO

  thanks in advance

  Ravi

• emily-1119612

  SSCarpal Tunnel

  Points: 4166

  More actions

  August 6, 2010 at 10:04 am

  #1203647

  I don't see anything wrong with the code. Switch the cc recipients and see what happens. Also check the sysmail tables in msdb.

  http://msdn.microsoft.com/en-us/library/ms187747.aspx

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  August 9, 2010 at 5:14 am

  #1204072

  mirravi (8/6/2010)

  ---

  @copy_recipients = '345@abc.com;444@abc.com'

  i am sure your email address list will be huge.So better to add a distribution list in exchange server and add that it in "@copy_recipients" parameter.

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)