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.

Mozellemozes

can you show the SQL code you're working on ? – Psia

Does this answer your question? SQL change field Collation in a select – Gerena

Answer 1

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.

Sathrum

Thank you Roryap. I am updating my code with COLLATE. I will let you know if have any issue. So it means nothing will be changed on database level and this COLLATE will be done only within the SQL script. – Mozellemozes

interestingly enough, up to now i thought i have to change the collation on database level to make em match, didnt know i could match em in my query using COLLATE ! thank you this saved me alot of trouble. – Teddy

Beautifully explained, Thanks @Sathrum – Hortense

You don't need to provide a hint for both sides of the comparison. I.e. this will suffice: ON a.MyID = b.YourID COLLATE SQL_Latin1_General_CP1_CI_AS – Biel

Answer 2

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

Tatter

Answer 3

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.

Bankston

Answer 4

This worked for me: (adding the COLLATE statement into the CONCAT statement)

SELECT CONCAT(TRIM(A.REFERRED_TO),' - ',TRIM(B.[NAME] COLLATE SQL_Latin1_General_CP1_CS_AS)), COUNT(A.REFERRED_TO) FROM CRIREFS AS A JOIN REFSERVS AS B ON B.CODE = A.REFERRED_TO AND B.ORGUUID = A.ORGUUID

Register

Please format your answer properly to ensure your contribution is useful to others. – Rootstock

Would this not even work without the COLLATE inside the brackets of the CONCAT? I did not test it, I just ask those who do to drop a remark. – Gerena

Answer 5

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:

1. I've created a copy of the tables which were 'Latin American, using script table as...
2. The new tables have obviously acquired the 'Modern Spanish' collation of my database
3. 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.

Mcnutt