FIX: Capitalization and accent changes on a column of user-defined data type are not propagated in SQL Server 2005 merge replication or in SQL Server 2008 merge replication

Article translations Article translations
Article ID: 975134 - View products that this article applies to.
Microsoft distributes Microsoft SQL Server 2005 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release.Microsoft distributes Microsoft SQL Server 2008 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release.
Expand all | Collapse all

SYMPTOMS

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.

CAUSE

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.

RESOLUTION

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:
974648 Cumulative update package 6 for SQL Server 2005 Service Pack 3
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:
960598 The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released
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:
975976 Cumulative update package 8 for SQL Server 2008
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:
956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released

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:
975977 Cumulative update package 5 for SQL Server 2008 Service Pack 1
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:
970365 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released
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.
{
	exec mydatabasename..sp_vupgrade_mergeobjects
}


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

sp_vupgrade_mergeobjects (Transact-SQL)

WORKAROUND

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.

STATUS

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

REFERENCES

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:
935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
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:
913089 How to obtain the latest service pack for SQL Server 2005
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:
http://go.microsoft.com/fwlink/?LinkId=131442
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:
822499 New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates

Properties

Article ID: 975134 - Last Review: October 23, 2009 - Revision: 1.1
APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
kbexpertiseadvanced kbsurveynew kbqfe kbfix KB975134

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com