sdc248
asked on
collation conflict
Hi:
I map locations from one table to another by doing something like:
update mytable
set mylocation=l.location
from mytable as p, locationtable as l
where p.storedid=l.storeid
However, when I test by:
select mylocation from mytable
where mylocation not in (select location from locationtable)
I got the following error message:
Cannot resolve the collation conflict between "Latin1_General_CS_AS_KS_W S" and "SQL_Latin1_General_CP1_CI _AS" in the equal to operation.
It sounds like two table/columns have different datatype. But not only they are both varchar, I actually map one column from the other. What's the possible problem here? How do I resolve it? Thanks.
I map locations from one table to another by doing something like:
update mytable
set mylocation=l.location
from mytable as p, locationtable as l
where p.storedid=l.storeid
However, when I test by:
select mylocation from mytable
where mylocation not in (select location from locationtable)
I got the following error message:
Cannot resolve the collation conflict between "Latin1_General_CS_AS_KS_W
It sounds like two table/columns have different datatype. But not only they are both varchar, I actually map one column from the other. What's the possible problem here? How do I resolve it? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select mylocation from mytable
where mylocation collate database_default not in (select location collate database_default from locationtable)
where mylocation collate database_default not in (select location collate database_default from locationtable)
ASKER
thanks guys.
ALTER DATABASE yourDatabase COLLATE Latin1_General_CS_AS_KS_WS