Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/252375

From BetaArchive Wiki
Knowledge Base


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

Article ID: 252375

Article Last Modified on 3/14/2006



APPLIES TO

  • Microsoft SQL Server 7.0 Standard Edition



This article was previously published under Q252375

BUG #: 56433 (SQLBUG_70)

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.


Additional query words: pull subscriptions anonymous named reinitialisation expiry cleanup synchronisation

Keywords: kbbug kbfix KB252375