Managing Data Conversion Between Client/Server Code Pages

This topic describes how to preserve the integrity of character data when the database does not store the character data by using Unicode data types and when your client-side applications that interact with the data are also not Unicode-aware. In this situation, the code page of your data storage and the code page of the client-side application should be the same. If these code pages differ, the conversion that occurs between the client and the server might cause the loss of some characters.

Disabling the AutoTranslate feature of the SQL Server ODBC driver to insert data defined by a different code page from the server is not supported. Also, even if AutoTranslate is disabled, it does not prevent code page translation for SQL language events. The result is that if the client and database code pages do not match, code page translation will generally be applied to any non-Unicode character string that is sent to or from the server.

If you can, you should avoid this situation. The best choice for a code page-specific server is to communicate only with the clients using the same code page. The second-best choice is to use another code page that has almost the same character set. For example, code page 1252 (Latin1) and code page 850 (Multilingual Latin1) can store almost the same set of characters, so that most characters in these two code pages can be converted from one code page to another without data loss.

If you must communicate with clients using different code pages, the supported solution is to store your data in Unicode columns. If any one of these options is not feasible, the other alternative is to store the data in binary columns using the binary, varbinary, or varbinary(max) data types. However, binary data can only be sorted and compared in binary order. This makes it less flexible than character data.

See Also

Concepts

Managing Data Conversion Between a Unicode Server and a Non-Unicode Client
Managing Data Conversion Between Unicode Encoding Schemes

Help and Information

Getting SQL Server 2005 Assistance

Change History