• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 838
  • Last Modified:

collation conflict


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_WS" 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.
  • 2
1 Solution
Hi sdc248,

They have a different collation, go into the table design view, at the bottom of the options at the bottom you will see collation, what the error is saying is that your query is trying to select fields of different collations, they need to match, so you might want to change one to match the other

else you could alter the entire database collation witht he following:

ALTER DATABASE yourDatabase COLLATE Latin1_General_CS_AS_KS_WS  -- Your Collation to change to here
Aneesh RetnakaranDatabase AdministratorCommented:
select mylocation from mytable
where mylocation collate database_default not in (select location collate database_default  from locationtable)
sdc248Author Commented:
thanks guys.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now