asked on

Collate

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?

Thanks

Dave

pdrg

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

daveamour

ASKER

Ok thanks but I still don't understand.

For example I have the following SQL:

Select

Plant.c_plant,
Cust_Master.c_ship_plant

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?

Thanks

Dave

ASKER CERTIFIED SOLUTION

lengreen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

daveamour

ASKER

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.

Thanks!

Dave