Posted on 2005-04-29
Last Modified: 2012-05-05
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?


Question by:daveamour
    LVL 1

    Expert Comment

    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'.
    LVL 19

    Author Comment

    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?


    LVL 10

    Accepted Solution

    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

    LVL 19

    Author Comment

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    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.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now