INF: SQL Server Code Pages and AutoAnsiToOem Behavior

ID: Q153449

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 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

If AutoAnsiToOem is disabled, conversion is disabled for all connections.

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 a code page of 1250 thru 1259 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 ([ASCII 165]) in an ANSI server gets converted to ASCII 190 ([ASCII 165]) when selecting from an OEM client with code page 850, and to ASCII 157 ([ASCII 165]) 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

E Entering Extended Characters with a U.S. Keyboard

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

G How to Change the OEM code page of MS-DOS and Windows 3.x

H Client Suggestions for Consistent Data

I MS-DOS Isql.exe Examples

J Win32 Bcp.exe Examples

K Isqlw.exe Examples

L Transfer Manager Examples

M ODBC and ANSI to OEM Conversions

N MS-Query ODBC Client Examples

O Other Related Code Page Articles

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 "[ASCII 177]" saved on an 850 server, selecting the data from Isql/w may show you character "[ASCII 177]" (when AutoAnsiToOem is on) or "[ASCII 241]" (when AutoAnsiToOem is off). However, selecting ASCII(<column_name>) will show you 241 (which is "[ASCII 177]" 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.

Example 2:

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.

PART E: Entering Extended Characters with a U.S. Keyboard

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

In addition to changing the OEMCP registry value, this setting also changes the OEMHAL value, which controls the display of extended characters at a command prompt.

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 code page Destination SQL Server code page Client code 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. 
```

PART I: MS-DOS Isql.exe Examples

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.

PART J: Win32 Bcp.exe Examples

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.

Example 2:

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.

Example 3:

EXPORT of 165 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.

Example 4:

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.

PART K: Isqlw.exe Examples

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.

Example 2:

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

* [ASCII 165] converted to OEM equivalent.
** [ASCII 165] converted to ANSI equivalent.

PART L: Transfer Manager Examples

Example 1:

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

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

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

Example 2:

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 Server	Client conversion from source server	Client conversion to dest. server	Destination server
157	165*	165**	165

* [ASCII 165] converted to ANSI equivalent.
** Because both the client and server are ANSI no conversion is performed.

Example 3:

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 Server	Client conversion from source server	Client conversion to dest. server	Destination server
165	165*	190**	190

* Because both the client and server are ANSI no conversion is performed.
** [ASCII 165] converted to OEM equivalent.

Example 4:

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 Server	Client conversion from source server	Client conversion to dest. server	Destination server
190	165*	190**	190

* [ASCII 165] converted to ANSI equivalent.
** [ASCII 165] converted to OEM equivalent.

PART M: ODBC and ANSI to OEM Conversions

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 ConvertOEM 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.

Example 2:

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.

Example 3:

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.

PART O: Other Related Code Page Articles

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

Q75435 - Windows, Code Pages, and Character Sets
Q152980 - BUG: AnsiToOem Check Box Does Not Work in 6.0 Transfer Manager

Additional query words: OemToChar

Keywords : kbusage SSrvGen
Version : winnt:4.2x,6.0,6.5
Platform : winnt
Issue type : kbinfo