SQL Server - Collation Conflict

CODING

Collation conflicts in SQL Server

If you try to compare values or insert new records into a table from another table from another database with different collation you could get some of the following errors:

"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1250_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation."

"Cannot resolve collation conflict for equal to operation."

- Advertisement -

For example, let's say that in the query below database_one has collation SQL_Latin1_General_CP1_CI_AS and database_two is with collation SQL_Latin1_General_CP1250_CI_AS:

select * from database_one.dbo.table_one -- SQL_Latin1_General_CP1_CI_AS
where column_one in (
  select column_two 
  from database_two.dbo.table_two -- SQL_Latin1_General_CP1250_CI_AS
)

This is an example where you could experience the error messages related to collation conflict. 

How to fix collation conflict

The solution is to change collation of the values from the second database in the query to be compatible with the original one. Modified query should look like this (note COLLATE SQL_Latin1_General_CP1_CI_AS in the second select statement):

select * from database_one.dbo.table_one
where column_one in (
  select column_two COLLATE SQL_Latin1_General_CP1_CI_AS 
  from database_two.dbo.table_two
)

- Advertisement -
- Advertisement -