Collation Conflict in SQL Server 2012

Question

The query:
USE vPICList_lite;
SELECT *
FROM ABS
runs correctly in SQL Server 2012

The query:
[dbo].[spVinDecode] @v = N'3VWRF81H2WM128705'
returns:
Msg 468, Level 16, State 9, Procedure spVinDecode, Line 282
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CPI_CI_AS" in the equal to operation

Line 282 of the procedure is: inner join wmi on wmi.id = wm.WmiId

Note that the spVinDecode procedure was packaged with the vPICList_lite database and is meant to process the string variable on the right side of the query.

How do I troubleshoot and resolve this issue?

Answer 1

This error occurs when you mix two columns with different collation in the same expression. Exactly what is going on in your case, I can't tell, since I don't have access to your database.

But a common situation is that stored procedures use temp tables. It works on the original server, because database and server collation are the same. But then the database is restored to a different instance with a different server collation. Because temp tables take their collation from the server, you get this error.

Here is a short example:

   CREATE TABLE MyTable(a varchar(23) NOT NULL)  
   go  
   CREATE PROCEDURE MyProc AS  
   CREATE TABLE #temp (a varchar(23) COLLATE Maori_100_CS_AI NOT NULL)  
   SELECT *   
   FROM   MyTable M  
   JOIN   #temp t ON M.a = t.a  
   go  
   EXEC MyProc  
   go  
   DROP TABLE MyTable  
   DROP PROCEDURE MyProc  

In this example, I explicitly set the collation in the temp table in order to provoke the error. I would guess in your case there is no COLLATE clause at all.

There is a way to avoid these conflicts. When you use the COLLATE clause, you can say database_default:

   CREATE TABLE #temp (a varchar(23) COLLATE database_default NOT NULL)  

Answer 2

Hi @AlanG
How about this:

ALTER TABLE YourTableName  
  ALTER COLUMN OffendingColumn  
    VARCHAR(100) COLLATE DATABASE_DEFAULT NOT NULL  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

From LiHongMSFT-4306: Is this issue solved now? If you have any questions, please feel free to share with us.

The issue is resolved rather than solved. The subject database, including the built-in procedure, was loaded onto a second, similar computer, also running Windows 10 and SQL Server 2012. On this second machine, the sample query provided by the database developers ran as expected and, in particular, did not generate any collation conflict. So, the database is working on the second computer, but the cause of the collation conflict on the original machine remains unsolved. Can anyone provide a possible reason why the same database would produce an error on one computer and not on another when the two systems are nominally the same?

Answer 4

Erland - Thank you very much; this was precisely the problem. Running your serverproperty query on the computer that was working correctly returned SQL_Latin1_General_CP1_CI_AS while the machine that was giving the collation error returned Latin1_General_CI_AS. The problem was that the first machine had the Language for non-Unicode programs set to English (United States) while the second machine was set to English (Canadian). Resetting the latter to English (United States) and reinstalling SQL Server and the database resulted in this machine also functioning correctly. - Alan