Transfering Chinese names from Oracle to SQL Server 2008
I needed to move some data containing chinese alphabets in Oracle Database to SQL Server Database. I saved the data from Oracle as a dat file say x.dat. I am using x.dat as my source file and using file connection manager in SSIS I am trying to tansfer
the data to the corresponding table in SQL Server. Oracle table and SQL Server table have the same structure. Data type in Oracle is varchar(50) and the corresponding columns in SQL Server has data type nvarchar(255). I changed the codepage option in
file manager in SSIS to Traditional Chienese. The data is loaded. How should I know the Chinese letters are moved correctly? If I use select statement in Oracle the Chinese letters are displayed as ' ??? ' not in the form of a chinese letter. After moving
data in Oracle I used select statement in SQL Server . The characters are displayed as some kind of rectangles . Is there any way to see the characters as real chinese characters? Is there any way to make sure the data is transferred correctly?
April 18th, 2012 6:22pm
Is the chinese font installed on the server and local pc? I know SSRS has that requirement.Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!
Free Windows Admin Tool Kit Click here and download it now
April 18th, 2012 8:18pm
There is a simple way to check the data using Excel. For that you need the Chinese font installed in your machine. Even if you have the font installed, the oracle interface tools like TOAD or SQL Server Management studio will not show the chinese letters.
For this use Excel to test the data
1. Install a Chinese font
2. Load the data into Excel using ODBC from Oracle/SQL Server.
This will show the chinese letters.
Bunch of thanks ~ Deva ~ mark it as answer if it answered your question :)
April 19th, 2012 1:49am
Thanks for the reply...
I am not sure Chinese is installed. If I go to control panel->settings->date, time, regional language options a window pops up. If I click on Language tab there is an
option to install East Asian Language like Japanese, Chinese etc. Is it what I should do? It will ask CD to install.
Instead If I click on the Advanced tab then under the Advanced TAB in the Code Page drop down simplified Chinese and Traditional Chinese checkboxes are already checked. Does
it mean the Chinese language is already installed?
I have not seen any Chinese fonts among the Microsoft Word fonts
Free Windows Admin Tool Kit Click here and download it now
April 19th, 2012 10:46am
Try the 2nd part of Devanathans suggestion then and connect to the data, see if it displays in Excel correctly (then the font is installed!)
If not, I found these steps, though personally on Win 7 with Excel 2007 I couldn't follow them. I am in a locked down environment though.
Start
Programs
Microsoft Office Tools
Language Settings
Click on Chinese Simplified
Add
Ok
YesBest Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!
April 19th, 2012 6:56pm
Thanks. What you suggested is right. I got installed the East Asian Language fonts. Now I can see characters. I hope it is real chinese in the real chinese form. In between I was with some other tasks. Again getting back to Chinese...I have seen a
piece of code online.
CREATE TABLE OrientalExpress (
ID
INT IDENTITY ( 1 , 1 ) PRIMARY KEY,
FirstName
NVARCHAR(10),
LastName
NVARCHAR(10),
Address
NVARCHAR(50),
City
NVARCHAR(20),
State
NVARCHAR(2),
Country
NVARCHAR(20),
Zipcode
NVARCHAR(10),
ModifiedDate
DATETIME default(getdate()))
GO
INSERT OrientalExpress(FirstName, LastName, Address, City,
State, Country, Zipcode)
SELECT N'', N'', N'
',
N'', N'', N'', N'13663'
GO
select firstname from dbo.OrientalExpress order by firstname collate Chinese_Simplified_Stroke_Order_100_CI_AS
I do not remember that website. It is not mine.
After installing East Asian Characters without using collate I can see the chinese characters. Use the select query below
select firstname from dbo.OrientalExpress
Then what does collacte do exactly?
Also see the link below. I did not tried it.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=173781
Thanks
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 8:28am