Search code examples
mysqlsql-serverdatabasecollate

COLLATE issue on joining across 2 database on same SQL.Server

I keep getting this error:

Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I am not sure how to fix it.

I am joining across two different databases on the same server by joining a column that has different names in both databases.

SELECT t1.ColumnNameDB1, t2.ColumnNameDB2
FROM DB1.schema.TableName AS t1
INNER JOIN DB2.schema.TableName as t2
ON t1.ColumnNameDB1 =  t2.ColumnNameDB2

I am picking my brain on trying to figure out what I am doing wrong.

Solution

  • You can try explicitly specifying the collation at the join level. 

    SELECT t1.ColumnNameDB1, t2.ColumnNameDB2
FROM DB1.schema.TableName AS t1
INNER JOIN DB2.schema.TableName as t2
ON t1.ColumnNameDB1 =  LTRIM(RTRIM(t2.ColumnNameDB2)) COLLATE SQL_Latin1_General_CP1_CI_AS