In This Article, I’ll show you How to solve Resolve the collation conflict in SQL Server.
For Example I have SQL Query:
SELECT * FROM categories INNER JOIN search ON categories.cid = search.cat_id
Above SQL Query Giving me a below error.
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Because Category.cid column has different collate than search.cat_id. So, we cannot use = operation between them.
Here categories.cid hase collate SQL_Latin1_General_CP1_CI_AS
And search.cat_id hase collate Latin1_General_CI_AS
1. Have to make both column with same Collate so we can compare.
1a. here we change collate of search.cat_id to collate SQL_Latin1_General_CP1_CI_AS from Latin1_General_CI_AS
SELECT search.* FROM categories INNER JOIN search ON categories.cid = search.cat_id collate SQL_Latin1_General_CP1_CI_AS
1b. here we change collate of categories.cid to Latin1_General_CI_AS from collate SQL_Latin1_General_CP1_CI_AS
SELECT search.* FROM categories INNER JOIN search ON categories.cid collate Latin1_General_CI_AS = search.cat_id
2. Use COLLATE DATABASE_DEFAULT
SELECT search.* FROM categories INNER JOIN search ON categories.cid COLLATE DATABASE_DEFAULT = search.cat_id COLLATE DATABASE_DEFAULT
How to check Collate:
Go to SQL Server Object Explorer then go to your database table. Expand table & expand Column of table then right click on column which you want to check Collate.
Now Click on Property and you will see following image