INF: SQL Server Code Pages and AutoAnsiToOem Behavior

• Microsoft SQL Server versions 4.21a, 6.0, and 6.5

SUMMARY

This article explains code pages in SQL Server, and how inserting or retrieving data is affected by enabling or disabling the AutoAnsiToOem setting.

A code page, also known as a character set, is a set of 256 uppercase and lowercase letters, numbers, and symbols. The printable characters of the first 128 values are the same for all character set choices. The last 128 characters, sometimes referred to as extended characters, differ from set to set. The default code page for SQL Server 4.21a is code page 850 (Multilingual). The default for SQL Server 6.0 and 6.5 is ISO 8859-1 (Latin 1 or ANSI), also known as code page 1252. The code page for SQL Server determines both the types of characters that SQL Server recognizes in your database, and the results that SQL Server returns for queries that involve character comparisons.

Note that the code page of SQL Server is different from the code page of the operating system. Windows NT and Windows 95 operating systems have an ANSI code page and an OEM code page. The ANSI and OEM code pages depend on the country settings. For the U.S. English Windows NT and Windows 95, the default ANSI code page is 1252, and the OEM code page is 437 (U.S. English). MS-DOS only has an OEM code page. For U.S. English, this code page is 437 by default. Windows 3.x runs as an extension to MS-DOS, and has an ANSI code page 1252 by default. Windows NT Console is internally unicode; however, this is a special case and it must appear to be running in an MS-DOS code page. Thus, console applications under Windows NT effectively run the OEM code page of the computer. However, any command line parameters passed to the console application are in the ANSI code page instead of the OEM code page and thus, a translation of AnsiToOem is required by the console application to properly pass the parameters to SQL Server.

The registry entry AutoAnsiToOem controls the default conversion behavior when data is inserted or retrieved. If AutoAnsiToOem is enabled, conversion is enabled in the following cases:

• ANSI clients to OEM servers
• OEM clients to ANSI servers

All of the Windows-based clients (such as Isql/w, SQL Enterprise Manager, Transfer Manager, SQL Administrator, and SQL Object Manager) are considered ANSI clients. MS-DOS or console-based applications such as Isql and BCP are considered OEM clients. The exception to this is the Transfer Data option of Object Manager, which behaves as an OEM client.

A SQL Server with code page 1252 (ISO 8859-1) is considered to be an ANSI server, and a SQL Server with any other code page (for example, 850 or 437) is considered to be an OEM server.

The OEM code page used to translate characters depends on the current OEM code page of the client's operating system. For example, with the AutoAnsiToOem option enabled, ASCII character 165 (¥) in an ANSI server gets converted to ASCII 190 (¥) when selecting from an OEM client with code page 850, and to ASCII 157 (¥) when selecting from an OEM client with code page 437.

MORE INFORMATION

The following table provides an overview for the rest of this article:

Part Description

A How to Determine the Code Page of SQL Server B Enabling or Disabling AutoAnsiToOem C How to Check the Validity of the Data Saved D What Happens when a Character on One Code Page is Unavailable on

         Another

PART A: How to Determine the Code Page of SQL Server

The stored procedure sp_helpsort can be used to find the SQL Server's sort order and character set.

PART B: Enabling or Disabling AutoAnsiToOem

The registry entry AutoAnsiToOem can be enabled or disabled by selecting the Automatic ANSI to OEM check box in the client configuration utility. In SQL Server 4.21a, Automatic ANSI to OEM is disabled by default, whereas in versions 6.0 and 6.5 it is enabled by default.

PART C: How to Check the Validity of the Data Saved

The Transact SQL string function ASCII(char_expr) can be used to determine what is actually saved on the server. This function shows the ASCII code value of the leftmost character in a character expression. For example, if you have character "±" saved on an 850 server, selecting the data from Isql/w may show you character "±" (when AutoAnsiToOem is on) or "ñ" (when AutoAnsiToOem is off). However, selecting ASCII(<column_name>) will show you 241 (which is "±" on 850 code page).

PART D: What Happens when a Character on One Code Page is Unavailable on Another

If a conversion occurs and a character on one code page is unavailable on another, the character gets converted to its closest equivalent character on the other code page. For example, ASCII 224 ("alpha") on code page 437 is converted to 97("a") on the ANSI code page. When you convert it back to code page 437, you receive 97("a"). This is a problem, because you have now lost the original 224("alpha"). This can potentially cause incorrect data to be saved on your server, as happens in the following two examples:

Example 1:

   You have an OEM server in which you are inserting data from an ANSI
   client, and your AutoAnsiToOem option is enabled. If the character that
   you are inserting is valid in both code pages, the correct data will be
   saved. However, if the character is valid in ANSI but not in OEM,
   the correct data will not be saved.

   You insert data into an ANSI server from an OEM client, and the
   AutoAnsiToOem option is enabled. If the character that you are inserting
   is valid in both code pages, the correct data will be saved. However, if
   the character is valid in OEM and not in ANSI, then correct data will
   not be saved.

Press NUMLOCK and use the numeric keypad on the right side of the keyboard. To enter ANSI extended characters, hold the ALT key and then type the ASCII code of the character preceded by a zero (0). To enter OEM extended characters, hold the ALT key and type just the ASCII code of the character. For example, to enter ¥, hold ALT and type 0165, or type 157 (if the client's OEM code page is 437), or type 190 (if the client's OEM code page is 850).

PART F: How to Change the OEM Code Page of Windows NT and Windows 95

For Windows NT 3.51, the preferred method of changing the OEM code page in Windows NT is to change the language in Control Panel International. For Windows NT 4.0 or Windows 95, the OEM code page is chosen during initial setup.

Additionally, The OEM code page can be changed by changing the OEMCP and OEMHAL (Windows NT only) registry values in the following location:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentlControlSet\Control\Nls\CodePage

These changes take effect after rebooting Windows NT or Windows 95.

The OEMCP value controls to which OEM code page to translate ANSI, and the OEMHAL value controls the display of extended characters at a command prompt. Some of the possible values for these registry values are:

OEM Code page OEMCP value OEMHAL value

437            437          vgaoem.fon
850            850          vga850.fon

PART G: How to Change OEM Code Page of MS-DOS and Windows 3.x

To change the code page for MS-DOS, refer to Chapter 6, National Language Support in the MS-DOS Programming Reference 6.0. Once the MS-DOS code page is changed, you must run the Windows Setup program to change its ANSI-OEM translation tables for ANSI to OEM conversion.

PART H: Client Suggestions for Consistent Data

The following suggestions will help to ensure that the data stored on the SQL Server is stored according to the SQL Server code page rather than the client's code page, which is usually the cause of bad data.

MS-DOS clients: Since MS-DOS only has an OEM code page and no ANSI code page, no conversions are made. The client will always store data on the server with the client's OEM code page, so the code page of SQL Server should be set to match the client's code page.

OEM and ANSI clients: If the computer running SQL Server is not using the ANSI code page, change the client's OEM code page to match the SQL Server code page and enable the AutoAnsiToOem conversion. If the computer running SQL Server is using the ANSI code page, just enable the AutoAnsiToOem conversion.

The following are exceptions to the above suggestions:

• For the Win32 Bcp.exe client importing to an ANSI SQL Server, change the client's code page to match the code page that the data was created with.
• For the Win32 Bcp.exe client exporting from an ANSI SQL Server, change the client's code page to the code page of the SQL Server that the export results will be imported into. For example, to import into a SQL Server with code page 850, change the client's code page to 850.
• For the SQL Server Transfer Manager 4.2x and 6.0 client, change the client's code page to match the code page of the non-ANSI code page SQL Server, if both SQL servers are not using the ANSI code page. The following table summarizes this:

     Source SQL Server  Destination SQL   Client code
     code page          Server code page  page to use
     ------------------------------------------------
  
     ANSI               437               437
     850                ANSI              850
     850                850               850
     437                850               *
  
     * When transferring between OEM code pages, make two transfers instead:
       OEM1 to ANSI followed by ANSI to OEM2.
  
  

The tables in each of the following examples show the decimal equivalents of the character at various points.

INSERT and SELECT of the ¥ character with the MS-DOS Isql.exe client with a 437 OEM code page into and from a 437 code page SQL Server.

Operation On client Client conversion On server

INSERT     157        157*               157
SELECT     157        157*               157

   * The value never changes from 157, since MS-DOS clients do not perform
     AnsiToOem conversions.

Example 1:

EXPORT and IMPORT of the ¥ character Win32 Bcp.exe client with an 850 OEM code page into an 850 code page SQL Server.

Operation On client Client conversion On server

IMPORT     190        190*               190
EXPORT     190        190*               190

   * Because both the client and server are OEM, no conversion is
     performed.

IMPORT of ¥ character Win32 Bcp.exe client with a 437 OEM code page, and data created with code page 437, into an ANSI code page SQL Server.

Operation On client Client conversion On server

IMPORT     157        165*               165

   * ¥ converted to ANSI equivalent.

EXPORT of ¥ character Win32 Bcp.exe client with a 437 OEM code page, from an ANSI code page SQL Server, to import later into a 437 code page SQL Server.

Operation On server Client conversion On client

EXPORT     165        157*               157

   * ¥ converted back to OEM equivalent.

EXPORT of ¥ character Win32 Bcp.exe client with a 1252 OEM code page, from an ANSI code page SQL Server to import later into the same SQL Server.

Operation On server Client conversion On client

EXPORT     165        165*               165

   * Because the ANSI code page matches the OEM code page on the client, no
     conversion occurs.

Example 1:

INSERT and SELECT of Win16 or Win32 Isqlw.exe client into an ANSI code page SQL Server.

Operation On client Client conversion On server

INSERT     165        165*               165
SELECT     165        165*               165

   * Because both the client and server are ANSI, no conversion is
     performed.

INSERT and SELECT of Win16 or Win32 ISQLW.EXE client with OEM code page 437 into a 437 code page SQL Server.

Operation On client Client conversion On server

INSERT     165        157*               157
SELECT     157        165**              165

    * ¥ converted to OEM equivalent.
   ** ¥ converted to ANSI equivalent.

Example 1:

SQL Server Transfer Manager 6.0 between two ANSI code page SQL Servers.

Source  Client conversion   Client conversion   Destination
server  from source server  to dest. server     server

165     165*                165*                165

   * Because both the client and server are ANSI, no conversion is
     performed.

SQL Server Transfer Manager 6.0 with OEM code page 437, from a 437 code page SQL Server to an ANSI code page SQL Server.

Source  Client conversion   Client conversion   Destination
server  from source server  to dest. server     server

157     165*                165**               165

    * ¥ converted to ANSI equivalent.
   ** Because both the client and server are ANSI no conversion is
      performed.

SQL Server Transfer Manager 6.0 with OEM code page 850, from an ANSI code page SQL Server to an 850 code page SQL Server.

Source  Client conversion   Client conversion   Destination
server  from source server  to dest. Server     server

165     165*                190**               190

    * Because both the client and server are ANSI no conversion is
      performed.
   ** ¥ converted to OEM equivalent.

SQL Server Transfer Manager 6.0 with OEM code page 850, from an 850 code page SQL Server to an 850 code page SQL Server.

Source  Client conversion   Client conversion   Destination
server  from source server  to dest. Server     server

190     165*                190**               190

    * ¥ converted to ANSI equivalent.
   ** ¥ converted to OEM equivalent.

All ODBC clients (GUI and console applications) are ANSI clients as defined by the ODBC specification. Translation will only occur with non-ANSI code page SQL Servers. ODBC clients have three translation choices: no translation, translate with OEM code page, or use an ODBC Code Page Translator. Translation with the OEM code page works the same as DB-Library ANSI to OEM conversion, in that the OEM code page of the client determines what is stored on the server. This option is chosen by selecting the Convert OEM to ANSI characters check box in the Datasource Setup dialog box. To use an ODBC Code Page Translator, click the Select button in the Translation section of the Datasource Setup dialog box, click the MS Code Page translator, and then select the code page translator to use. This option overrides the Convert OEM to ANSI characters check box and does the OEM translation according to the translator chosen, instead of the OEM code page. All ODBC clients are ANSI clients, and translation will only occur with non-ANSI code page SQLservers.

PART N: MS-Query ODBC Client Examples

Example 1:

SELECT of MS-Query client from an ANSI code page SQL Server.

On server Client conversion On client

165        165*               165

   * Since both client and server are ANSI, no conversion is performed no
   matter which translation is chosen.

SELECT of MS-Query client with OEM code page 437, and Convert OEM to ANSI character enabled, from a 437 code page SQL Server.

On server Client conversion On client

157        165*               165

   * Converted to ANSI equivalent.

SELECT of MS-Query client with OEM code page 437, Convert OEM to ANSI character enabled, and the Windows Latin 1(1252)/850 (Multilingual- Latin 1) MS Code Page translator from a 850 code page SQL Server.

On server Client conversion On client

190        165*               165

   * Converted to ANSI equivalent using 850 OEM code page due to
     translator.

For more information on code pages and the AnsiToOem option, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q75435
   TITLE     : Windows, Code Pages, and Character Sets

   ARTICLE-ID: Q152980
   TITLE     : BUG: AnsiToOem Check Box Does Not Work in 6.0 Transfer
               Manager