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:
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:
@body='There has been an Insert/Delete/Update in the database table'
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.
2012年3月3日 0:44版主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
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
Is the full procedure.
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
- 已标记为答案 amber zhangModerator 2012年3月12日 9:06