SQL Server Performance Forum – Threads Archive

Special Characters in SQL Server 2005

Hello Everyone,
I load data every day from Sybase to SQL server 2000 and sybase database has some special characters in the database. I never had issues with special characters in reading/storing them SQL 2000 tables . We recently migrated our database from SQL 2000 to SQL Server 2005 and now SQL 2005 database engine is reading special characters incorrectly and storing them in weird format. I would really appreciate if anyone can through their ideas/suggestions to resolve this issue. Both SQL 2000 and SQL 2005 instances have same type of collation. The data loads from Sybase database source to SQL database everyday . The value "MuÃ±oz" with special character is stored in SQL 2000 instance as is on sybase database but in SQL 2005 it stored as either "MuÂ¤oz" or "MuÂ±oz". Another obseration is; Copying from one instance of SQL Server 2005 to another instance of SQL 2005, the special characters are stored correctly. Ex: "MuÃ±oz" will be copied as is in another instance of SQL server 2005. I cannot give any good explaination to my manager about this. He asks me what is the solution to fix this issue? I would greatly appreciate anyone’s experience and thoughts on this issue. Bhushan
Bhushan Kalla

Also check the windows locale settings in this case and try to select the data by specifying the required collation. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Hi ,
Please let me know if you have got any solution of this.infact I am facing same issue in my project.
Regrds
rohit

Welcome to the forums.
You can store the special characters and manage encoding of text, UTF-8 encoding to
strings before passing them into the database. As per the BOL SQL Server 2005 uses a UCS-2 encoding scheme to encode Unicode characters in binary format. Some languages, such as Chinese, define additional characters that are less frequently used and are not part of the Unicode standard. UCS-2 stores these characters, supplementary characters, as two undefined Unicode characters that define a supplementary character in storage when they are paired together.
This way, SQL Server stores supplementary characters without risk of loss or corruption. Also, you can use and display supplementary characters in any SQL Server-based application, including tools-based applications such as Business Intelligence.
Many server-to-server activities can fail or yield inconsistent results if collation settings are not consistent across servers. To avoid this, select a Windows locale or SQL Server collation (SQL_*) to match the collation settings in other instances of SQL Server.

]]>