Problem with database collation change (SQL Server 2008)

Question

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?

Pironi

possible duplicate of Change collation on clustered index column in SQL 2005 – Defeatism

Answer 1

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.

Cyte

Actually you can change a column's collation without dropping a table. Except for columns that are depended on, e.g. by computed columns, constraints or indexes. – Unrivalled

you can drop the dependencies and recreate them. – Harrietteharrigan

If SQL Server is complaining about a VIEW then you can drop the VIEW and recreate after you have changed the database collation. – Sexlimited

This is the most stupid change they ever made in SQL server. I could change collation on databases up until SQL server 2016 without any issue! – Swordfish

Answer 2

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.

Ferwerda

The database collation cannot be changed if a schema-bound object depends on it. Check constraints and stored procedures (if I'm not mistaken), won't allow the collation change. – Rajasthan

Answer 3

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.

Tripoli

Answer 4

I have this problem because of my computed columns, so I just cancel computed columns and then change the collation and after that change the columns to computed columns. it works to me

Clench

Answer 5

Just create new database with ex database_b.change the collation according to your requirement and export from new source to target.

or

use alt collation tool which will override all the errors.

--Appu

Tanbark

What is the alt collation tool? – Often

Answer 6

Just type N before writing your string.

for example:

insert into table values (N'yourstring')

N = unicode.

Tinea