XML FeedsThis was asked on twitter recently and I gave the answer there. I decided to write a blog post about this because I can use over 140 charaters here instead.
You will see the Cannot resolve collation conflict for equal to operation error when you try to join 2 tables. let's take a look at what we need to do to resolve this. First create and populate these two tables
- use tempdb
- go
- create table Test (SomeColumn varchar(100) not null)
- insert Test values('bla')
- insert Test values('test')
- create table Test2 (SomeColumn varchar(100) collate Traditional_Spanish_CI_AI not null)
- insert Test2 values('Niño')
- insert Test2 values('bla')
Now run the following join between the Test and Test2 tables
- select * from Test t1
- join Test2 t2 on t1.SomeColumn = t2.SomeColumn
Here is the error you will get
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
To quickly fix this you can use collate in your SQL, you have to make sure that the collation is the same on both columns. So you either add the collate with the collation of the column in test2 to the column in the test table or vice-versa. Here is one example
- select * from Test t1
- join Test2 t2 on t1.SomeColumn = t2.SomeColumn collate Traditional_Spanish_CI_AI
You can also apply collate on the other column...first we need to know what the default was in your database. We can use the ANSI information_schema.columns view to get this info, we need to use the collation_name column. Run the following query to grab it
- select column_name,collation_name
- from information_schema.columns
- where table_name = 'test'
In my case it is SQL_Latin1_General_CP1_CI_AS, now the query becomes the following
- select * from Test t1
- join Test2 t2 on t1.SomeColumn collate SQL_Latin1_General_CP1_CI_AS = t2.SomeColumn
Just for fun let's see what the collation is for the column in the Test2 table
- select column_name,collation_name
- from information_schema.columns
- where table_name = 'Test2'
As expected it is Traditional_Spanish_CI_AI
You can use the ::fn_helpcollations()function which returns a list of all the collations supported by SQL Server to get more info about the collation
Run the following query (and yes :: is not a typo)
- select * from ::fn_helpcollations()
- where name ='Traditional_Spanish_CI_AI'
The query returns the following description for Traditional_Spanish_CI_AI'
Traditional-Spanish, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
One thing to be aware of is that when using collate it needs to do a conversion so you might get performance problems...make sure to check those execution plans.
Hopefully this will help some person when dealing with this in the future.
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
The SomeColumn column in the test table was created without specifying the collation, so it will inherit the default collation for that database. Instead of getting the collation for the column from information_schema, you could have used:
Thanks George
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.