[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 830
  • Last Modified:

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_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.
0
sdc248
Asked:
sdc248
  • 2
1 Solution
 
aprestoCommented:
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



Apresto
0
 
aprestoCommented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
select mylocation from mytable
where mylocation collate database_default not in (select location collate database_default  from locationtable)
0
 
sdc248Author Commented:
thanks guys.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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