Problem Statement:

Today, We faced an issue with one of the projects we engaged with.Project team was experiencing issues having different collation for TEMPDB and USERDB.The error message is given below:

“Cannot resolve the collation conflict between “SQL_Latin1_Genral_CP1_CI_AS” and “Latin1_Genral_CI_AI” in the equal to operation”.

Troubleshooting:

This was a clear indication that the issue is with the collation mismatch. When we further analysed the procedure, we could identify there is a temp table used and being used to match the records with USERDB table.

To understand the issue with collation in detail, we checked the collation of TEMPDB database and USERDB and found the collation is set as “SQL_Latin1_Genral_CP1_CI_AS” and “Latin1_Genral_CI_AI”.

Use the below script to know the collation for databases:

Select name,COLLATION_NAME from sys.databases

As long as we are not sure of the difference in Collation between TEMPDB and USERDBs, as a quick fix, we recommanded to change the SQL Query as below while creating the temp tables, this would by default take the user database’s collation during the temp table creation:

CREATE TABLE #Table
(
VoucherID NVARCHAR(10) COLLATE database_default,
VocherName NVARCHAR(100) COLLATE database_default,
FileTypeID INT
)

Call To Action:

1. Please make sure your USERDBs and systemDBs are in the same collation unless there is a requirement for explicit change.
2. When you want to change the Collation for a USERDB, Please check the Collation of TEMPDB as well.

See Also:

https://msdn.microsoft.com/en-us/library/bb402915.aspx