thevan thevan - 6 months ago 11
SQL Question

Temp Table collation conflict

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."

How to resolve this problem?

tempdb collation is "Latin1_General_CI_AI" and my database collation is "SQL_Latin1_General_CP1_CI_AS".

Answer

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

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   

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;

OR, easier

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

SqlFiddle here