Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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
0
daveamour
Asked:
daveamour
  • 2
1 Solution
 
pdrgCommented:
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'.
0
 
daveamourAuthor Commented:
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
0
 
lengreenCommented:
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


Cheers!
0
 
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.

Thanks!

Dave
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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