How to fix a collation conflict in a SQL Server query?

user_1312649 0
Viewed 155066

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.

collation sql-server database
5 Answers

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_42597610

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.

user_62323690

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_30013340
Related