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

• In Microsoft SQL Server 2005, you use SQL Server Management Studio to query character data from a SQL Server database that uses a non-Unicode data type. For example, the SQL Server database uses the char data type, the varchar 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.

• The character data is represented as a question mark (?). You may see this problem if you inserted or updated the character data as a non-Unicode data type before you queried the character data. This problem occurs if you make this change by using SQL Server Management Studio on a client computer that has a different code page.
• The character data is represented as corrupted data. 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. This problem occurs when 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 2000, the character data is represented correctly if the Perform translation for character data setting (the Auto Translate parameter) is disabled. The Auto Translate parameter is a parameter of the ConnectionString property for Microsoft OLE DB Provider for SQL Server and for Microsoft .NET Framework Data Provider for OLE DB.

Back to the top

Back to the top

Method 1: Use a Unicode data type instead of a non-Unicode data type

Back to the top

Method 2: Use an appropriate collation for the database

Back to the top

Method 3: Use the binary data type or the varbinary data type

Back to the top

Method 4: Use a different tool to store and retrieve data, and disable the Auto Translate parameter

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;
   

Back to the top

Steps to reproduce the problem

1. On the client computer that has Korean (CP949) as the default code page, start SQL Server Management Studio.
2. Connect to a database that has English (CP1252) as the default code page.
3. 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))

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

   Insert into tbTest (A,NA,Comment) values('가',N'가','SQL2005/INSERT')

5. Create a select query to retrieve the data by using the following line of code.

   select * from tbTest

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

Back to the top