Consider the following scenario:

• In Microsoft SQL Server 2005 or in Microsoft SQL Server 2008, you set up a merge replication between two databases, and these databases are case-sensitive and accent sensitive.
• In the replication, you publish a table article with column tracking enabled.
• The published table article contains a column of user-defined data types (UDT). The UDT is derived from one of the following base data types:
  • char
  • varchar
  • nchar
  • nvarchar
• In the table article, you have a nonclustered index that covers the UDT column.
• You run a singleton update statement (an update to a unique column that affects only one row) on one of the existing rows on the publisher or on the subscriber. By using this update statement, you update only the capitalization or the accent of the characters in the UDT column.

In this scenario, when you synchronize the data, the changes to the column may not be propagated.

Note Batch updates are not affected by this problem, only singleton updates are affected.

This problem occurs because the update procedure does not correctly compare the data in the UDT column before and after the update operation to make sure that the column is changed.

SQL Server 2005 Service Pack 3

The fix for this issue was first released in Cumulative Update 6 for SQL Server 2005 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

The release version of SQL Server 2008

Important You must install this fix if you are running the release version of SQL Server 2008.

The fix for this issue was first released in Cumulative Update 8. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

SQL Server 2008 Service Pack 1

Important You must install this fix if you are running SQL Server 2008 Service Pack 1.

The fix for this issue was first released in Cumulative Update 5 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Installing the cumulative update package alone does not regenerate the affected update procedures. Therefore, additional action is required to resolve the problem that is described in the "Symptoms" section.

The fix for this problem is within the sys.sp_MSmakeupdateproc internal system stored procedure. This internal system stored procedure is used at both the publisher and subscriber replicas. It is used at various times, and enables replication to dynamically build the syntax for the singleton update procedures for each table article. The singleton update procedures, that are built dynamically, are named with the convention MSmerge_upd_sp_<guid> where <guid> is the article's ArtId. The singleton update procedures have to be regenerated from scratch to include the fixed Transact-SQL (T-SQL) code to avoid this problem.

After you install this hotfix, you can use one of the following methods to make the fix take effect.

For New merge publications

If you are preparing to set up a new merge replication publication, it is sufficient to install this hotfix on the publisher, distributor, and subscriber SQL Server instances before you create the merge publication. No additional actions are required.

When you create the new publication, the affected update procedures will be generated by using the newly installed code from the master and the resource databases. Additionally, during the initial synchronization, the merge agent will deliver the appropriate procedure definition to the merge subscribers.

The subscribers also require this update. If schema changes occur at the subscriber or article property changes occur, the update procedures may be regenerated locally at the subscriber by using the version of the sys.sp_MSmakeupdateproc procedure which exists in both the subscriber instance master and resource databases.

For existing merge publications

If you have an existing merge replication topology affected by this problem, one approach to resolve the problem is to install the cumulative update package and then re-create the merge publication from scratch. However, this is invasive and requires additional downtime, and reinitialization of subscriber data.

Instead, the less invasive approach is to install the cumulative update hotfix on all the publisher, distributor, and subscriber replica servers. After you install the fix on all the servers, you can run the sp_vupgrade_mergeobjects system procedure within the context of every merge replicated database that is experiencing the problem. This regenerates the upgrade procedure code locally to include the fixed Transact-SQL code.

After you install the cumulative update, run this procedure to regenerate the stored procedures including the fixed code. You must run this stored procedure for each merge replicated database for both the publishers, and for the subscribers.


For more information, visit the following Microsoft Developer Network (MSDN) Web site:

To work around this problem, use one of the following methods:

Method 1

You can include "char" in the user-defined data type name so that the replication procedures will detect that the data type name resembles the following:
%char%
Therefore, the column is considered as a character-based column and binary comparisons will be used when SQL Server delivers values. For example, if you have a UDT named ‘myType’, you can rename the UDT to ‘mycharType.'

Method 2

You must not use an index to cover the UDT column in the affected table article. When there is no index on the column, the replication procedures are less selective about how to optimize the data, and will bypass the procedure code which causes the problem.

Method 3

You can use row level tracking instead of column level tracking. However, this method is not always possible for the business requirements of a complex replication topology.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base: For more information about how to obtain SQL Server 2005 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base: For more information about the new features in SQL Server 2005 Service Pack 3 (SP3) and about the improvements in SQL Server 2005 SP3, visit the following Microsoft Web site: For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base: For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: