Sorting rules for SQL Server transformation databases

What are collation rules?

The collation specifies the bit pattern that represents each character. It also specifies the rules for sorting and comparing characters. Collations have the following characteristics:

Language

Case sensitive

Accent Sensitive

Distinguish Kana

To understand the collation that the server is currently using, you can run the Sp_helpsort system procedure in SQL Query Analyzer.

SQL Server 7.0 does not support databases that use multiple collations. Therefore, all databases created in SQL Server 7.0 use the default collation. SQL Server 2000 supports multiple collation rules. SQL Server 2000 databases can use a collation other than the default collation. In addition, SQL Server 2000 supports columns that use collations other than the collation created by the database.

Criteria for using the Use Collation option in DTS

SQL Server 2000 can contain multiple databases or columns, and they can use collations other than the default collation. Therefore, a new option named "Use Collations" is introduced in Data Transformation Services (DTS). The behavior of the Use Collation option is determined by the type of transformation to be performed. If you want to transfer data between two instances of SQL Server 2000 and the Use Collation option is enabled, the data is converted from the source code page to the target code page. If the Use Collation option is not enabled and the code pages in the two instances of SQL Server 2000 are the same, the data is transferred directly. If the code page is different, the data is converted from the source code page to the target code page. However, there may be an error in the conversion when transferring data.

Note: Collation is important if the collation is used for the data itself and the column uses a COLLATE clause. The Use collation option determines whether code pages are converted when data is transferred from one collation to another. The Use collation option does not affect whether the COLLATE property of the column definition is set. Therefore, if the source table contains a column with a specific collation created using the COLLATE clause, the collation will be preserved when the data is transferred, regardless of whether the Use Collation option is enabled in the Data Transformation Services Wizard.

Conversion methods without changing the collation of the database

The following methods do not change the collation of the database:

Backup and restore: When a database is restored on a server, the restored database is not converted to use the new collation if the server uses a collation that is different from the collation used by the server for the backup. The database collation remains intact.

Detach and Reattach: If you detach a database created with a collation and reattach the database to another server that uses a different collation, the collation of the database does not change. The collation of the database remains intact.

Copy Database Wizard: The Copy Database Wizard essentially automates the detach and reattach process. The collation of the database remains intact.

Note: The Copy Database Wizard is available only in SQL Server 2000.

To transform the collation of a database in SQL Server 7.0

To change the collation of a database between two computers running SQL Server 7.0, you must create a user database and all objects on the target server, and then use the DTS or bcp utility to transfer the data.

To transfer a database from a computer running SQL Server 7.0 to a computer running SQL Server 7.0 that has another collation, follow these steps:

1. Back up the source database.

2. Use SQL Server Enterprise Manager to create scripts for all objects in the source database.

3. To export data from all tables in the database, use the DTS or bcp utility.

4. Create a new database on the target server using SQL Server Enterprise Manager or the CREATE DATABASE statement.

Note: When you use the CREATE database statement, the database uses the same collation as the computer that is running SQL Server 7.0.

5. Use SQL Query Analyzer to run the script created in step 2 to re-create all objects in the target database.

Note: This table and column will use the same collation as the computer running SQL Server 7.0.

6. Use the DTS or bcp utility to import data into the destination table.

To convert a collation of a database in SQL Server 7.0 to a collation in SQL Server 2000

To change the collation of a database between SQL Server 7.0 and SQL Server 2000, you must create a database, a column, or create both a database and a column on the target server before you transfer data. However, when transferring data from SQL Server 7.0 to SQL Server 2000, you can use DTS to delete and re-create objects. Therefore, when you do this, the Use Collation option must be enabled in DTS.

Note: Do not use DTS utilities in SQL Server 7.0 to transfer all objects to and from computers running SQL Server 2000. If you must transfer data between SQL Server 7.0 and SQL Server 2000, you must use the DTS utility in SQL Server 2000.

To convert the collation of a database in SQL Server 7.0 to a collation in SQL Server 2000, follow these steps:

1. Back up the source database.

2. Use SQL Server Enterprise Manager to create scripts for all objects in the source database.

3. If the column must use a different collation than the default collation on the target database, make the necessary changes to the collation of the corresponding column in the script.

4. Create a new database on the target server using the appropriate collation rules.

5. Use SQL Query Analyzer to run the script created in step 2 on the target server to re-create all objects in the database.

Note: New tables and columns use the same collation as the database, unless you specify additional collations for those columns.

6. Use DTS or bcp utility to transfer data.

Note: If you use DTS, verify the following information:

Make sure the Use Collation option is enabled when transferring data from SQL Server 7.0 to SQL Server 2000.

The Create target object first option is disabled because an object has been created with the appropriate collation on the target server.

To transform the collation of a database in SQL Server 2000

To convert the collation of a database in SQL Server 2000, follow these steps:

1. Back up the source database.

2. Note Whether all columns use the COLLATE clause.

3. Create a new database on the target server using the appropriate collation rules.

4. If no columns use the COLLATE clause, use DTS to transfer data to the target server. To do this, enable the Use Collation option for code page conversions and convert the data to a new collation on the target database. If all columns use the COLLATE clause, follow these steps:

A. Generate scripts for all objects (excluding indexes, triggers, primary keys, foreign keys, default settings, and constraints). Also, make sure that the script only for versions compatible with 7.0 option is enabled to remove the COLLATE clause from the script.

Note: You can change the collation when you use the script only for versions compatible with version 7.0 option. However, any new SQL Server 2000 options (including user-defined functions, extended properties, INSTEAD of triggers, and indexes on views) will not be taken into account when generating scripts.

B. Run the script created in step A on the target database to create the object using the collation of the target database.

C. Use DTS to transfer only data from the source database.

D. When data is transmitted successfully, scripts are generated for all constraints, foreign keys, primary keys, and indexes in the source database.

E. Run the script created in step d on the target database.

To change the collation of the master database

If you want to change the collation of the master database, you must rebuild the master database. When you rebuild the master database, you are essentially creating a new master database. Therefore, before rebuilding the master database, consider the following considerations:

Make sure that you create a valid backup of the current master database. The msdb database and the model database are also rebuilt when the master database is rebuilt. Therefore, you must back up the msdb database and the model database before rebuilding the master database. The msdb database is a system database for storing SQL Server jobs, alerts, operators, and DTS packages. The model database is the template database that is used when you create a new database.

Because rebuilding the master database creates a new master database, you must re-enter the existing login information after rebuilding the master database. Therefore, you must also export the login information before rebuilding the master database, and then import the login information after rebuilding the master database.

Because the msdb database is rebuilt when the master database is rebuilt, scripts must be generated for all jobs, alerts, and operators before rebuilding the master database. In addition, you must ensure that all DTS packages are moved.

Because the model database is rebuilt while rebuilding the master database, you must write down or export any previous changes to the model database or generate scripts for it before rebuilding the master database. After you rebuild the model database, reapply all changes that have been recorded.

To change the collation of the master database in SQL Server 2000

Before you change the collation of the master database, follow these steps:

1. After rebuilding the master database, all databases created prior to rebuilding the database are not automatically restored. Therefore, all databases must be restored from backup, or if the files are still on disk, you can use the sp_attach_db system stored procedure to reattach the database. Make sure that all necessary information is reattached to the existing database before rebuilding the master database.

2. When rebuilding the master database, only change the collation of the master database, the model database, and the msdb database. The collation of the user database is not changed. To change the collation of an existing user database or to create a new database using the appropriate collation, use the ALTER Database command, and then use the DTS or bcp utility to transfer data to the new database.

Note: If you use the ALTER DATABASE command in SQL Server 2000 to change the collation of the databases, the collation of the columns in the table will not automatically change. To change the collation of a column, use the ALTER TABLE command and the ALTER COLUMN command. If you use DTS, you can create tables and columns using the appropriate collation before transferring data or using the Use Collation option. If you are using DTS and tables that already contain the appropriate collation, make sure that the Delete existing objects first option is disabled before running the package.

To change the collation of the master database in SQL Server 7.0

Before you change the collation of the master database, follow these steps:

1.SQL Server 7.0 does not support databases using collations other than the default collation. Therefore, before rebuilding the master database, export all the data from the user database.

2. Generate scripts for all objects in the database.

3. Recreate the master database using the appropriate collation rules.

4. Create a new database. Automatically creates a new database using the new default collation.

5. Run the previously saved script to re-create the object, and then import the previously exported data.