无法解决整理冲突 [英] 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"在equal to操作中的排序冲突
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 并且这工作正常.DB 的新位置默认为 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-changed-Collation-of-all-Database
http://www.sqlservercentral.com/Forums/Topic820675-146-1.aspx
如果您需要在两个对象上使用不同的排序规则或无法更改排序规则 - 您仍然可以使用 COLLATE 命令在它们之间 JOIN，并选择您想要的排序规则加入.
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屋！
