数据库定序变更发生问题(SQL Server 2008)

bkhjykvo 于 2022-12-10 发布在 SQL Server

关注(0)|答案(6)|浏览(212)

When I tried to change the collation of my existing database (including data) from ARABIC_CS_AS to PERSIAN_100_CS_AS the following error occurs:
Alter failed for Database 'XXXX'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The object 'ItemTables' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The object 'CK_FilteredReportColumnFilters' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The object 'CK_FilteredReportColumnFilters_1' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The object 'CK_FilteredReportColumnFilters_2' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
The object 'CK_Reports' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
ALTER DATABASE failed. The default collation of database 'XXXX' cannot be set to Persian_100_CS_AS. (Microsoft SQL Server, Error: 5075)
Trying to correct the errors mentioned by removing those database objects causes another error with other titles in conversion process.
Any idea ? Is there any well-defined solution to solve this problem?

sql-server

来源：https://stackoverflow.com/questions/7371234/problem-with-database-collation-change-sql-server-2008

6条答案

按热度按时间

7jmck4yq1#

Ahh, this is one of the worst problems in SQL Server: you cannot change the collation once an object is created (this is true both for tables and databases...).
You can only save your data (don't use bcp or backup utilities, you need to place them in a csv of similar file types...), drop the database, recreate with the right collation and re-import the data into the new database...
Hope this helps.

赞(0）回复(0）举报 2022-12-10

b1zrtrql2#

I have got the same issue and all objects were functions First takes full backup
script function as create. Delete these function then Run

use master
go
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
go
ALTER DATABASE xxx COLLATE yyyy
go
ALTER DATABASE xxx SET MULTI_USER
go

Recreate functions
I hope this helpful.

赞(0）回复(0）举报 2022-12-10

lhcgjxsq3#

只需使用ex database_b创建新数据库。根据您的要求更改排序规则，并从新的源导出到目标。
或
使用alt校对工具，它将覆盖所有错误。
--阿普

赞(0）回复(0）举报 2022-12-10

camsedfj4#

我遇到这个问题是因为我的计算列，所以我只是取消计算列，然后更改排序规则，然后将列更改为计算列。这对我很有效

赞(0）回复(0）举报 2022-12-10

fd3cxomn5#

I had the same problem. in my case altering the database with this query

use master
go
ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
go
ALTER DATABASE xxx COLLATE yyyy
go
ALTER DATABASE xxx SET MULTI_USER
go

didn't give me any solution. and I ended up with errors!! So I created a new Database with the right collation, and I imported the table from one database to another.

赞(0）回复(0）举报 2022-12-10

u0njafvf6#

只需在编写字符串之前键入N。
例如：

insert into table values (N'yourstring')

N =统一码。

赞(0）回复(0）举报 2022-12-10

我来回答

数据库定序变更发生问题(SQL Server 2008)

6条答案

相关问题

热门标签

最新问答