Sorting rules for conversion Database

What is a sorting rule? The sorting rule specifies the bit mode for each character. It also specifies rules for sorting and comparing characters. Ordering Rules have the following features: ◆ language ◆ case sensitive ◆ Stress Sensitive ◆ distinguish Kana to understand the ordering rules currently used by the server, you can run the sp_helpsort system process in the SQL query analyzer. S

What is a sorting rule? The sorting rule specifies the bit mode for each character. It also specifies rules for sorting and comparing characters. Ordering Rules have the following features: ◆ language ◆ case sensitive ◆ Stress Sensitive ◆ distinguish Kana to understand the ordering rules currently used by the server, you can run the sp_helpsort system process in the SQL query analyzer. S

What is a sorting rule?

The sorting rule specifies the bit mode for each character. It also specifies rules for sorting and comparing characters. Sorting Rules have the following features:

◆ Language

◆ Case sensitive

◆ Stress Differentiation

◆ Distinguish Kana

To understand the sorting rules currently used by the server, you can run the sp_helpsort system process in the SQL query analyzer.

SQL Server 7.0 does not support databases that use multiple sorting rules. Therefore, all databases created in SQL Server 7.0 use the default sorting rules. SQL Server 2000 supports multiple sorting rules. SQL Server 2000 databases can use other sorting rules except the default sorting rules. In addition, SQL Server 2000 supports the use of sorting rules (in addition to database-created sorting rules) columns.

Conditions for using the "use sorting rules" option in DTS

SQL Server 2000 can contain multiple databases or columns. They can use sorting rules other than the default sorting rules. Therefore, the data conversion Service (DTS) introduces a new option named "use sorting rules. The behavior of the "use sorting rules" option is determined by the type of conversion to be executed. If you want to transfer data between two instances of SQL Server 2000 and enable the "use sorting rules" option, the data will be converted from the source code page to the target code page. If the "use sorting rules" option is not enabled and the two instances of SQL Server 2000 share the same code page, data is transmitted directly. If the code page is different, data is converted from the source code page to the target code page. However, an error may occur during data transmission.

Note: If the sorting rule is used for the data itself and the column uses the COLLATE clause, the sorting rule is very important. Use the sorting rules option to determine whether to convert the code page when data is transferred from one sorting rule to another. The "use sorting rules" option does not affect whether the COLLATE attribute defined by the column is set. Therefore, if the source table contains columns with specific sorting rules created using the COLLATE clause, this sorting rule will be retained during data transmission, whether or not the "use sorting rules" option is enabled in the data conversion service wizard.

Conversion Method without changing database sorting rules

The following methods do not change the database sorting rules:

◆ Backup and Restoration: When restoring a database on a server, if the sorting rules used by the server are different from those used by the server for backup, the restored database is not converted to the new sorting rule. Database sorting rules remain unchanged.

◆ Detach and reattach: If you detach a database created using a sorting rule and re-attach the database to another server using other sorting rules, the database sorting rules will not change. The database sorting rules remain unchanged.

◆ Copy Database Wizard: The copy Database Wizard automates the process of separation and re-attachment. The database sorting rules remain unchanged.

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

Convert database sorting rules in SQL Server 7.0

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

To transfer a database from a computer running SQL Server 7.0 to a computer running SQL Server 7.0 with another sorting rule, 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 DTS or bcp utility.

4. Use the SQL Server Enterprise Manager or CREATE DATABASE statement to CREATE a new DATABASE on the target Server.

Note: When the create database statement is used, the DATABASE uses the same sorting rule as the computer running SQL Server 7.0.

5. Run the script created in step 2 using the SQL query analyzer to recreate all objects in the target database.

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

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

Convert the database sorting rules in SQL Server 7.0 to those in SQL Server 2000.

To change the database sorting rules between SQL Server 7.0 and SQL Server 2000, you must, use appropriate sorting rules on the target server to create databases, columns, or both databases and columns. However, when transmitting data from SQL Server 7.0 to SQL Server 2000, you can use DTS to delete and recreate the object. Therefore, when performing this operation, you must enable the "use sorting rules" option in DTS.

Note: Do not use the DTS utility in SQL Server 7.0 to transmit all objects with the computer running SQL Server 2000. If data must be transmitted between SQL Server 7.0 and SQL Server 2000, you must use the DTS utility in SQL Server 2000.

To convert the database sorting rules in SQL Server 7.0 to those 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 other sorting rules instead of the default sorting rules in the target database, make necessary changes to the sorting rules of the corresponding columns in the script.

4. Use appropriate sorting rules to create a new database on the target server.

5. Run the script created in step 2 on the target server using the SQL query analyzer to recreate all objects in the database.

Note: New tables and columns use the same sorting rules as databases unless you specify other sorting rules for these columns.

6. Use DTS or bcp utility to transmit data.

NOTE: If DTS is used, verify the following information:

◆ Make sure that the "use sorting rules" option is enabled when data is transmitted from SQL Server 7.0 to SQL Server 2000.

◆ Because an object has been created using an appropriate sorting rule on the target server, you must disable the "Create target object first" option.

Convert database sorting rules in SQL Server 2000

To convert database sorting rules in SQL Server 2000, follow these steps:

1. Back up the source database.

2. Check whether the COLLATE clause is used for all columns.

3. Use appropriate sorting rules to create a new database on the target server.

4. If no column uses the COLLATE clause, use DTS to transmit data to the target server. To do this, enable the "use sorting rules" option for code page conversion and convert data to new sorting rules 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. Additionally, make sure that the "write scripts only for features compatible with version 7.0" option is enabled to delete the COLLATE clause from the script.