SQL Server collation settings can cause great grief if you do not ensure that the target SQL Server collation does not conflict of the database that is being created or restored.
I recently ran into this issue again. Everything was verified to be working fine on our development environment and some machines on development environment of customer. But when we deployed the application on their test environment, we started to get strange Collation mismatch exception in specific (not all) SQL statements.
Cannot resolve the collation conflict between
"XXX" and "YYY"
in the equal to operation.
As it turned out, those queries were using Temporary tables and performing JOIN operations on permanent tables from our database. Since the SQL Server collation was different from Collation of our database backup, comparison operations started to fail in JOIN operations.
Lengthy and permanent solution to get collations in sync.
Simpler and quick solution was to specify collation in specific SQL statements. It is required for nvarchar, char, varchar columns. We verified that it was not required for cases where column type was bit, int, datetime etc. Something like following:
SELECT
t1.col1
FROM
table_1 t1
INNER JOIN #table_2 t2
ON t2.col2 COLLATE DATABASE_DEFAULT = t1.col2
It solved the problem for the moment and gave some time for time to find a permanent solution of the problem.
