How to fix a collation conflict in a SQL Server query

Question

I am working on a view  wherein I am using an inner join on two tables which are from two different servers  We are using linked server  When running the query I am getting this message      Cannot resolve the collation conflict between  SQL Latin1 General CP1 CI AS  and  Arabic CI AS  in the equal to operation    I don t know much about collation  Searching through internet I find solutions to use COLLATE  but the concept of COLLATE is not clear to me  Will it change anything for any of the databases  I am looking for a solution without changing anything for the databases   Any good learning material for these concepts is welcome

User · Accepted Answer

You can resolve the issue by forcing the collation used in a query to be a particular collation, e.g. SQL_Latin1_General_CP1_CI_AS or DATABASE_DEFAULT. For example:

SELECT MyColumn
FROM FirstTable a
INNER JOIN SecondTable b
ON a.MyID COLLATE SQL_Latin1_General_CP1_CI_AS = 
b.YourID COLLATE SQL_Latin1_General_CP1_CI_AS

In the above query, a.MyID and b.YourID would be columns with a text-based data type. Using COLLATE will force the query to ignore the default collation on the database and instead use the provided collation, in this case SQL_Latin1_General_CP1_CI_AS.

Basically what's going on here is that each database has its own collation which "provides sorting rules, case, and accent sensitivity properties for your data" (from http://technet.microsoft.com/en-us/library/ms143726.aspx) and applies to columns with textual data types, e.g. VARCHAR, CHAR, NVARCHAR, etc. When two databases have differing collations, you cannot compare text columns with an operator like equals (=) without addressing the conflict between the two disparate collations.

User · Answer

I had problems with collations as I had most of the tables with Modern Spanish CI AS  but a few  which I had inherited or copied from another Database  had SQL Latin1 General CP1 CI AS collation   In my case  the easiest way to solve the problem has been as follows    I ve created a copy of the tables which were  Latin American  using script table as    The new tables have obviously acquired the  Modern Spanish  collation of my database I ve copied the data of my  Latin American  table into the new one  deleted the old one and renamed the new one    I hope this helps other users

User · Answer

if the database is maintained by you then simply create a new database and import the data from the old one   the collation problem is solved

User · Answer

Adding to the accepted answer  you can used DATABASE DEFAULT as encoding   This allows database to make choice for you and your code becomes more portable   SELECT MyColumn FROM      FirstTable a         INNER JOIN SecondTable b             ON a MyID COLLATE DATABASE DEFAULT   b YourID COLLATE DATABASE DEFAULT

User · Answer

I resolved a similar issue by wrapping the query in another query     Initial query was working find giving individual columns of output  with some of the columns coming from sub queries with Max or Sum function  and other with  distinct  or case substitutions and such   I encountered the collation error after attempting to create a single field of output with     select rtrim field1      rtrim field2            The query would execute as I wrote it  but the error would occur after saving the sql and reloading it   Wound up fixing it with something like     select z field1     z field2         as OUTPUT REC from  select rtrim field1   rtrim field2         z   Some fields are  max  of a subquery  with a case substitution if null and others are date fields  and some are left joins  might be NULL    in other words  mixed field types   I believe this is the cause of the issue being caused by OS collation and Database collation being slightly different  but by converting all to trimmed strings before the final select  it sorts it out  all in the SQL

[sql-server] How to fix a collation conflict in a SQL Server query?

Examples related to sql-server

Examples related to database

Examples related to collation