FIX: "Cannot resolve the collation conflict" error when you apply a snapshot to the subscriber database in SQL Server

Symptoms
Consider the following scenario:
  • You set up a Transactional replication environment in which the publisher is an on-premises database.
  • The on-premises database is hosted on a server that's running Microsoft SQL Server 2012 Service Pack 3 (SP3), Cumulative Update 7 for SQL Server 2012 Service Pack 2 (SP2), SQL Server 2014, or 2016.
  • The subscriber is an Azure SQL database.

However, when you apply the snapshot to the subscriber database, the Distribution Agent fails. Additionally, you receive an error message that resembles the following:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
(Source: MSSQLServer, Error number: 468)

Cause
This issue occurs because the collations of the catalog databases for Azure SQL Database differ from the collations of the replication databases at the publisher and the subscriber.
Resolution

Cumulative update information

This issue is fixed in the following updates:
Recommendation: Install the latest cumulative update for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
Workaround
To work around this issue, do one of following:
  • Downgrade from SQL Server 2012 SP3 to Cumulative Update 4 (build 11.00.5569) for SQL Server 2012 Service Pack 2.
  • Change the collations of the involved databases to be the same as the server collation on Azure SQL Database (SQL_Latin1_General_CP1_CI_AS).
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
Learn about the terminology that Microsoft uses to describe software updates.
Properties

Article ID: 3131443 - Last Review: 07/26/2016 08:44:00 - Revision: 8.0

Microsoft SQL Server 2012 Service Pack 3, Microsoft SQL Server 2012 Service Pack 2, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core

  • kbqfe kbsurveynew kbfix kbexpertiseinter KB3131443
Feedback