Search code examples
sql-serversql-server-2008-r2collationlinked-server

Collation conflict between different database collation in SQL Server 2008 R2

I have two different servers:

Server 1: It has database DB1 with the collation Latin1_General_CI_AS

Server 2: has DB2 with the collation SQL_Latin1_General_CP1_CI_AS

I come up with the scenario where i need to backup and restore DB1 from server 1 to server 2.

When I run a simple select statement through the linked server, I end up with an error by saying Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Solution

  • Use : COLLATE DATABASE_DEFAULT on both sides of condition for character columns. Example below.

        select * from es
    inner join es e2
    on es.year COLLATE DATABASE_DEFAULT = e2.year COLLATE DATABASE_DEFAULT