SQL 校对错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13785814/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Collation Error
提问by Nitin
I am using Microsoft SQL Server Management Studio. I have two databases one is the system database, which has the master database and the other one is my database called CCTNS_CAS_DE_DB. When I am trying to generate the reports through the tool which uses the CCTNS_CAS_DE_DBdatabase.
我正在使用 Microsoft SQL Server Management Studio。我有两个数据库，一个是系统数据库，它有主数据库，另一个是我的数据库，名为CCTNS_CAS_DE_DB. 当我尝试通过使用CCTNS_CAS_DE_DB数据库的工具生成报告时。
I get the following error:
我收到以下错误：
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation
无法解决 equal to 操作中“Latin1_General_CI_AI”和“SQL_Latin1_General_CP1_CI_AS”之间的排序规则冲突
I went through the SQL Server and checked the properties of the master database it shows the collation as Latin1_General_CI_AIbut when I went to the properties of the CCTNS_CAS_DE_DBdatabase it shows the collation as SQL_Latin1_General_CP1_CI_AS.
我浏览了 SQL Server 并检查了 master 数据库的属性，它显示了排序规则，Latin1_General_CI_AI但是当我转到CCTNS_CAS_DE_DB数据库的属性时，它显示排序规则为SQL_Latin1_General_CP1_CI_AS.
I searched for the error online but most of the solution tell how to change the collation of a particular table but I didn't come across any query which will change the collation my database to Latin1_General_CI_AI.
我在线搜索了错误，但大多数解决方案都说明了如何更改特定表的排序规则，但我没有遇到任何将数据库排序规则更改为Latin1_General_CI_AI.
I came across one query which is:-
我遇到了一个查询，它是：-
ALTER DATABASE CCTNS_CAS_DE_DB COLLATE Latin1_General_CI_AI
When I ran this query in my SQL Server it threw the following error:-
当我在 SQL Server 中运行此查询时，它引发了以下错误：-
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'CCTNS_CAS_DE_DB' cannot be set to Latin1_General_CI_AI.
消息 5030，级别 16，状态 2，第 1 行
无法以独占方式锁定数据库以执行操作。
消息 5072，级别 16，状态 1，第 1 行
ALTER DATABASE 失败。数据库 'CCTNS_CAS_DE_DB' 的默认排序规则不能设置为 Latin1_General_CI_AI。
I know this question has already been posted here but that was in a different context I think.
我知道这个问题已经张贴在这里，但我认为那是在不同的背景下。
采纳答案by Adam Maras
Here's the biggest hint to your problem:
这是您问题的最大提示：
Msg 5030, Level 16, State 2, Line 1 The database could not be exclusively locked to perform the operation.
消息 5030，级别 16，状态 2，第 1 行 无法以独占方式锁定数据库以执行操作。
What you need to do is set the database to single-user mode before you run the ALTER DATABASEstatement, and then set it back to multi-user mode when it's completed. This will lock the database and make it available only to the current connection, which will allow you to successfully run the ALTER DATABASE ... COLLATEstatement.
您需要做的是在运行ALTER DATABASE语句之前将数据库设置为单用户模式，然后在完成后将其设置回多用户模式。这将锁定数据库并使其仅对当前连接可用，这将允许您成功运行该ALTER DATABASE ... COLLATE语句。
You can use SQL Server Management Studio or T-SQL commandsto do this.
回答by RAY
Need to set it to SINGLE_USER first.
需要先将其设置为 SINGLE_USER。
ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [database] COLLATE SQL_1xCompat_CP850_CI_AS;
GO
ALTER DATABASE [database] SET MULTI_USER;
GO
回答by Minh Thành
Work perfectly Thank you alot
完美工作 非常感谢
ALTER DATABASE [database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [database] COLLATE SQL_1xCompat_CP850_CI_AS;
GO
ALTER DATABASE [database] SET MULTI_USER;
GO
