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
