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:
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
Last Reviewed: February 18, 1999