[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

Cannot Resolve the Collation Conflict



Analysis Services
CLR Integration
High Availability
Open Source
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019

Cannot Resolve the Collation Conflict.

Applies to: Microsoft SQL Server 2008.

Problem Description.

After a software vendor created a new database on a SQL Server 2008 instance, we received the following error message while using SQL Server Management Studio:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation. (Microsoft SQL Server, Error:468)

Additionally, when we tried to run a query joining two tables,  one table that belongs to the database created by the software vendor and the other a temporary table, both tables joined by a common column of varchar(10) data type, then we received the following error from SSMS:

(1 row(s) affected)
Msg 468, Level 16, State 9, Line 4
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.


We configured the SQL Server instance with the SQL_Latin1_General_CP1_CI_AS because it is the standard in our organization, and that means all system databases on the server have that collation (including TempDB), but the software vendor created the new database with a different collation, collation Latin1_General_CI_AS. The conflict is originated by the difference in collation between the instance and the vendor database.


If possible change the database collation. In our case, was not possible because the vendor does not support SQL_Latin1_General_CP1_CI_AS collation. The following link gives instructions on how to change the database collation.


To change the server collation, you either have to reinstall SQL Server or rebuild system databases. We reinstalled Microsoft SQL Server and set the server collation to the collation specified by the software vendor. For more information, please see the following article:


To allow specific queries to run despite the difference on collations, you need to modify those queries and include the COLLATE or COLLATE database_default clause when comparing string columns with different collations. Please see the following article, for more information:





.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.