1

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.

FutbolFan
  • 13,235
  • 3
  • 23
  • 35

2 Answers2

1

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
FutbolFan
  • 13,235
  • 3
  • 23
  • 35
  • 1
    Can you please exaplain to me what it means to collate and the LTRIM and RTRIM commands –  Jul 28 '15 at 23:18
  • Sure, collation in SQL Sever defines how you sort and compare string values. It defines how to handle different accents, case sensitivity, context, ASCII sorts or UNICODE. For the particular collation above, here is what it means: http://stackoverflow.com/a/5039321/1821329. Also, RTRIM and LTRIM function is used to remove any trailing and leading spaces in string values. For e.g. let's say your field had a value = ` Test ` In this case, LTRIM would remove the leading space whereas the RTRIM would remove the trailing space which in return should give a value = `Test`. Hope that helps! :) – FutbolFan Jul 29 '15 at 03:09
  • Is there another version or set of collate specs I can use? –  Jul 29 '15 at 16:17
  • Sure, it all depends on your needs. Here are different types of collation you can choose from: https://technet.microsoft.com/en-US/library/ms144250(v=sql.105).aspx – FutbolFan Jul 29 '15 at 16:19
0

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
danvasiloiu
  • 751
  • 7
  • 24