How to change collation settings in Analyst Database (SQL Server)

Summary

This article describes how to change collation settings in SQL Server 2005/2008. 

International deployments may use collations that are not compatible with the standard U.S. collation (SQL_Latin1_General_CP1_CI_AS). 

Solution

Follow this steps to update an existing database to the desired collation.

STEP 1 - Perform a full backup of the database 

1. Log in to the SQL Server host using an administrator account (if required).
2. Open SQL Server Management Studio (SSMS). 
3. Connect to SQL Server using a login with sysadmin privileges. 
4. Expand Databases, then right-click the target database. 
5. Select Tasks > Back Up... 
6. Set Backup Type to Full. 
7. Select or add the backup destination (the .bak file path). 
8. Click OK to start the backup. 
9. Verify that the backup completed successfully and confirm that the .bak file exists.

STEP 2 - Finding the current collation settings 
Use the steps below to view the current collation settings in SQL Server: 

1. Open SQL Server Management Studio (SSMS) and connect to the server using the sa account. 
2. Open a new query window. 
3. Copy and run the following SQL query:

SELECT CONVERT(char, SERVERPROPERTY('collation'))

List available collation settings 
To view all available collation settings in SQL Server, run the following query:

SELECT * FROM ::fn_helpcollations()

To find collations that match specific regional or language requirements, you can filter the results. For example, to find Korean collations that are case-insensitive and accent-sensitive, use:

SELECT *
FROM ::fn_helpcollations() 
WHERE name LIKE '%Korean%' 
  AND name LIKE '%CI%AS%';

STEP 3 - How to execute AlterCollations.exe 

1. Locate and double-click the AlterCollations.exe file. 
2. In the Server field, enter the SQL Server instance name. 
3. In the Database field, enter the database name (for example, skfuser, if different). 
4. In the Collation field, enter the desired collation setting. 
5. Click Script Only to generate the SQL script for changing the database collation (see Figure 2). 

Figure 1 - Change collation

Figure 2 - Change collation - result box

6. Click in the results pane and copy all content using the following shortcuts: 
   Ctrl + A – Select all 
   Ctrl + C – Copy to clipboard

   

Figure 3 - Select and copy all contents

STEP 4 - How to implement a new collation on the Analyst Database (AlterCollations.exe)

1. Close AlterCollations.exe.
2. Open SQL Server Management Studio (SSMS).
3. Close all existing connections, including any open query windows.
4. Connect using the SA (admin) account.
5. Click New Query to open a new SQL query window.
6. Paste the generated script (see Figure 4).

Figure 4 - Paste all contents

STEP 5 - Modify the database mode from multi-user to single user

1. Right-click the skfuser database (see Figure 5) and select Properties. 
2. In the Database Properties window, select Options from the left panel.

Figure 5 - skfuser DB

3. Under the Options list in the right panel, locate.Restrict Access and set it to SINGLE_USER (see Figure 6). 
4. Click Execute to apply the change. 
5. Do not interrupt the process until it is complete.

Figure 6 - Database properties

Process Explanation

The generated script performs the following steps in sequence:

1. Switches the database to single-user mode.
2. Disables all triggers.
3. Drops existing constraints.
4. Drops indexes.
5. Updates the database to the selected collation.
6. Alters database columns to DATABASE_DEFAULT, so they inherit the new collation.
7. Recreates the indexes.
8. Recreates the constraints.
9. Re-enables the triggers.
10. Sets the database back to multi-user mode.

 

Contacting SKF Technical Support Group

For further assistance please open a support case using the Technical Support group's self-help portal at www.skf.com/cm/tsg. Once your support case is submitted, a technician will contact you to begin working on your issue. For urgent issues we are available at these times by phone:

• Monday through Friday, 5:00 a.m. to 4 p.m. Pacific Time -
  Phone: [phone] within the US or [phone] outside the US.
• Monday through Friday, 8:00 a.m. to 4:00 p.m. Central European Time -
  Phone: [phone].
• Monday through Friday, 7:30 a.m. to 4:30 p.m. India Standard Time -
  Phone: [phone].