无法解决归类冲突 [英] Cannot Resolve Collation Conflict

问题描述

我已将其中一个数据库（DB1）从SQL Server 2008移至2012，并且在运行存储过程时遇到以下错误

I have moved one of our databases (DB1) from SQL Server 2008 to 2012 and when I run the stored procedures I get the following error

在等于操作中无法解决 SQL_Latin1_General_CP1_CI_AS和 Latin1_General_CI_AS之间的排序规则冲突

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation

我更改了使用

ALTER DATABASE [optimiser] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [optimiser] COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE [optimiser] SET MULTI_USER

但是无论何时运行存储过程，我仍然会收到错误消息。我相信，因为SP正在使用另一个数据库（GE的ihistorian）的联接，并且排序规则不匹配。

But I still get the error whenever the stored procedures run. I believe because the SP is using a join to another database (GE's ihistorian) and it has a collation mismatch. IS there anyway to resolve this.

在旧服务器上，DB1设置为 Latin1_General_CI_AS ，并且工作正常。数据库的新位置的默认值为 SQL_Latin1_General_CP1_CI_AS 。是否值得将新服务器上的排序规则n DB1更改回 Latin1_General_CI_AS ??

On the old server DB1 was set as Latin1_General_CI_AS and this works fine. The new location for the DB has a default of SQL_Latin1_General_CP1_CI_AS. Is it worth changing the collation n DB1 on the new server back to Latin1_General_CI_AS??

推荐答案

关于归类的事情是，尽管数据库具有自己的归类，但是每个表和每个列都可以具有自己的归类。如果未指定，它将采用其父对象的默认值，但是可以不同。

The thing about collations is that although the database has its own collation, every table, and every column can have its own collation. If not specified it takes the default of its parent object, but can be different.

更改数据库的排序规则时，它将是所有新表的新默认值和列，但不会更改数据库中现有对象的排序规则。您必须手动更改每个表和列的排序规则。

When you change collation of the database, it will be the new default for all new tables and columns, but it doesn't change the collation of existing objects inside the database. You have to go and change manually the collation of every table and column.

幸运的是，互联网上有可以完成此任务的脚本。我不会推荐任何东西，因为我还没有尝试过，但是这里有一些链接：

Luckily there are scripts available on the internet that can do the job. I am not going to recommend any as I haven't tried them but here are few links:

http://www.codeproject.com/Articles/302405/The-Easy-way-of-更改所有数据库的排序规则

即时更新数据库中所有字段的整理方式

http://www.sqlservercentral.com/Forums/Topic820675-146-1.aspx

如果需要在两个对象上使用不同的排序规则或无法更改排序规则-您仍然可以在两个对象之间进行 JOIN 使用 COLLATE 命令，然后选择要加入的排序规则。

If you need to have different collation on two objects or can't change collations - you can still JOIN between them using COLLATE command, and choosing the collation you want for join.

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE Latin1_General_CI_AS 

或使用默认数据库排序规则：

or using default database collation:

SELECT * FROM A JOIN B ON A.Text = B.Text COLLATE DATABASE_DEFAULT

这篇关于无法解决归类冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！