In This Article, I’ll show you How to solve Resolve the collation conflict in SQL Server.
For Example I have SQL Query:
Query:

SELECT     *
FROM         categories INNER JOIN
                      search ON categories.cid = search.cat_id

Above SQL Query Giving me a below error.
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.

Why:
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

Solution:

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

OR
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