PRB: DBCS data problems occur after an upgrade to SQL Server 7.0 (289497)

SYMPTOMS

Double-byte character set (DBCS) data is stored in SQL Server 6.5 whose code page is 1252. In SQL Server 6.5, you can select or insert DBCS characters correctly if you have turned off automatic translation for the connection. After you upgrade SQL Server to SQL Server 7.0 that uses the same code page, you can still retrieve and display the DBCS data correctly if you have automatic translation turned off for the connection and if the client application is using an earlier driver; however, with some utilities such as SQL Query Analyzer, you cannot insert or update the DBCS characters correctly on a corresponding non-English client even if you have dislabled automatic translation for the connection. The characters that are retrieved from the server are question mark (?) characters.

CAUSE

If the DBCS characters that are stored in the original code page 1252 for SQL Server 6.5 are not valid character data, SQL Server does not recognize their boundaries correctly. Microsoft does not always support the storing of code page X data in the code page Y of the computer that is running SQL Server. For example: Code page 950 data in a server whose code page is 1252.

In some cases, you may process these DBCS characters that are stored in computer that is running SQL Server whose code page is 1252 without any problems if you are using a SQL Server ODBC driver or a SQL Server OLE DB driver that is earlier than version 7.00.623. However, the SQL Server 7.0 ODBC driver and the Microsoft OLE DB provider for SQL Server introduce a new behavior when connecting to SQL Server Desktop Engine 1.0, SQL Server 7.0, or later versions of either product. When you use SQL Server Desktop Engine 2.1 or a later version of the SQL Server ODBC driver (version 3.70.0623 or later), or the Microsoft OLE DB provider for SQL Server (version 7.01.0623 or later), the SQL statements that SQL Server sends as a language event are converted to Unicode on the client before they are sent to the server. If the SQL Server code page is not the same as the client code page, SQL Server cannot process the data properly.

WORKAROUND

To work around this behavior:

Set up an additional instance of SQL Server that is using the clients code page (the staging server). For example, if the clients use code page 950 (Traditional Chinese), install the new SQL Server 7.0 server that is using code page 950.

NOTE: It is important that the new instance of SQL Server is earlier than SQL Server 7.0 Service Pack 2 (SP2) for this to be successful. Do not apply SQL Server SP2 or later to the new instance of SQL Server.For additional information about why you must use a build earlier than SP2, click the article number below to view the article in the Microsoft Knowledge Base:
236868 FIX: DMO Transfer Methods Does Not Perform Code Page Conversion
Use the Import Export Wizard to transfer objects between SQL Server 7.0:
1. On the computer that is running SQL Server, start the Import Export Data Wizard.
2. On the Choose a Data Source screen, select the original instance of SQL Server as the data source, and then select the corresponding database that you want to transfer.
3. On the Choose a Destination screen, select the new instance of SQL Server as the destination.
4. In the Database box, select new, and then type a name as the destination database.
5. On the Specify Table Copy or Query screen, click the Transfer objects and data between two SQL Server 7.0 databases option, and then follow the screen instructions to complete the wizard.
Verify that you migrated all database objects to the staging server that is running SQL Server.
Run RebuildM.exe to rebuild the master database on the original server so that it has the same code page settings as the staging server that you installed in step 1.
Move the system databases (master and msdb) from the staging server that is running SQL Server to the original server that you rebuilt with the new code page.
Run the sp_detatch_db stored procedure on all user databases on the staging server. For information about the sp_detatch_db stored procedure, see the "sp_detatch_db" topic in SQL Server Books Online.
Copy the physical .mdf, .ldf, and .ndf files that were associated with the user databases on the staging server to the original server.
Run the sp_attach_db stored procedure on the original server, and then attach the databases that you just detatched from the staging server. For information about the sp_attach_db stored procedure, see the "sp_attach_db" topic in SQL Server Books Online.

Steps 5 through 9 mention the procedure for moving system and user databases between the two computers that are running SQL Server. For additional information about how to move the databases, click the article number below to view the article in the Microsoft Knowledge Base:

224071 INF: Moving SQL Server 7.0 Databases to a New Location

NOTE: If you still have your 6.5 databases and you can repeat the upgrade, you can use the following (less difficult) workaround:

Re-install SQL Server 7.0 with the appropriate code page, such as 950.
Run the Upgrade Wizard again. During the Upgrade Wizard, make sure select code page 950 for the Code Page Override option.

MORE INFORMATION

If you are using the SQL Server Desktop Engine 2.1, or the SQL Server ODBC driver (version 3.70.0623 or later), or the Microsoft OLE DB provider for SQL Server (version 7.01.0623 or later), and you are connecting to SQL Server Desktop Engine 1.0 or later, or to SQL Server 7.0 or later, all SQL statements were converted as Unicode strings on the client before they were sent to the server regardless of whether the current automatic translation setting for the connection is on or off.

This behavior is described in the following Microsoft Knowledge Base article:

234748 PRB: SQL Server ODBC Driver Converts Language Events to Unicode

The code page of the remote computer that is running SQL Server does not affect the conversion. Before you send the data to the server, the SQL Server ODBC driver and the SQL Server OLE DB driver convert characters to Unicode according to the client computer's Original Equipment Manufacturer (OEM) code page. All the statements that are going through the wire (before arriving at the computer that is running SQL Server) have been accurately converted to the corresponding Unicode. For example, the SQL Server ODBC driver on a Simplified Chinese client converts all the Simplified Chinese characters to Unicode based on the client OEM code page (Simplified Chinese), and then it sends them out to the computer that is running SQL Server. Whether SQL Server can process these Unicode strings correctly is based on the SQL Server code page and the destination table field type.

Performing translation for character data is the only behavior of the ODBC driver. The data coming from the server is not converted; it goes through the wire as the same value that it was stored as in the database. For example, Unicode data is sent as Unicode data, and non-Unicode data is sent as non-Unicode data. When data arrives at the client, the client ODBC driver converts the data from the code page of the computer that is running SQL Server to the code page of the client computer according to the setting of the Perform translation for character data option. If this option is on, the conversion occurs.

The AutoAnsitoOem option only applies to DB-Library based programs such as isql/w. This option does not affect ODBC applications.

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

234748 SQL Server ODBC Driver Converts Language Events to Unicode

239530 Unicode String Constants in SQL Server Require N Prefix

75435 Windows, Code Pages, and Character Sets

170962 How to Insert DBCS Data in SQL Server from English Windows