collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"

Question

in some procedure that i work on, i write this code:

update a
set a.custName = b.custName
from #x as a inner join pl_Customer as b on a.Company_Code = b.Company_Code and a.cust = b.Cust

ans i got this error:

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

i try so solve it with this:

update a
set a.custName = b.custName
from #x as a inner join pl_Customer as b on a.Company_Code = b.Company_Code and a.cust = b.Cust
collate Latin1_General_CI_AI;

but it is still error.

Seems like your table definitions are inconsistent. Fix that, and the problem is gone. — jarlh, Feb 25 '20 at 13:52
`a.cust collate database_default = b.cust collate database_default` in join condition — Vali Maties, Feb 25 '20 at 14:21
@ValiMaties that would be a bad idea - the server can't use indexes in that case and has to perform a full scan. Besides, it's a temporary table. Either an application or a stored procedure had to create it. It's far easier to just avoid the problem by fixing that script than trying to recover from it afterwards — Panagiotis Kanavos, Feb 25 '20 at 16:31
@PanagiotisKanavos , the example I learned from mssqltips.com... here is the link: https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/ — Vali Maties, Feb 25 '20 at 20:01

score 4 · Accepted Answer · answered Feb 25 '20 at 14:09

4

Temporary tables are created using the server's collation by default. It looks like your server's collation is SQL_Latin1_General_CP1_CI_AS and the database's (actually, the column's) Hebrew_CI_AS or vice versa.

You can overcome this by using collate database_default in the temporary table's column definitions, eg :

create #x (
    ID int PRIMARY KEY,
    Company_Code nvarchar(20) COLLATE database_default,
    Cust nvarchar(20) COLLATE database_default,
    ...
)

This will create the columns using the current database's collation, not the server's.

answered Feb 25 '20 at 14:09

Panagiotis Kanavos

120,703
13
188
236

1

Or he can put `collate database_default` in join condition, like `a.cust collate database_default = b.cust collate database_default` , and it will collate both columns as database default collation, as columns may have different collation than database. – Vali Maties Feb 25 '20 at 14:18
@ValiMaties that would be a bad idea - the server can't use indexes in that case and has to perform a full scan. Besides, it's a *temporary* table. Either an application or a stored procedure had to create it. It's far easier to just avoid the problem by fixing that script than trying to recover from it afterwards – Panagiotis Kanavos Feb 25 '20 at 16:29

score 3 · Answer 2 · answered Feb 25 '20 at 14:08

3

In your temp table definition #x,add COLLATE DATABASE_DEFAULT to the String columns, like

custName nvarchar(xx) COLLATE DATABASE_DEFAULT NOT NULL

answered Feb 25 '20 at 14:08

PeterHe

2,766
1
8
7

collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"

2 Answers2