Error Cannot resolve the collation conflict between SQL Latin1 General CP1 CI AS and Arabic CI AS in the equal to operation

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.

Help me out please!!

Answer 1

Hello @kartik,

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.

Hope this helps you!!

Thank You!!

To know more about SQL, It's recommended to join SQL Online Course today.