March 12, 2009 at 8:37 pm
We got an issue related to scrambled character like 「╔¢╬¸╩íÁþ┴ª╣½╦¥」. The issue may caused by we were trying to integrate databases with different collation.
For instance, the collation of the database in China is SQL_1xCompat_CP850_CI_AS and the collation of the database in U.S. is SQL_Latin1_General_CP1_CI_AS.
In the other hand, the China's database doesn't store records by Unicode but the database in U.S. does.
We wrote an application by .Net. In this application we were trying to read records from China's database by SqlDataReader ,a .Net object, and use web service method to insert the record into U.S.’s database.
However the retrieved records from China's database are shown in scrambled characters therefore, after inserting it via web service, the records in the destination database are shown in scrambled characters too.
We tried to use the following 2 methods to work around it but they didn't work.
Idea 1, insert the records in to a temp table with another collation first.
--------------------------------------------------------------------------------------------------------------------------
create table #temptable([Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
insert into #temptable SELECT top 10 Name FROM ChinaTable
select * from #temptable
--------------------------------------------------------------------------------------------------------------------------
Idea 2, using CAST or CONVERT function.
--------------------------------------------------------------------------------------------------------------------------
SELECT top 10 CAST ( Name AS nvarchar(100))
FROM ChinaTable
SELECT top 10
CONVERT ( nvarchar(100) , Name
FROM ChinaTable
--------------------------------------------------------------------------------------------------------------------------
Could you give me any suggestion to work around it? 🙂
March 13, 2009 at 8:31 am
Is your target format Unicode or non-unicode.
If it is unicode try doing a select into a table with unicode defs in your source db and see if it will convert (this sometimes works)
but without understanding your target datatype it's hard to help.
March 13, 2009 at 9:05 am
You need to change the Chinese database table definition to Unicode and then use Text Encoding in your .NET code, in .NET 3.5 most known languages can be encoded. You can also save the .NET code as Chinese.
http://msdn.microsoft.com/en-us/library/system.text.encoding.aspx
Kind regards,
Gift Peddie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply