When one of our projects deployed to production application was throwing a strange error and while debugging it is found that one of the stored procedure getting failed because of collation conflict issue.
The reason behind this error is the stored procedure used a temp table to join a table on string column. Temp tables are created on TempDB, so if you don’t specify collation for a string columns they inherit the collation of TempDB. If the collation of user database doesn’t match with the collation of TempDB and you are comparing and/or joining on string columns, it may cause this collation conflict issue.
There are many ways to resolve this issue. However the best way as per me is to change to specify the collation of the string column of temp table to database default, so that it will inherit the collation of .user database.
CREATE TABLE #tmpTable(IDCol INT, StringCol VARCHAR(100) COLLATE DATABASE_DEFAULT)
Smart coding 🙂