I've been seeing errors like this recently in my work:

Cannot resolve collation conflict for equal to operation.

I've fixed my problems by designing queries in Design View of views then examining the resulting SQL and things like the following have been appearing.

COLLATE Latin1_General_CI_AS
COLLATE SQL_Latin1_General_CP1_CI_AS

I haven't got a clue what this all means though but it's obviously pretty important.  Can anyone explain what this is all about?


LVL 19
Who is Participating?
Hi daveamour,

A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared

dbo.plant.c_plant  has a different collation to  dbo.cust_master.c_ship_plant,
plant has the collation Latin1_General_CI_AS,  so you must change dbo.cust_master.c_ship_plant collation to match this so you can do the =

CI_AS stands for case insensitive accent sensitive, so THIS is equal to This

Look in Books Online (sqlbol) in your installation directory for SQL Server - it tells you everything you'll ever need to know :)

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. For example, in an ORDER BY clause, a speaker of English would expect the character string 'Chiapas' to come before 'Colima' in ascending order. But a speaker of Spanish in Mexico might expect words beginning with 'Ch' to appear at the end of a list of words beginning with 'C'. Collations dictate these kinds of sorting and comparison rules. The Latin_1 General collation will sort 'Chiapas' before 'Colima' in an ORDER BY ASC clause, while the Traditional_Spanish collation will sort 'Chiapas' after 'Colima'.
daveamourAuthor Commented:
Ok thanks but I still don't understand.

For example I have the following SQL:



From Plant

Inner Join Cust_Master On Plant.c_plant = Cust_Master.c_ship_plant

Which fails and I don't know why.  If I create the same SQL using Visual Designer for Views I get:

SELECT     dbo.plant.c_plant, dbo.cust_master.c_ship_plant
FROM         dbo.plant INNER JOIN
                      dbo.cust_master ON dbo.plant.c_plant = dbo.cust_master.c_ship_plant COLLATE Latin1_General_CI_AS

And this works fine.  These kinds of joins are what I do all the time and I've never come across this before.

Can you explain please?


daveamourAuthor Commented:
Ahh I see.  I checked the collation in the design view for the tables and they were indeed different.  I simply changed these to match and everything was fine.


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.

All Courses

From novice to tech pro — start learning today.