Temp Table collation conflict - Error : Cannot resolve the collation conflict between Latin1* and SQL_Latin1*

Question

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?

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

Answer 1

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

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

Answer 2

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.

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

Answer 4

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:

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