April 28, 2012

Cannot resolve collation conflict for column 1 in SELECT statement.

In MS SQLSERVER, When you try to concatenate, compare, make joins on columns (defined with different collations), you will get the following error message:

Cannot resolve collation conflict for column 1 in SELECT statement.

In MS SQLSERVER, collation can be set at column level. You can resolve the issue in different ways :
  1. Define same collation for each column in the subject query (Right click TableName > Design/Modify > Select Column > set Collation property in TableDesigner section of Column Properties pane).
  2. Place COLLATE DATABASE_DEFAULT after each column name used in the query. e.g.
    SELECT TOP 1
        FirstName COLLATE DATABASE_DEFAULT + ' ' +
        LastName COLLATE DATABASE_DEFAULT + ' ' +
        Email COLLATE DATABASE_DEFAULT + ' '       
    FROM [User]
    
  3. Place COLLATE DATABASE_DEFAULT after select clause, but before from clause. e.g.
    SELECT TOP 1
        FirstName + ' ' +
        LastName + ' ' +
        Email + ' '       
      COLLATE DATABASE_DEFAULT
     FROM [User]
    

3 comments: