CODEWITHSUNDEEP
sql-serversql-server-2008-r2collationlinked-server
MAK
MAK

Reputation: 7260

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.

Upvotes: 0

Views: 1281

Answers (2)

WhoamI
WhoamI

Reputation: 416

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

Upvotes: 1

Mohamed Hasan
Mohamed Hasan

Reputation: 237

I think a simple workaround is to restore DB1 on server1 to a new database "DB2"; then you can try to change the collation of DB2 to SQL_Latin1_General_CP1_CI_AS, and finally try to restore DB2 to server2.

I also found another answer here which might be helpful.

Upvotes: 0

Related Questions