[SOLVED] collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI

collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS"

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.

Solution

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.