FAQ
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.


Source - SQL Server 2000 Database with a number of tables with NVARCHAR
datatype
Stored data is in Ge'ez language - it does mean that there is no
standard locale.
SQL Server internally is using UCS-2 to store Unicode data
(compatible with UTF16)
Target - Oracle 10g (10.2.0.4) Enterprise edition on Windows 2003 Server
32-bit
NLS_CHARACTERSET = AL32UTF8

NLS_NCHAR_CHARACTERSET=AL16UTF16

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.

Search Discussions

  • Jared Still at Nov 7, 2008 at 5:12 pm
    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 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) 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
  • Michael Rosenblum at Nov 7, 2008 at 6:05 pm
    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
    Sent: Friday, November 07, 2008 12:12 PM
    To: mrosenblum_at_dulcian.com
    Cc: 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
    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.


    Source - SQL Server 2000 Database with a number of tables with NVARCHAR
    datatype
    Stored data is in Ge'ez language - it does mean that there is no
    standard locale.
    SQL Server internally is using UCS-2 to store Unicode data
    (compatible with UTF16)
    Target - Oracle 10g (10.2.0.4) Enterprise edition on Windows 2003 Server
    32-bit
    NLS_CHARACTERSET = AL32UTF8

    NLS_NCHAR_CHARACTERSET=AL16UTF16

    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
  • Goulet, Richard at Nov 7, 2008 at 6:11 pm
    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
    On Behalf Of Michael Rosenblum
    Sent: Friday, November 07, 2008 1:06 PM
    To: 'Jared Still'
    Cc: 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
    Sent: Friday, November 07, 2008 12:12 PM
    To: mrosenblum_at_dulcian.com
    Cc: 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

    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...


    Source - SQL Server 2000 Database with a number of
    tables with NVARCHAR datatype
    Stored data is in Ge'ez language - it does mean
    that there is no standard locale.
    SQL Server internally is using UCS-2 to store
    Unicode data (compatible with UTF16)
    Target - Oracle 10g (10.2.0.4) Enterprise edition on
    Windows 2003 Server 32-bit
    NLS_CHARACTERSET = AL32UTF8

    NLS_NCHAR_CHARACTERSET=AL16UTF16

    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
  • Jan Benjamins at Nov 7, 2008 at 9:04 pm
    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
    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@freelists.org] *On Behalf Of *Michael Rosenblum
    *Sent:* Friday, November 07, 2008 1:06 PM
    *To:* 'Jared Still'
    *Cc:* 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
    *Sent:* Friday, November 07, 2008 12:12 PM
    *To:* mrosenblum_at_dulcian.com
    *Cc:* 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 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) 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
  • Michael Rosenblum at Nov 7, 2008 at 9:17 pm
    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
    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
    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
    On Behalf Of Michael Rosenblum
    Sent: Friday, November 07, 2008 1:06 PM
    To: 'Jared Still'

    Cc: 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
    Sent: Friday, November 07, 2008 12:12 PM
    To: mrosenblum_at_dulcian.com
    Cc: 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
    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.


    Source - SQL Server 2000 Database with a number of tables with NVARCHAR
    datatype
    Stored data is in Ge'ez language - it does mean that there is no
    standard locale.
    SQL Server internally is using UCS-2 to store Unicode data
    (compatible with UTF16)
    Target - Oracle 10g (10.2.0.4) Enterprise edition on Windows 2003 Server
    32-bit
    NLS_CHARACTERSET = AL32UTF8

    NLS_NCHAR_CHARACTERSET=AL16UTF16

    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
  • Yechiel Adar at Nov 12, 2008 at 12:49 pm
    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
    *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
    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
    ] *On Behalf Of *Michael
    Rosenblum
    *Sent:* Friday, November 07, 2008 1:06 PM
    *To:* 'Jared Still'

    *Cc:* 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
    ]
    *Sent:* Friday, November 07, 2008 12:12 PM
    *To:* mrosenblum_at_dulcian.com
    *Cc:* 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
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 7, '08 at 3:39p
activeNov 12, '08 at 12:49p
posts7
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase