none
RDA DB Sending an email from trigger

    Question

  • Greetings once again folks!

    I have successfully implemented RDA into my application, everything has been going well!

    I wanted to create a trigger that sends a mail when there is an "Insert/Select/Update" on any of the tables in the database.

    I set this up fine and when I do a select statement directly on the SQL server an e-mail is generated. When syncing however I get an error during the push.

    I read in the following post:

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/758a656c-5a72-478a-8053-19fb944a7c93 

    That in a similar environment, he did the same thing and got the same error. So I setup my trigger to insert a new row into a new table, this table has a trigger enabled that executes the following stored procedure:

    EXEC msdb.dbo.sp_send_dbmail

    profile_name='SQL Admin',
    @recipients='some@mail.com',
    @subject='Changes',
    @body='There has been an Insert/Delete/Update in the database table'

    END

    So to cut a long story short. If you comment out the SP, RDA Works.

    If you don't RDA but make an insert, you get a mail.

    If you RDA with the trigger sending a mial, push fails.

    Any insight would be helpful.

    d3

    Friday, March 02, 2012 2:48 PM

Answers

  • Hi D347hm4n,

    Regarding to the description, you could create a trigger for UPDATE, INSERT, DELETE that would send an e-mail to a user notifying them of the listed transactions using sp_send_dbmail. You can refer to David’s code in this thread with the same scenario as yours.

    Meanwhile it may be better to INSERT the info to an email queue table and schedule a stored procedure with SQL Server Agent to do the emailing based on the email queue table when you send email from trigger. For more information, please refer to here.

    If you followed the thread as you mentioned it should work. If that doesn't work, try using profiler and see what commands are actually being sent.


    Regards, Amber zhang

    Wednesday, March 07, 2012 6:41 AM
    Moderator

All replies

  • And you have set nocount on, and do not see any additional result sets from the sproc?

    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    Saturday, March 03, 2012 12:44 AM
    Moderator
  • Yes I have set no count:

    ALTER TRIGGER [dbo].[Email_Trigger] ON [ehc_DurhamHIS].[dbo].[_Table_Changes] AFTER INSERT,DELETE,UPDATE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here

    EXEC msdb.dbo.sp_send_dbmail

    profile_name='SQL Admin',
    @recipients='some@mail.com',
    @subject='Changes',
    @body='There has been an Insert/Delete/Update in the database table'

    END


    Is the full procedure.

    d3

    Monday, March 05, 2012 9:03 AM
  • Hi D347hm4n,

    Regarding to the description, you could create a trigger for UPDATE, INSERT, DELETE that would send an e-mail to a user notifying them of the listed transactions using sp_send_dbmail. You can refer to David’s code in this thread with the same scenario as yours.

    Meanwhile it may be better to INSERT the info to an email queue table and schedule a stored procedure with SQL Server Agent to do the emailing based on the email queue table when you send email from trigger. For more information, please refer to here.

    If you followed the thread as you mentioned it should work. If that doesn't work, try using profiler and see what commands are actually being sent.


    Regards, Amber zhang

    Wednesday, March 07, 2012 6:41 AM
    Moderator