How to troubleshoot collation conflict errors when executing queries that involve columns with different collations


Symptoms


When you execute a query, you encounter one of the following error messages: 
  
ErrorNumber: 446
ErrorFormat: Cannot resolve collation conflict for %ls operation.

ErrorNumber: 449
ErrorFormat: Collation conflict caused by collate clauses with different collation '%.*ls' and '%.*ls'.

ErrorNumber: 451
ErrorFormat: Cannot resolve collation conflict for column %d in %ls statement.

ErrorNumber: 456
ErrorFormat: Implicit conversion of %ls value to %ls cannot be performed because the resulting collation is unresolved due to collation conflict.

ErrorNumber: 457
ErrorFormat: Implicit conversion of %ls value to %ls cannot be performed because the collation of the value is unresolved due to a collation conflict.

ErrorNumber: 468
ErrorFormat: Cannot resolve the collation conflict between "%.*ls" and "%.*ls" in the %ls operation.

Cause


This error message is reported when queries use 2 or more columns with different collations for join and comparison purposes. This can happen if you are working with two different databases that have a different default collation or with columns that have collation explictly specified as different.
 
The above error message was generated from the following query:
SELECT * FROM master.sys.objects a INNER JOIN SharePoint_Config.sys.objects b ON a.name = b.name
 
On this server, the master databse and the SharePoint_Config database had the collations SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS_KS_WS respectively.

Resolution


  1. Review the columns used in the query that generates the error message. Evaluate and change the collation of the columns to match and avoid this error. Use the following query to find the collation settings of a column:
    • select name , collation_name from sys.columns where object_id = object_id ('TableName')
  2. If the object definition does not have an explicit collation settings for the individual columns, then review the database collation using the query
    • select name , collation_name from sys.databases order by collation_name
  3. If the database was created as part of the installation of a specific product, review the product documentation for details on supported collations. Please refer to the More Information section on some details regarding this.
  4. If the application or product allows you to change the database collation, then follow the instructions in the topic Setting and Changing the Database Collation.
  5. It is also possible that you restored a database from another server that has a different server collation. In that case, you might have to rebuild the target server to match the source server for the collation. You can follow the instructions from the topic Setting and Changing the Server Collation.
  6. If you encounter this problem when querying temporary tables, you can use the solutions discussed in the topic Setting and Changing the Column Collation.

More Information


Collation settings are maintained at the server, database and column levels. You specify the server collation when installing the SQL Server Database Engine. The System databases [master, model, tempdb] inherit the same collation as the server collation. The collation of system databases cannot be changed. When you create databases, you can specify a custom collation using the COLLATE clause or use the default server collation. When you create objects, you can specify the column level collation using the COLLATE clause.

Certain products [e.g. Sharepoint Server and Reporting Services] create databases with a collation setting that is different from the default server level collation. Please refer to the product documentation of these products to understand the requirements and limitations of collation settings.

Things to consider when you configure collation settings for SQL Server 2000 databases or SQL Server 2005 databases that are used by SharePoint Portal Server 2003, by SharePoint Server 2007, and by Windows SharePoint Services
Supportability regarding SQL collation for SharePoint Databases and TempDB

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

 

 Rule software

 Rule title

 Rule description

 Product versions against which the rule is evaluated

SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)

User database collation different from model

The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect these conditions where the collation settings for a user database are different from the model database. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2.

If you run the BPA tool and encounter a Warning with the title of Engine - User database collation different from model, then review the collation settings for the affected databases and take appropriate actions using the information provided in the Resolution section and More Information section.

SQL Server 2008
SQL Server 2008 R2

SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)

User database collation different from model

The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect these conditions where the collation settings for a user database are different from the model database.

If you run the BPA tool and encounter a Warning with the title of Engine - User database collation different from model, then review the collation settings for the affected databases and take appropriate actions using the information provided in the Resolution section and More Information section.

 SQL Server 2012