FIX: Non-Conflicting Column Updates May Cause Erroneous Metadata Mismatch Conflicts

• Microsoft SQL Server version 7.0

SYMPTOMS

Replication lineage can become invalid when performing non-conflicting column updates on the subscriber.

The Merge Agent detects conflicts through a system column called lineage, which represents the history of changes in a row. When the lineage become invalid it may manifest as metadata mismatch and/or erroneous conflicts and can lead to missing or invalid rows after a merge operation takes place.

Following is an example of an erroneous metadata mismatch conflict error that may occur:

"The row was updated at <publisher.table> but could not be updated at <subscriber.table>. Metadata mismatch"

RESOLUTION

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next SQL Server service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

   File name   Platform
   ---------------------------

   S70755i.exe   Intel
   S70755a.exe   Alpha 

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

There are several ways the conflicts can be resolved priority based, custom procedures, COM object, and others. If you receive a message or indication that conflicts occurred during the merge process and you suspect that data has not been merged properly the "Replication Conflict Viewer" is the easiest way to detect how the conflict occurred and how it was resolved. Using the utility you can observe reported conflicts. Using the following details you can determine if the reported conflict was erroneous due to the lineage mismatch.

The Merge Agent detects conflicts through a system column named lineage in MSmerge_contents, which represents the history of changes in a row. The agent updates the lineage column in MSmerge_contents automatically when a user updates a row.

The entry is a combination of a site identifier and the last version of the row created by that site. When the Merge Agent is merging changes, and it encounters a row that might have changed recently, it examines the lineage of each site’s version of the row to determine if there is a conflict. When conflicts occur, the agent initiates an automatic reconciliation.

Conflicts to the data in the base table can be recognized either at the column level or at the row level. The default option presented through the user interface is column-tracked articles. This option allows changes made to disjointed columns to be merged; only changes made to the same columns are flagged as conflicts.

The issue is with the former of the two column level operations. The row was modified at the subscriber but the same columns were not affected at other participating sites at the time of the merge operation. The result is no need to combine the row changes and or perform conflict resolution.

The conflicts can be reported at the publisher, centralized, or subscriber, decentralized, based on the setup of conflict logging in the replication topology.

The conflict resolver stores information about the conflict by creating a conflict table named conflict_usertablename. The conflict table has the same structure as the original table, and the conflict resolver copies the "losing" version of the row into the conflict table.

To determine if you are encountering this issue you can compare the columns in the row from the losing version to the data in the actual table to see if the criteria for this issue is met and only disjointed columns where merged.

We keep track of the conflicts (counts) in the publisher_conflictcount and subscriber_conflictcount columns in sysmerge_history. One has to read the conflict_tablename to identify the rows involved in conflicts. However, sysmerge_history can be queried to identify if a conflict has occurred.

Additional query words:

Keywords : kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug