临时表排序规则冲突-错误：无法解决Latin1 *和SQL_Latin1 *之间的排序规则冲突 [英] Temp Table collation conflict - Error : Cannot resolve the collation conflict between Latin1* and 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

但是它给出了错误

无法解决 Latin1_General_CI_AI和 SQL_Latin1_General_CP1_CI_AS之间的排序规则冲突等于操作。

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

如何解决此问题？

我的 tempdb 排序规则为 Latin1_General_CI_AI ，而我的实际数据库排序规则为 SQL_Latin1_General_CP1_CI_AS

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

推荐答案

发生这种情况是因为＃tempdb.temp_po.OD1 和 STR_IndentDetail.D1 是不同的（特别是，请注意 #tempdb 是不同的系统数据库，即通常，为什么它会具有默认的整理意见，而不像您自己的数据库和表中可能会提供更具体的意见。

This happens because the collations on #tempdb.temp_po.OD1 and STR_IndentDetail.D1 are different (and specifically, note that #tempdb is a different, system database, which is generally why it will have a default opinion for collation, unlike your own databases and tables where you may have provided more specific opinions).

由于您可以控制临时表的创建，因此解决此问题的最简单方法似乎是在临时表中创建* char列，并使用与 STR_IndentDetail 表：

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:

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

在无法控制表创建的情况下，当您加入列时，另一种方法是添加显式的 COLLATE 语句在发生错误的DML中，可以使用 COLLATE DATABASE_DEFAULT

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

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

或者，更容易

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

SqlFiddle在这里

这篇关于临时表排序规则冲突-错误：无法解决Latin1 *和SQL_Latin1 *之间的排序规则冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！