How to change database collation

Question

Post reply

How to change database collation

binutb

SSCommitted

Points: 1741
More actions
November 21, 2018 at 10:46 am

#376447

Hi,
I have generated one database schema and data from one server (using Generate scripts option is SQL server management studio )
and loaded same in NEW server. Loaded server collation is different.
How to change database collation of NEW server
Tried below script for change collation, but not success due to error (below)
ALTER DATABASE BlueWhale
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE BlueWhale COLLATE SQL_Latin1_General_CP1_CI_AS
Msg 5075, Level 16, State 1, Line 154
The object 'CHK_XML_DATA_REPOPULATE_REPOPULATE' 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.
Msg 5075, Level 16, State 1, Line 154
The object 'UDF_DocReqMatrixCheckingForGeneral' 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.
Msg 5075, Level 16, State 1, Line 154
The object 'CHK_PAYSCALE_MF_ACTIVE' 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.
Msg 5075, Level 16, State 1, Line 154
Please advise best method to change collation
Regards
Binu
November 21, 2018 at 5:31 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bert-701015 SSCommitted Points: 1615 More actions · Answer 1

Martin Bell wrote a good article and associated script for this:

https://sqlblogcasts.com/blogs/martinbell/archive/2011/03/07/Changing-Database-Collations-with-Powershell.aspx

I've used it several times successfully. One note: it does not work with replicated databases.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/
https://sqlquantumleap.com/2018/06/11/changing-the-collation-of-the-instance-and-all-columns-across-all-user-databases-what-could-possibly-go-wrong/

Johnson Welch SSCoach Points: 18773 More actions · Answer 3

This may help you out:
http://www.macaalay.com/2011/12/19/the-easy-way-of-changing-collation-of-all-database-objects-in-sql-server/

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 4

binutb - Wednesday, November 21, 2018 10:46 AM
Hi,
I have generated one database schema and data from one server (using Generate scripts option is SQL server management studio )
and loaded same in NEW server. Loaded server collation is different.
How to change database collation of NEW server
Tried below script for change collation, but not success due to error (below)
ALTER DATABASE BlueWhale
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE BlueWhale COLLATE SQL_Latin1_General_CP1_CI_AS
Msg 5075, Level 16, State 1, Line 154
The object 'CHK_XML_DATA_REPOPULATE_REPOPULATE' 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.
Msg 5075, Level 16, State 1, Line 154
The object 'UDF_DocReqMatrixCheckingForGeneral' 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.
Msg 5075, Level 16, State 1, Line 154
The object 'CHK_PAYSCALE_MF_ACTIVE' 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.
Msg 5075, Level 16, State 1, Line 154
Please advise best method to change collation
Regards
Binu

Are the database object collations different than the database collation? , I think you may have to change the object collation first (based on error) and keep it to default and then try changing the database collation - just a suggestion.