CODEWITHSUNDEEP
mysqlsql-serverdatabasecollate
user4309564
user4309564

Reputation:

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.

Upvotes: 1

Views: 3994

Answers (2)

danvasiloiu
danvasiloiu

Reputation: 751

Use this: 

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

Upvotes: 0

FutbolFan
FutbolFan

Reputation: 13723

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

Upvotes: 1

Related Questions