Search code examples
sql-serverdatabasereplicationcollation

Change Collation in SQL Server

I tried to change collation database like this :

ALTER DATABASE [MYDB] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
GO 

ALTER DATABASE [MYDB]
COLLATE SQL_Latin1_General_CP1_CI_AS
GO 

ALTER DATABASE [MYDB] 
SET MULTI_USER; 
GO

But I got this error:

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Warning: Changing default collation for database 'MYDB', which is used in replication. All replication databases should have the same default collation.

Msg 5075, Level 16, State 1, Line 10
The object 'CK_PriceL_EndDate' 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 10
The object 'CK_PriceL_Price' 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 10
The object 'chk_trades_year' 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 10
The object 'chk_trades_month' 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 10
The object 'SplitObjects' 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 5072, Level 16, State 1, Line 10
ALTER DATABASE failed. The default collation of database 'MYDB' cannot be set to SQL_Latin1_General_CP1_CI_AS.

I read how to set single user to multi user in doc.microsoft

I read how to set or change the database collation in doc.microsoft

Note: this server is main publisher of replication, I use SQL Server 2019

Solution

  • Finally I can find answer for change SQL SERVER Collation after installation .

    We can run by this bat file for change collation. (Save below code in text and change file type to .bat) and then run as administrator.

    this file code is very simple :

    cd C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2008\MSSQL\Binn
sqlservr -m -T4022 -T3659 -s"MSSQLServer2008" -q"SQL_Latin1_General_CP1_CI_AS"

    This clear anyone need special path and instance name.

    Note: This is not a recommended idea for change collation on production servers.