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 · 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

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

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

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

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