Before shifting this topic to gateways/HS discussion...
Back to the original question - I WAS using heterogeneous services and
got a problem moving Unicode data from SQL Server to Oracle.
*From:* Jan Benjamins
*Sent:* Friday, November 07, 2008 4:04 PM
*Cc:* mrosenblum_at_dulcian.com; Jared Still; oracle-l_at_freelists.org
*Subject:* Re: Unicode data migration from SQL Server to Oracle
You could look into oracle heterogeneous services, just google for
these terms, you find enough, no need for transparent gateway
(license). I think you need odbc for this, so for windows no
problem, for unix you could look at the datadirect drivers (from
Merrant ?). Used it years ago, pretty straighforward.
On Fri, Nov 7, 2008 at 7:11 PM, Goulet, Richard
If that's what your developers want then your only option,
that I know of, is Oracle's transparent gateway to
Sql*Server. You install it on the Sql*server database machine
& then create database links inside Oracle to it. Not very
hard to set up(should take you about an hour max). Very easy
to use and you can control whether or not they can update
Sql*Server data from Oracle.
Senior Oracle DBA
/ information transmitted in this communication is intended
only for the person or entity to which it is addressed and may
contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of
any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If
you received this in error, please destroy any copies, contact
the sender and delete the material from any computer./
] *On Behalf Of *Michael
*Sent:* Friday, November 07, 2008 1:06 PM
*To:* 'Jared Still'
*Subject:* RE: Unicode data migration from SQL Server to Oracle
It is not a problem to push data from SQL Server to Oracle
even using Microsoft's "Import and Export Data" utility (as
long as Oracle's own ODBC drivers are used)
But our developers want to have views on Oracle side that
would point to SQL Server tables - and write migration scripts
againt these views.
*From:* Jared Still [mailto:jkstill_at_gmail.com
*Sent:* Friday, November 07, 2008 12:12 PM
*Subject:* Re: Unicode data migration from SQL Server to
Have you tried using the Migration Workbench in SQL Developer?
It doesn't require using ODBC.
The Oracle connection is via Java connector, and the SQL
connection is via open source SQL Server connector (jTDS)
If you do try it, use version 1.2. The new 1.5 version of
doesn't work for the SQL Server -> Oracle migration.
I just used it this week, and have a question in on OTN
for the 1.5 version.
Certifiable Oracle DBA and Part Time Perl Evangelist
On Fri, Nov 7, 2008 at 7:39 AM, Michael Rosenblum
Good morning, all!
Currently we are building a system in Ethiopia -- and
as a part of the project need to do a data migration.
1. Source -- SQL Server 2000 Database with a number of
tables with NVARCHAR datatype
a. Stored data is in Ge'ez language -- it does
mean that there is no standard locale.
b. SQL Server internally is using UCS-2 to store
Unicode data (compatible with UTF16)
2. Target -- Oracle 10g (10.2.0.4 <http://10.2.0.4
Enterprise edition on Windows 2003 Server 32-bit
a. NLS_CHARACTERSET = AL32UTF8
3. Migration mechanism - Heterogeneous Services via ODBC
Problem - *Unicode data comes to Oracle as '?'*.
And it is not a visual affectation:
SQL> select ascii("Column1") a, "Column1" from
1. OLEDB -- the same problem
2. Third party ODBC drivers -- don't even recognize
3. Played with HS_LANGUAGE and HS_NLS_LANG -- no impact.