FIX: Pull Subscribers Unsynchronized if Reinitialized from Subscriber Causing Distribution Failures

Article translations Article translations
Article ID: 252375 - View products that this article applies to.
This article was previously published under Q252375
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 56433 (SQLBUG_70)
Expand all | Collapse all

SYMPTOMS

When transactional publications are set up, with no support for anonymous subscriptions using SQL Enterprise Manager, the named pull subscriptions may become unsynchronized with the published database. This problem may occur when pull subscriptions are reinitialized from the subscriber. As a result, you may see errors such as primary key violations or foreign key errors returned by the distribution agent.

CAUSE

When creating a transactional publication using SQL Enterprise Manager, the @immediate_sync property for the publication is set to false in the syspublications table on the publisher if the publication does not support anonymous subscribers. However, when named pull subscriptions are created with Enterprise Manager, the @immediate_sync property is set to true in the msreplication_subscriptions table on the subscriber. The @immediate_sync property should be the same on publisher and subscriber.

RESOLUTION

Here are three workarounds for this problem. Use any of the following:
  • Use the stored procedure sp_addpublication instead of Enterprise Manager and set the @immediate_sync property to be the same as the pull subscription.
  • Use the stored procedure sp_addpullsubscription instead of Enterprise Manager and set the @immediate_sync property of pull subscriptions to be the same as the publication.
  • Do not reinitialize pull subscriptions from the subscriber. Instead, initialize from the publisher if the @immediate_sync property is different on the publisher and subscriber.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.

MORE INFORMATION

Any transactions in a distribution database that are cleaned up before subscription are not applied to the subscriber unless you generate a new snapshot. Due to the fact that the @immediate_sync property is different on the publisher and subscriber, on reinitialization of pull subscriptions from the subscriber side, the publisher is not informed about the reinitialisation and does not generate a new snapshot file. This may result in inconsistent data between the publisher and subscriber.

Properties

Article ID: 252375 - Last Review: October 20, 2013 - Revision: 2.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB252375

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com