Temp表排序冲突—错误:无法解决Latin1*和SQL_Latin1*之间的排序冲突

I can't update temp table. This is my query

我不能更新临时表。这是我的查询

CREATE TABLE #temp_po(IndentID INT, OIndentDetailID INT, OD1 VARCHAR(50), OD2 VARCHAR(50), 
        OD3 VARCHAR(50), ORD VARCHAR(50), NIndentDetailID INT, ND1 VARCHAR(50), ND2 VARCHAR(50), 
        ND3 VARCHAR(50), NRD VARCHAR(50), Quantity DECIMAL(15,3))

        INSERT INTO #temp_po(IndentID, OIndentDetailID, OD1, OD2, OD3, ORD)
        SELECT ID.IndentID, ID.IndentDetailID, ID.D1, ID.D2, ID.D3, ID.RandomDimension 
        FROM STR_IndentDetail ID WHERE ID.IndentID = @IndentID

        UPDATE 
            t 
        SET
            t.ND1 = CASE WHEN D.D1 = '' THEN NULL ELSE D.D1 END,
            t.ND2 = CASE WHEN D.D2 = '' THEN NULL ELSE D.D2 END,
            t.ND3 = CASE WHEN D.D3 = '' THEN NULL ELSE D.D3 END,
            t.NRD = CASE WHEN D.RandomDim = '' THEN NULL ELSE D.RandomDim END,
            t.Quantity = D.PurchaseQty
        FROM
            #temp_po t INNER JOIN @detail D ON D.IndentDetailID = t.OIndentDetailID
        WHERE
            t.IndentID = @IndentID

But it gives the error

但它给出了误差

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

无法解决相等操作中的“Latin1_General_CI_AI”和“SQL_Latin1_General_CP1_CI_AS”之间的排序冲突。

How to resolve this problem?

如何解决这个问题?

My tempdb collation is Latin1_General_CI_AI and my actual database collation is SQL_Latin1_General_CP1_CI_AS.

我的tempdb排序是Latin1_General_CI_AI，而我的实际数据库排序是SQL_Latin1_General_CP1_CI_AS。

4 个解决方案

#1

This happens because the collations on #tempdb.temp_po.OD1 and STR_IndentDetail.D1 are different.

这是因为#tempdb.temp_po上的排序。OD1 STR_IndentDetail。D1是不同的。

Since you have control over the creation of the temp table, the easiest way to solve this appears to be to create *char columns in the temp table with the same collation as your STR_IndentDetail table:

由于您可以控制temp表的创建，因此解决这个问题的最简单方法似乎是在temp表中创建与STR_IndentDetail表具有相同排序的*char列:

CREATE TABLE #temp_po(
    IndentID INT, 
    OIndentDetailID INT, 
    OD1 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS, 
    .. Same for the other *char columns

In the situation where you don't have control over the table creation, when you join the columns, another way is to add explicit COLLATE statements in the DML where errors occur, either via COLLATE SQL_Latin1_General_CP1_CI_AS or easier, using COLLATE DATABASE_DEFAULT

在没有对表创建进行控制的情况下，当您加入列时，另一种方法是在发生错误的DML中添加显式的排序规则语句，可以通过排序规则SQL_Latin1_General_CP1_CI_AS或更简单的方法，使用排序规则DATABASE_DEFAULT

SELECT * FROM #temp_po t INNER JOIN STR_IndentDetail s 
   ON t.OD1 = s.D1 COLLATE SQL_Latin1_General_CP1_CI_AS;

OR, easier

或者,更简单

SELECT * FROM #temp_po t INNER JOIN STR_IndentDetail s 
   ON t.OD1 = s.D1 COLLATE DATABASE_DEFAULT;

SqlFiddle here

SqlFiddle这里

#2

Changing the server collation is not a straight forward decision, there may be other databases on the server which may get impacted. Even changing the database collation is not always advisable for an existing populated database. I think using COLLATE DATABASE_DEFAULT when creating temp table is the safest and easiest option as it does not hard code any collation in your sql. For example:

更改服务器排序不是一个直接的决定，服务器上可能会有其他数据库受到影响。即使修改数据库排序规则也不总是可取的。我认为在创建临时表时使用COLLATE DATABASE_DEFAULT是最安全、最简单的选项，因为它不会硬编码sql中的任何排序。例如:

CREATE TABLE #temp_table1
(
    column_1    VARCHAR(2)  COLLATE database_default
)

#3

By default temp table take the collation of server. So instead updating all stored procedure with temp table change only server collation.

默认情况下，临时表接受服务器的排序。因此，使用临时表更新所有存储过程只更改服务器排序。

Check this link for Set or Change the Server Collation

检查此链接以获取设置或更改服务器排序

This worked for me.

这为我工作。

#4

We ran into the same problem right now. Instead of adding the collation to the temp table creation (or to each temp table join), we just changed the temp table creation to a table variable declaration.

我们现在遇到了同样的问题。我们没有将排序规则添加到临时表创建(或每个临时表连接)中，而是将临时表创建更改为表变量声明。

#1