Account Home | Help | Blog | Contact us | Log Out

Cannot resolve collation conflict for UNION operation.

Posted By: siteadmin on 12/12/2011 14:27:00

If you get this error:

Cannot resolve collation conflict for UNION operation.

You might be pointed to this article:

http://www.sqlservercentral.com/articles/T-SQL/61288/

Where the author describes the fact that there are lots of different collations for different languages e.g.

http://www.serverintellect.com/support/sqlserver/change-database-collation.aspx

Now if you are using UK english, the US character set is the same!  So why is there an issue?

Well you can see on the preceding link that they actually have different collation properties.  How irritating.

English (United Kingdom) = Latin1_General_CI_AS 

English (United States) = SQL_Latin1_General_CP1_CI_AS 

So you need to change the property of the column to match each other.  When you created one of the original tables it might have come from a script that specified the Collation property as the US version.

I searched high and low for how to change the collation property of a table in SQL enterprise manager but could not find it.  Anyway this screen grab will show you where it is.  You've seen it before just never taken any notice.

Enjoy