TEMPDB – Collation Issues with User databases in SQL Server

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s