3

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

2 Answers2

4

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?

Aaron Bertrand
  • 340
  • 3
  • 10
2

Adding some bits to the information @Aaron gave:

The collation is stored as a default value in the database server, one is stored as a default within each database, and one is stored with each character-type table column. If you try to change collation on a whole database, it is not sufficient to change the database default or so, you must change the collation for every single character-type column in every single table. However, this may be even better than to live with different collations for a long time.

By the way, CP437 is the old default DOS 8-bit character set. CP1 specifies code page 1252 (obvious, is it :), the Windows default 8-bit character set. CI means case-insensitive, AI means accent-insensitive, AS is accent-sensitive. ( http://msdn.microsoft.com/en-us/library/ms180175.aspx ) So you must make some decisions: How do you want your data to be compared? What is the actual character set?

Moritz Both
  • 657
  • 9
  • 17