更改数据库SQL Server 2008的排序规则 [英] change collation of DB SQL Server 2008

问题描述

我已经在SQL Server 2008 Express（10.0.1600.22）中创建了一些新数据库，并且还从SQL Server 2005 Express（9.00.1399.06）中恢复了一个数据库。

I have created some new databases in SQL Server 2008 Express (10.0.1600.22) and I have also restored one from SQL Server 2005 Express (9.00.1399.06).

这些规则的排序规则是不同的，因此我无法在它们之间执行查询。因此，我尝试将还原的数据库排序规则

The collations for these are different and I cannot execute queries across them as a result. So I am trying to change the restored database collation

from: SQL_Latin1_Genral_CP1_CI_AS
to:   Latin1_General_CI_AS

但是，新排序规则未出现在选项列表中。不确定是否可行。

However the new collation does not appear in the list of options. Not sure if this is possible.

BTW-不能解决的变通方法：

BTW - workarounds that are not options:

• 我无法编写来自sql server 2005 Express的数据脚本（看来-我可能丢失了一些东西）

• I cannot script the data from sql server 2005 express (it seems - I may be missing something)

我无法编写2K8数据库的脚本服务器，因为这样做时出现内存不足异常：-（

I cannot script the DB on 2K8 server as I get an out of memory exception when doing that :-(

推荐答案

如果该排序规则对于您实例上的其他数据库可见，但从2005实例恢复的该特定数据库却不可见，我想知道显示的列表是否取决于数据库兼容性模型。

If the collation is visible for other databases on your instance but not this specific one restored from a 2005 instance I wonder if the list displayed is dependent upon the databases compatibility model.

也许尝试将已还原数据库的兼容模式更改为100，以查看其是否出现在选项列表中。

Maybe try changing the compatibility mode of the restored database to 100 to see if it appears in the list of options.

请注意，更改数据库排序规则不会影响现有列。这是一个可能有助于。

Note changing the database collation will not affect existing columns. Here's a script that may help with that.

这篇关于更改数据库SQL Server 2008的排序规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！