You have just completed setting up a new development database server for a new project. After that, everyone in the team now starts working on implementing their design and starts to create their objects. When the time came for them to start adding test data, Chinese characters cannot be stored to the database. The system the development team are working on needs to store data in Chinese, Katakana and English characters at the same time.
The collation setting in your current setup must not be compatible with using Chinese or Katakana characters. Therefore you need to change the server level collation settings in your SQL Server. When doing an installation of a new instance of SQL Server you need to understand the code page, sorting, case sensitivity and accent sensitivity requirements of that application that is going to use the database.
SQL Server collation specifies the set of rules that will determine what types of characters will be stored in the database, for sorting and when you need to compare characters or strings. This is important because this is what specifies what code page to be used. Setting the wrong collation in your SQL Server may force you to re-install your SQL Server instance as this is a complex procedure.
There is a way to change your server collation settings without the need to re-install your SQL Server. This article is a personal recount of how I was able to update our server collation settings when the above scenario happened to our development team by following the steps below.
- Disconnect all current users. Let the development team that you need to at least 15-20 minutes to fix the issue. This was how long I had to fix our server when it happened.
- Script all your logins, scheduled jobs, and other server objects you may have. This will serve as a backup that you will need to run later. In my case, I had some legacy DTS packages so I had export them one by one and saved it somewhere.
- Detach all your user database
- Stop the SQL Server instance
- Verify your SQL Server authentication mode. If you are using mixed mode authentication, you will need the ‘sa’ account password on hand.
- Open the command window.
- Issue the following command, see my screenshot below, please take note also of the directory path where you need to run the command.
If you are using mixed mode authentication in your security settings, you need to enter the ‘sa’ account password. The /instancename is your SQL Server instance name. The /SQLCollation parameter should contain the correct collation you need to set.
After the above command is successful, you will need to put all your system objects back because all the SQL Server system database are newly setup. So your earlier script for all your logins, scheduled jobs and other objects will come in handy now because you need to put them all back. Then attach your user database and verify everything is in order. If you have DTS packages which was the case for me, I needed to import them one by one again.
After that you can check your server collation setting and it should be the correct one now. And everyone can get back to work and you can get another cup of coffee.