Sql – Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation

sqlsql-server-2008-r2

I'm trying to copy over a schema from one server to another, and I keep getting the following error and I have no clue where to start from to debug this issue…

Error 2 SQL01268: .Net SqlClient Data Provider: Msg 468, Level 16, State 9, Procedure Import_Keys, Line 41 Cannot resolve the collation conflict between "SQL_Latin1_General_CP437_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. SchemaCompare1 552 0

Any help would be appreciated! Using SQL Server 2008 R2

Thanks,

Ryan

Best Answer

Seems like your servers and/or databases have different collations. Does this particular line of code represent a where clause that uses a #temp table? In 2008 R2 and earlier, #temp tables are created using the server collation rather than the calling database's collation. The same kind of issue will happen if you try to equate any two columns where the collation is different due to the server or database the column was created in.

Take a look at line 41, it should point to the columns that are causing the issue. You can trace them back and see that their collation_name value is going to be different in sys.columns (note that it sounds like they might be in different databases).

As you might guess, it is not trivial to change the server or database collation, however you can work around this by changing your where clauses as follows:

WHERE left_column COLLATE SQL_Latin1_General_CP437_CI_AI
   = right_column COLLATE SQL_Latin1_General_CP437_CI_AI

This is not fun of course, and you can limit the collation specifiers to the columns that aren't using the local collation. I dealt for years with a system where one instance was set up as CP437 and the other two were set up as CP1. What a nightmare! Do you know if there's any reason why CP437 was in use?

Related Topic