How to get rid of collation conflict in a SQL Server query?
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.
SQL Server rounding Error, Giving different values Unit testing - database data How do I get date/time information from a TIMESTAMP column? How to ORDER BY in SQL PIVOT Return empty or null fields as <element /> from SQL Server 2008 R2 with FOR XML Storing data in non alphabetical order
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.
2 Solutions collect form web for “How to get rid of collation conflict in a SQL Server query?”
You can resolve the issue by forcing the collation used in a query to be a particular collation, e.g.
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
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.
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.
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…
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.