Share Coding

Tutorials, Problems, Stuffs …

Change Server Collation in MSSQL server 2017

** please create a Full Database Backup if necessary

Target: change server collation from “SQL_Latin1_General_CP1_CI_AS” to “Chinese_Hong_Kong_Stroke_90_CI_AS”

1. Check current collation

SELECT SERVERPROPERTY(N’Collation’)

1

2. Stop SQL server services

3.png

3. open a command prompt with administrative privileges, go to the BINN directory of Microsoft SQL Server and run sqlservr.exe as follow:

C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\Binn
sqlservr -m -T4022 -T3659 -s”SQLEXPRESS” -q”Chinese_Hong_Kong_Stroke_90_CI_AS”

-m starts an instance of SQL Server in single-user mode

-t starts an instance of SQL Server with a specified trace flag

-s”<instance name>”

-q”<New Collation Name>”

ref: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/database-engine-service-startup-options?view=sql-server-2017

2.png

4. Restart SQL server

5

5. Check for the change in Server Collation

6

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: