SQL Server: collation conflict between & quot; Chinese_PRC_CI_AS & quot; and & quot; SQL _Latin1_General_CP1_CI_AS & quot;

Issue: Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL _Latin1_General_CP1_CI_AS" in the equal to operation.

Issue

One day in a multinational company, DBA set up a database Server for testing in Beijing. This DB Server is called CH01.
One day, the developer ran the business system on CH01, and all stored procedures with temporary tables encountered an error. The error code is as follows:

Msg 468, Level 16, State 9, Procedure CIU_PCUDefaultStreams_SP_Temp_Panel, Line 250Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Analysis

Collation settings is:SQL _Latin1_General_CP1_CI_AS.

However, the service period sorting rule of the DB Server CH01 is setChinese_PRC_CI_AS

Therefore, the collation settings of the temporary table database tempdb is the same as that of the server.Chinese_PRC_CI_AS.

When temporary tables are created in the stored procedures in the Business Database, these temporary tables are created in tempdb. So the sorting rule settings of temporary tables are alsoChinese_PRC_CI_AS.

Therefore, when the physical tables in the Business Database and temporary tables in the tempdb perform joint queries, an error occurs.

How to resolve

There are three solutions:

1. When creating a table (including a temporary table), you can specify collation settings ).

2. You can specify the sorting rule settings in the query statement.

3. You can specify the sorting rule settings when creating a database, or you can change the sorting rule settings after creating a database.