Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS”

Collation is nothing but the set of rules that ensure proper use of characters for Language or Alphabet.
If the fields you are comparing use different collation, you will get this error.

select * from table1 a inner join table2 b on a.id = b.id

but if your table1 and table2 is having different collate then you need to specify

collate externally means

select * from table1 a inner join table2 b on a.id = b.id COLLATE Latin1_General_CI_AI

Generally this happen when you restore your database from other system and in some

procedures you may have mentioned create temptable. so your runtime temptable and your

database table have different collate. and you are making join from that.

select * from mydbtable
INNER JOIN #temptable ON dbo.mydbtable = #temptable.id

For that you can solve it by

CREATE TABLE #temptable(
id NVARCHAR(50) Collate SQL_Latin1_General_CP1_CI_AS,
value NVARCHAR(MAX) Collate SQL_Latin1_General_CP1_CI_AS)

or

CREATE TABLE #temptable(
id NVARCHAR(50) COLLATE Latin1_General_CI_AI,
value NVARCHAR(MAX) COLLATE Latin1_General_CI_AI)