Character data is represented incorrectly when the code page of the client computer differs from the code page of the database in SQL Server 2005 (904803)

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

Change 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:

Method 2: Use an appropriate collation for the database

If 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 type

If 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 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 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:

1. Manually convert the characters to raw data, and then insert the data into the database by using the code page of the database. To do this, use code that 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 the database;

   Note This code example is written in C#.
2. When you want to query the data, use Microsoft OLE DB Provider for SQL Server or Microsoft .NET Framework Data Provider for SQL Server to connect to the database, and then set the Auto Translate parameter to False. To do this, use code that 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;
   

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