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.
(Microsoft.SqlServer.ConnectionInfo)
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.



Cause.


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.


Solution.


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.

http://msdn.microsoft.com/en-us/library/ms175835.aspx

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:

http://msdn.microsoft.com/en-us/library/ms179254.aspx

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:

http://msdn.microsoft.com/en-us/library/ms184391.aspx


 

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