Reprint: Character data is represented incorrectly when the code page of the client computer differs from the code page of the D Atabase in SQL Server 2005

https://support.microsoft.com/en-us/kb/904803

Character data is represented incorrectly, the code page of the client computer differs from the code page of the data Base in SQL Server 2005

• Email
• Print

Symptoms

Consider the following scenario:

• In Microsoft SQL Server 2005. SQL Server Management Studio to query character data from a SQL Server database Uses a Non-unicode data type. For example, the SQL Server database uses the char data type, Thevarchar data type, or the text data type.
• The code page of the client computer differs from the code page of the database. The code page is associated with the collation of the database.

In this scenario, character data is represented incorrectly.
For example, experience one of the following problems:

• The character data is represented as a question mark (?). Problem if you inserted or updated the character data as a Non-unicode data type before you queried the C Haracter data. This problem occurs if you make the this change by using SQL Server Management Studio on a client computer that have a differen T code page.
• The character data is represented as corrupted data. The character data of code page is X stored in a non-unicode column of code page Y . Additionally, the character data is not translated. This problem occurs if you query the character data by using SQL Server Management Studio.
  Note when you query the character data by using SQL query Analyzer in Microsoft SQL Server A, the character data is rep Resented correctly if the Perform translation for character data setting (the Auto translateparameter) is disable D. The Auto Translate parameter is a parameter of the ConnectionString property for Microsoft OLE DB Provider for SQL Serv Er and for the Microsoft. NET Framework Data Provider for OLE DB.

Cause

This problem occurs because the character data of code pageXis stored in a non-unicode column of code pageY. Additionally, the character data is not translated correctly. We do not support storing the character data of code pageXIn a column of code pageY.
In SQL Server 2005, when you use a string literal of a Non-unicode data type, the string literal are converted by using the Database ' s default code page is derived from the database ' s collation. Storing the character data of code pageXIn a column of code pageYMay cause data loss or data corruption.
If the character data is represented as corrupted data, the data can being 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 isn't support the Auto Translate parameter.

Workaround

To work around this problem, use one of the following methods.

Method 1:use a Unicode data type instead of a Non-unicode data type

The columns to a Unicode data type to avoid all the problems is 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 K Nowledge Base:

239530 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 database

If You must use a Non-unicode data type, always make sure, 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 type

If you want the database to directly store and retrieve the exact byte values of the characters that is handled without t Rying to perform appropriate code page translation with 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 parameter

Warning 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 cod E page translation (data corruption). We encourage you and use one of the methods to work around this problem.
When do I use Microsoft OLE DB Provider for SQL Server to connect to a database this has a different code page and you Try to query character data from a Non-unicode data type column, and you must make sure that you store the untranslated Charac Ters to the database.
Note The following example assumes that the code page of the client computer be Korean (CP949) and that the code page of the SQL Server database is 中文版 (CP1252). You must replace the placeholders in the code examples with values that is appropriate for your situation.
To work around this problem, follow these steps:

• manually convert the characters to raw data, and then inserts the data into the database by using the code page of the Database. To does this, use code, which is similar to the following code example.

    string strsrc= "?"; String strsrc= "?"; String strtag=encoding.getencoding (1252). GetString (Encoding.GetEncoding (949). GetBytes (STRSRC)); sql= "INSERT into <tablename> (<COLUMN>,) VALUES ('" + Strtag + "')";//code for updating th e database;  

  Note This code example are written in C #.

• When you want to query the data, use Microsoft OLE DB Provider for SQL Server or Microsoft. NET Framework Data Provider fo R SQL Server to connect to the database, and then set the ' Auto Translate parameter to False. To does this, use code, which is similar to the following code example.

  OleDbConnection conn=new OleDbConnection("Provider=SQLOLEDB;" +" Initial Catalog =<yourdatabase>;"+"User id=<youruserid>; Password=<yourpassword>;"+"Auto Translate=False");// code for representing the character data;

STATUS

Microsoft has confirmed a problem of the Microsoft products which is listed in the ' Applies to ' section.

More informationsteps to reproduce the problem

• On the client computer this has Korean (CP949) as the default code page, start SQL Server Management Studio.
• Connect to a database (CP1252) as the default code page.
• Create a table in the database by using the following line of code.

  Create table tbTest (A char(20), NA nchar(10), Comment char(20))

• Insert a Korean character to the database by using the following query.

  Insert into tbTest (A,NA,Comment) values(‘?‘,N‘?‘,‘SQL2005/INSERT‘)

• Create a SELECT query to retrieve the data by using the following line of code.

  select * from tbTest

You receive the following results. The value in column A is a question mark.

A                    NA         Comment-------------------- ---------- --------------------?                    ?          SQL2005/INSERT

REFERENCES

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:

162367 How to transfer Korean Double Byte Character Set chars

234748 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

For more information about SQL Server Unicode data types, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn2.microsoft.com/en-us/library/aa902669 (sql.80). aspx

Reprint: Character data is represented incorrectly when the code page of the client computer differs from the code page of the D Atabase in SQL Server 2005