Re: Unicode data migration from SQL Server to Oracle

IIRC, HS has its own init.ora parameter file and you need to set hs_nls_lang there.

Adar Yechiel
Rechovot, Israel

Michael Rosenblum wrote:
> 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.
>
> Any help?
>
> Thanks,
> Michael Rosenblum
>
> -----Original Message-----
> *From:* Jan Benjamins [mailto:jan.benjamins_at_gmail.com]
> *Sent:* Friday, November 07, 2008 4:04 PM
> *To:* Richard.Goulet_at_parexel.com
> *Cc:* mrosenblum_at_dulcian.com; Jared Still; oracle-l_at_freelists.org
> *Subject:* Re: Unicode data migration from SQL Server to Oracle
>
> Hi,
>
> 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.
>
> regards,
>
> Jan
>
> On Fri, Nov 7, 2008 at 7:11 PM, Goulet, Richard
> <Richard.Goulet_at_parexel.com <mailto:Richard.Goulet_at_parexel.com>>
> wrote:
>
> Michael,
>
> 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.
>
>
> */Dick Goulet/*//
> Senior Oracle DBA
> PAREXEL International
> 978.313.3426
> / 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./
>
>
>
>
> ------------------------------------------------------------------------
> *From:* oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> [mailto:oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>] *On Behalf Of *Michael
> Rosenblum
> *Sent:* Friday, November 07, 2008 1:06 PM
> *To:* 'Jared Still'
>
> *Cc:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *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.
>
> Regards,
> Michael Rosenblum
> Dulcian Inc
>
> -----Original Message-----
> *From:* Jared Still [mailto:jkstill_at_gmail.com
> <mailto:jkstill_at_gmail.com>]
> *Sent:* Friday, November 07, 2008 12:12 PM
> *To:* mrosenblum_at_dulcian.com <mailto:mrosenblum_at_dulcian.com>
> *Cc:* oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
> *Subject:* Re: Unicode data migration from SQL Server to
> Oracle
>
> 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
> Server
> connection is via open source SQL Server connector (jTDS)
>
> If you do try it, use version 1.2. The new 1.5 version of
> SQL Developer
> 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.
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>
>
> On Fri, Nov 7, 2008 at 7:39 AM, Michael Rosenblum
> <mrosenblum_at_dulcian.com <mailto:mrosenblum_at_dulcian.com>>
> wrote:
>
> Good morning, all!
>
> Currently we are building a system in Ethiopia -- and
> as a part of the project need to do a data migration.
> So...
>
> 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
> b. NLS_NCHAR_CHARACTERSET=AL16UTF16
> 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
> test_at_db_link
> A Column1
> --------- --------
> 63 ?
> SQL>
>
> Attempted actions:
> 1. OLEDB -- the same problem
> 2. Third party ODBC drivers -- don't even recognize
> NVARCHAR columns
> 3. Played with HS_LANGUAGE and HS_NLS_LANG -- no impact.
>
> Any ideas/suggestions?
>
> Thanks,
> Michael Rosenblum
> Dulcian Inc.
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l