FIX: Replication with Immediate-Updating Subscribers Fails with Error 21082

This article was previously published under Q241448
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 57295 (SQLBUG_70)
If you have configured replication with immediate-updating subscribers, you may receive the following error during the initial synchronization:
Error 21082
Database property 'IsRecursiveTriggersEnabled' has to be true for subscribing databases at updatable subscribers.
When you view the Distribution Agent Error Details window, you will see that the Last Command will be similar to:
{call sp_addsynctriggers (N'TABLE1', N'null', N'SERVER1', N'test_repl', N'test_repl', N'sp_MSsync_ins_TABLE1_1', N'sp_MSsync_upd_TABLE1_1', N'sp_MSsync_del_TABLE1_1', N'dbo', N'null', N'msrepl_synctran_ts', N'null', 0x01)} 				
This error occurs if the Recursive Triggers option is enabled in the subscribing database.
The error message returned is incorrect. It should read:
Error 21082
Database property 'IsRecursiveTriggersEnabled' has to be false for subscribing databases at updatable subscribers.
While doing initial synchronization for an immediate-updating subscriber, SQL Server calls sp_addsynctriggers, which creates immediate-updating triggers at the subscriber. The sp_addsynctriggers stored procedure checks to determine if the database option 'Recursive triggers' is set to TRUE for the subscribing database. If it is set to TRUE, sp_addsynctriggers fails with the error described in the SYMPTOMS section of this article. However, in the error message, the word "true" should actually read "false".
To work around this problem, disable the Recursive Triggers option on the subscribing database. To do this, you can either use the sp_dboption stored procedure, or use SQL Server Enterprise Manager by doing the following:
  1. In Enterprise Manager, right-click the subscribing database and click Properties on the shortcut menu.
  2. On the Options tab, clear the Recursive Triggers check box.
  3. Click OK.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
For more information on setting up replication with immediate-updating subscribers or on recursive triggers, see the SQL Server 7.0 Books Online.
repl sync synch synced synched synchronize synchronized synchronization dist task job err msg updateable

Article ID: 241448 - Last Review: 10/22/2013 00:33:10 - Revision: 2.1

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix KB241448