Beware of Mixing Collations: Part 2- Converting Collations - Page 2

Changing the Server Collation Setting

Changing the server collation setting is not an easy task. There is no ALTER SERVER command to help change the collation setting for the server. In order to change the server collation you will need to rebuild the server.

This means you will need to export all the data in the existing databases. Gather up and/or generate all the scripts to recreate the user databases and objects in those user databases. Drop all the user databases and rebuild the MASTER database. When you rebuild the MASTER database, you will specify your new collation setting.

Note that if you are generating table create scripts from a SQL Server 2000 database then they might contain COLLATE clauses. Prior to executing generated scripts scan your CREATE TABLE and DATABASE scripts to verify that there are no COLLATE clauses. If you do not do this, then potentially your newly migrated columns might end up with their original collation settings.

Once you have rebuilt the MASTER database, then you can re-create all the user databases. Each object in each user database will need to be re-created with the collation setting of the database. If your create table statements do not have any COLLATE clauses, then all columns will be given the default collation setting for the database. Once all of your tables are created you will need to import the data using the files exported from the database that contained the wrong collation settings.

Once again not a simple task, but it can be done. This is why choosing your collation setting up front is very important.

Alternative Method for Migrating Database and All Columns Within to a New Collation

If you need to change all of the collation settings in a database, you might consider migrating your data to a new database that has the desired collation setting. This typically is how I migrate from one collating sequence to another.

I accomplish this by performing the following multi-step process. This process can be done in a number of different ways; I will walk you though the steps I normally use.

Step 1: Create a new database with the desired default collation setting.

Step 2: Generate all of the CREATE TABLE statements from the database that has the wrong collation settings. Only generate the create table statements; do not generate any constraints. If your scripts where generated from a SQL Server 2000 database, remember to review your scripts for COLLATE clauses (see note above).

Step 3: DTS the data from the database that has the wrong collation settings, to your newly created database.

Step 4: Generate all of the constraints from the database with the wrong collation settings. Apply the generated constraints to your newly created and populated database. If you do not have clean data, you may have to apply some of the constraints with the NOCHECK option.

Step 5: Generate the scripts to create all views, stored procedures, roles, user, and/or any other objects you might need from the database with the wrong collation settings and execute the generated script on the new database.

Step 6: Backup your newly created database and restore it over the top of the database with the wrong collation. Of course, only do this if you want to replace your original database with your new database, which has a new collation setting.

This method is not simple, and is still error prone. You need to take care when generating scripts that nothing is missed and the scripts generated build things in the proper order.

I've found that SQL Server tends to generate things in alphabetical order, which is not always an appropriate order. I have resorted to using a third party tool "SQL COMPARE" from Red Gate Software for generating my scripts. I've also used this tool to compare both old and new databases to make sure that no objects were missed when migrating objects from the database with the wrong collation settings to the new database.

Even though this method is time consuming, I've found that it is a reliable method to convert from one collation to another. I'm sure there might be other methods out there for converting collation settings.

Conclusion

Converting from one collation setting to another is not easy. There is no simple tool to perform this transformation. In order to minimize collation issues it is best to require all server databases to have the same collation settings, even if it requires you to migrate your data from one collation setting to another.

» See All Articles by Columnist Gregory A. Larsen