Menu
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?
470
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. Msg 5072, Level 16, State 1, Line 2 ALTER DATABASE failed. The default collation of database 'land_gis' cannot be set to Persian_100_CI_AS.
The collation of the user databases is not changed. To change the collation of an existing user database or to create a new database with the appropriate collation, use the ALTER DATABASE command, and then use DTS or the bcp utility to transfer the data to the new database.
The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.
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.
171
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.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
