Article ID: 904803 - View products that this article applies to.
Consider the following scenario:
For example, you may experience one of the following problems:
This problem occurs because the character data of code page X is stored in a non-Unicode column of code page Y. Additionally, the character data is not translated correctly. We do not support storing the character data of code page X in a column of code page Y.
In SQL Server 2005, when you use a string literal of a non-Unicode data type, the string literal is converted by using the database's default code page that is derived from the database's collation. Storing the character data of code page X in a column of code page Y may cause data loss or data corruption.
If the character data is represented as corrupted data, the data can be represented correctly only if you disable the Auto Translate parameter for Microsoft OLE DB Provider for SQL Server or for Microsoft .NET Framework Data Provider for OLE DB.
Note SQL Server Management Studio uses Microsoft .NET Framework Data Provider for SQL Server to connect to the SQL Server database. This data provider does not support the Auto Translate parameter.
To work around this problem, use one of the following methods.
Method 1: Use a Unicode data type instead of a non-Unicode data typeChange the columns to a Unicode data type to avoid all the problems that are caused by code page translation. For example, use the nchar data type, the nvarchar data type, or the ntext data type.
For more information about storing Unicode data, click the following article number to view the article in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/239530/ )You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
Method 2: Use an appropriate collation for the databaseIf you must use a non-Unicode data type, always make sure that the code page of the database and the code page of any non-Unicode columns can store the non-Unicode data correctly. For example, if you want to store code page 949 (Korean) character data, use a Korean collation for the database. For example, use the Korean_Wansung_CI_AS collation for the database.
Method 3: Use the binary data type or the varbinary data typeIf you want the database to directly store and retrieve the exact byte values of the characters that are handled without trying to perform appropriate code page translation, use the binary data type or the varbinary data type.
Method 4: Use a different tool to store and retrieve data, and disable the Auto Translate parameterWarning We do not test or support storing the character data of code page X in a column of code page Y. This operation may cause linguistically incorrect query results, incorrect string matching or ordering, and unexpected code page translation (data corruption). We encourage you to use one of the other methods to work around this problem.
When you use Microsoft OLE DB Provider for SQL Server to connect to a database that has a different code page and you try to query character data from a non-Unicode data type column, you must make sure that you store the untranslated characters to the database.
Note The following example assumes that the code page of the client computer is Korean (CP949) and that the code page of the SQL Server database is English (CP1252). You must replace the placeholders in the code examples with values that are appropriate for your situation.
To work around this problem, follow these steps:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Steps to reproduce the problem
For more information about the SQL Server collation and the Auto Translate parameter, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/162367/ )How to transfer Korean Double Byte Character Set chars
234748For more information about SQL Server Unicode data types, visit the following Microsoft Developer Network (MSDN) Web site:
(https://support.microsoft.com/kb/234748/ )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
Article ID: 904803 - Last Review: March 16, 2007 - Revision: 1.4