Solved

collation conflict

Posted on 2006-07-24
4
811 Views
Last Modified: 2010-01-29
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
Comment
Question by:sdc248
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
apresto earned 125 total points
ID: 17169139
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
 
LVL 23

Expert Comment

by:apresto
ID: 17169153
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17169155
select mylocation from mytable
where mylocation collate database_default not in (select location collate database_default  from locationtable)
0
 

Author Comment

by:sdc248
ID: 17170275
thanks guys.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question