SQL 临时表整理冲突 - 错误：无法解决 Latin1* 和 SQL_Latin1* 之间的整理冲突
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21332105/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Temp Table collation conflict - Error : Cannot resolve the collation conflict between Latin1* and SQL_Latin1*
提问by thevan
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.
无法解决 equal to 操作中“Latin1_General_CI_AI”和“SQL_Latin1_General_CP1_CI_AS”之间的排序规则冲突。
How to resolve this problem?
如何解决这个问题？
My tempdbcollation is Latin1_General_CI_AIand my actual database collation is SQL_Latin1_General_CP1_CI_AS.
我的tempdb排序规则是Latin1_General_CI_AI，我的实际数据库排序规则是SQL_Latin1_General_CP1_CI_AS.
回答by StuartLC
This happens because the collations on #tempdb.temp_po.OD1and STR_IndentDetail.D1are different (and specifically, note that #tempdbis 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).
发生这种情况是因为#tempdb.temp_po.OD1和STR_IndentDetail.D1上的排序规则不同（特别是，请注意这#tempdb是一个不同的系统数据库，这通常是为什么它会有一个默认的排序规则意见，不像您自己的数据库和表，您可能已经提供了更具体的意见） .
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_IndentDetailtable:
由于您可以控制临时表的创建，因此解决此问题的最简单方法似乎是在临时表中使用与您的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 COLLATEstatements in the DML where errors occur, either via COLLATE SQL_Latin1_General_CP1_CI_ASor easier, using COLLATE DATABASE_DEFAULT
在您无法控制表创建的情况下，当您加入列时，另一种方法是COLLATE在发生错误的 DML 中添加显式语句，通过COLLATE SQL_Latin1_General_CP1_CI_AS或更容易，使用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;
OR, easier
或者，更容易
SELECT * FROM #temp_po t INNER JOIN STR_IndentDetail s
ON t.OD1 = s.D1 COLLATE DATABASE_DEFAULT;
回答by Maneesh Singh
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_DEFAULTwhen 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
)
回答by Munavvar
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.
这对我有用。
回答by IngoB
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.
我们现在遇到了同样的问题。我们没有将排序规则添加到临时表创建（或每个临时表连接）中，而是将临时表创建更改为表变量声明。
