Microsoft KB Archive/313533

= FIX: SQL Server CE subscriptions may not be correctly marked as expired =

Article ID: 313533

Article Last Modified on 9/9/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Windows CE Edition 1.1

-



This article was previously published under Q313533



BUG #: 356006 (SHILOH_BUGS)



SYMPTOMS
Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) may not properly mark subscribers as expired if the associated publication has a retention period set to a value other than zero (0). The zero setting means subscriptions never expire.

If the SQL Server CE subscribers synchronize at a point past the retention period, subscribers and publishers may become unsynchronized (also known as data divergence), or SQL Server CE may not detect conflicts between subscribers and publishers.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem, use either of these methods:
 * Create new logic in your SQL Server CE application based on a date/time value or a timer to synchronize the data before the subscription expires. You should create this timer in coordination with the retention period set for the publication.

-or-


 * Set the retention period of the publication to zero (0), which means no cleanup executes. If you set the retention period to 0, the size of the metadata tables in the publisher database could become large over time, which may lead to longer synchronization times due to poor performance of queries run on the publisher to perform the synchronization.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.



MORE INFORMATION
Without this fix, you may encounter two types of problems when you synchronize a SQL Server CE subscriber:


 * Data Divergence: Data divergence occurs when the subscriber and publisher are not synchronized. Without the fix, SQL Server CE subscribers may receive changes that are not synchronized (such as less rows than what exists at the publisher) with the publisher instead of being forced to reinitialize the subscription.

-and-


 * Conflicts Not Detected: The other scenario involves conflict detection between a subscriber and publisher. When you make updates in your SQL Server CE application, a subsequent synchronization may cause those updates to overwrite changes to the publisher without any conflict detected.

Even with the fix, you should avoid any re-initialization of your SQL Server CE subscriber after the expiration period of the publisher if you use the bUploadBeforeReinit flag property for the subscription. Any re-initialization of the subscription without the bUploadBeforeReinit flag is safe to use regardless of the expiration period of the publication. If you use the bUploadBeforeReinit flag and reinitialize, you may encounter the same conflict detection problem as described earlier.

Cleanup of metadata is performed automatically as part of the synchronization of a SQL Server CE application. When a SQL Server CE application synchronizes, the stored procedure sp_mergemetadataretentioncleanup is executed in the context of the published database to determine what metadata is safe to cleanup based on the retention period property of the publication. With the fix, you can either:
 * Allow your SQL Server CE application to run a cleanup automatically (recommended approach).

-or-


 * Manually execute the cleanup procedure in the context of the published database. Manual execution of the cleanup procedure may be a consideration if you have used a retention period of 0 and now want to set the retention period to a certain frequency. This way, the time it takes to run the cleanup procedure does not affect the performance of SQL Server CE synchronization.

The sp_mergemetadataretentioncleanup stored procedure is introduced in Microsoft SQL Server 2000 Service Pack 1. Another stored procedure, sp_mergecleanupmetadata, has existed since the release of SQL Server 2000. Microsoft strongly discourages use of the sp_mergecleanupmetadata stored procedure if you have SQL Server CE subscribers. If you run the sp_mergecleanupmetadata stored procedure, you must reinitialize your SQL Server subscriber immediately.