You cannot correctly translate character data from a client to a server by using the SQL Server ODBC driver if the client code page differs from the server code page (234748)

MORE INFORMATION

Autotranslation (that is, the "Perform Translation for character data" checkboxes in newer ODBC applications) converts character data from the client code page to the server code page before sending the data to the server, using Unicode as a translation medium. However, the 3.7 SQL Server ODBC driver also converts all SQL statements sent as a language event to Unicode before placing them on the wire, which has an effect that is similar to Autotranslation but is not governed by the Autotranslation setting. In contrast, character data flowing from the server back to the clients respect the Autotranslation flag; if Autotranslation is turned off the data arrives at the client application with the same character codes as the data had on the server. Similarly, translation of data for client-to-server RPC events can be disabled by turning off Autotranslation. A simple script that demonstrates how this behavior affects language events follows. This example was run from Query Analyzer on a code page 1252 client connecting to a code page 437 server:

-- Turn Autotranslation off here.
    USE tempdb
    GO
    CREATE TABLE t1 (c1 int, c2 char(1))
    GO
    
    -- Enter a yen character, using the keystroke ALT-0165.
    INSERT INTO t1 VALUES (1, '¥') 
    SELECT c1, c2, ASCII (c2) FROM t1

Note the following about the preceding example:

• Even though Autotranslation was off during this batch, the character code 165 (yen in code page 1252) was converted to 157 (yen in code page 437). This is because the ODBC driver converted the SQL string to Unicode before sending it the the server, so the server was able to convert it to the appropriate character for storage in code page 437.
• When the client ran a SELECT to retrieve the data that had just been stored, the character 157 arrived non-translated at the client (157 shows up as a box "�" on a code page 1252 client). This is because the conversion discussed in this article only applies to data sent from the client to the server, not from the server to the client. The data was not translated because the Autotranslation setting is off.

-- Turn Autotranslation back on before running the following batch.
    INSERT INTO t1 VALUES (2, '¥')
    SELECT c1, c2, ASCII (c2) FROM t1

In this case, turning Autotranslation back on had no effect on the translation from the client to the server (that is, the same correct translation from character code 165 to character code 157 happened), but it did have an effect on the data retrieved from the server. Note that when the SELECT statement is run this time (with Autotranslation on), the yen symbols display correctly in the code page 1252 application because they have been translated from character code 157 back to character code 165 by the Autotranslation mechanism.

You will see this behavior (conversion of language events to Unicode on the client) when using any SQL Server ODBC driver version 3.70 or later and connecting to SQL Server 7.0 or later. It will not occur when using older ODBC drivers, or when using the 3.7 driver to connect to SQL Server 6.5 or earlier. In addition, if you are storing your data in Unicode columns (NCHAR/NVARCHAR/NTEXT) the conversion will not be an issue.

For more information about how character data is represented in SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base: