CODEWITHSUNDEEP
sqlsql-servercollationcollate
M.R
M.R

Reputation: 59

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.

Upvotes: 1

Views: 5280

Answers (3)

Refael
Refael

Reputation: 61

Do the following:

 ...WHERE 
        fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT

Original answer

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131180

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.

Upvotes: 4

PeterHe
PeterHe

Reputation: 2766

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

custName nvarchar(xx) COLLATE DATABASE_DEFAULT NOT NULL

Upvotes: 3

Related Questions