Microsoft KB Archive/308843

= PRB: Non-Unique Clustered Index on a Published Table Causes Incorrect Data to Replicate to Subscribers =

Article ID: 308843

Article Last Modified on 10/30/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308843



SYMPTOMS
In a transactional publication, if you add a non-unique index on a table that you have defined as an article, incorrect data may replicate to Subscribers. For example, blank spaces might be included for the variable length columns.



CAUSE
The Replication LogReader Agent uses the column offsets to generate commands that replicate to the Subscriber. If you create a non-unique clustered index on a published table or if you drop a unique clustered index and replace it with a non-unique clustered index, the column offsets for all variable length columns change. The column-offset changes cause the Replication LogReader Agent to generate invalid commands.



WORKAROUND
To work around this problem:
 * Do not add a non-unique clustered index on a published table that belongs to Transactional Publication.

-or-


 * If you need to add a non-unique clustered index to a published table (after the table replicates to a Subscriber):


 * Drop the existing subscriptions.
 * Add the new index.
 * Re-create the subscriptions.



MORE INFORMATION
By default, SQL Server adds a clustered index on the column that is set as the primary key. However, based on the database design you might choose to create the primary key with a non-clustered index. You may need to create the unique clustered index on another non-primary key column for design reasons.

By default, replication propagates the clustered index of a published table to the subscriber during the initial synchronization process (snapshot process). If you add a clustered unique index to a table after the publication of the table, the Replication LogReader Agent takes into account the column offsets, which then causes the distribution of incorrect commands.

With SQL Server 2000 Service Pack 2, a new error displays if you add a non-unique clustered index to a table that is included in a transactional publication. The error text presented is:

Error 1960, Cannot create a non-unique clustered index on a table after it is published for transactional replication. Drop all publications that include this table before creating the index.

The SQL Server 2000 Service Pack 2 upgrade does not correct existing non-unique clustered indexes that you added prior to applying SQL Server 2000 Service Pack 2. You must correct those indexes by using the suggestions in the Workaround section of this article.

Keywords: kbprb kbpending KB308843

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.