SqlServer to Oracle Migration of Unicode Data

Hello all,

I have a SqlServer db that has quite a few tables that have nvarchar fields. From what I have read I believe these nvarchar fields are stored in UCS-2 which I guess is a version of Unicode. I need to preserve this Unicode data during the Oracle migration.

I am using bcp/sqlldr for unloading/loading respectively. I have migrated the data as ascii fine, using the -c option for bcp which unloads data to ascii flat files. Sqlldr loads this ascii data fine, and I am able to do a full load/unload for all tables using this 'ascii only' method.

I run into problems when I try to recreate the unload/load process using Unicode files, not ASCII. I use the -w option of bcp which should unload data to a Unicode flat file. Then when I use Sqlldr to load the data, I specify the character set 'UTF8', make sure my field/row terminators are the hexadecimal representation in unicode, and use direct path because my sessions nls_lang is not a superset of unicode. When I run sqlldr, all I see is a few lines displaying its version, and then it exits without any type of error or informational message. The log file for the load is also empty.

I could be way off, but this was the only way I could find to get this Unicode data migrated properly. Unfortunately I cannot get it to work.

Has anyone had success migrating multi-byte character sets from SqlServer to Oracle? Any tips would be greatly appreciated.

Brandon

PS: I am ftping the unicode flat files from dos to unix using binary mode. I am then using `tr -d '\r'` to remove all carriage returns. Could this be a problem. I have also tried lots of variations of the transfer process to see if it might have been the problem.

PS2: Oracle version is 8.1.5.2. Received on Fri Jul 14 2000 - 00:00:00 CDT