SQL Server Performance

DTS Oracle 10 g to SQL 2000 collation ?

Discussion in 'SQL Server DTS-Related Questions' started by acki4711, Feb 15, 2006.

  1. acki4711 Member

    Hi folks,
    Want to copy data from a oracle 10 g db into a sql server 2000 db.
    oracle uses collation WE8MSWIN1252 (case sensitiv) my sql db uses SQL_Latin1_General_CP1_CI_AS.
    Problem now is that the german umlaute äöü (two points on top) are not transfered ok.
    (a-umlaut becomes a)

    Any help is highly appreciated.

    TIA
    Dan
  2. Tahsin New Member

  3. acki4711 Member

    Thasin,
    Result is the same a-umlaut becomes a.

    :-(
  4. Adriaan New Member

    You can Google this stuff ...

    WE8MSWIN1252 seems to be an 8-bit character set, also referred to as UTF-8.

    There are references to mapping the data to NCHAR - perhaps you could try something like
    CAST(CAST(col AS NCHAR(8000)) AS VARCHAR(8000))
  5. FrankKalis Moderator

  6. Adriaan New Member

    quote:Originally posted by FrankKalis

    Wouldn't that be NCHAR(4000) instead of NCHAR(8000)?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs

    Oops, just shows you how many times I work with Unicode data.
  7. FrankKalis Moderator

    ...just like me. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Not sure if this helps, but have a look here:<br /<a target="_blank" href=http://www.microsoft.com/downloads/details.aspx?FamilyID=B4E57B5E-2CFC-49AE-A184-5705ACF8591F&displaylang=en>http://www.microsoft.com/downloads/details.aspx?FamilyID=B4E57B5E-2CFC-49AE-A184-5705ACF8591F&displaylang=en</a><br /<a target="_blank" href=http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx>http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx</a><br /<a target="_blank" href=http://www.microsoft.com/downloads/details.aspx?FamilyID=E35CEE88-C919-463F-B020-81468CD231DA&displaylang=en>http://www.microsoft.com/downloads/details.aspx?FamilyID=E35CEE88-C919-463F-B020-81468CD231DA&displaylang=en</a><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  8. Tahsin New Member

    Adriaan/Frank, you guys probably know more about this than I do, but why wouldn't changing the collation compatible to a European character set or unicode work? Frank, what collation are you using on your server in Germany? I am sure you run across umlauts a lot (no rhyme intended!) [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]
  9. FrankKalis Moderator

    Well, yes I obviously do. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />On my testbox I use<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT SERVERPROPERTY('Collation')<br /> <br />--------------------- <br />Latin1_General_CI_AS<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />On most production boxes we have<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT SERVERPROPERTY('Collation')<br /> <br />----------------------------- <br />SQL_Latin1_General_CP1_CI_AS<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />So the same as Dan uses. Both collations should be capable of representing ä, ö, ü and ß. So I would suspect the transfer process to cause trouble, but I'm not into DTS. So I can't tell what's right and wrong there.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  10. acki4711 Member

    Hi all,
    Thanks for all of your replies.
    Here are two solutions that both (just one is needet) worked for me.

    - insert additional parameter in tnsnames.ora
    ( CHARSET = 8859-1 )
    - added the environment variable
    NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252
    (Don't forget to logoff logon after adding the evironment variable)

    Cheers
    Dan

  11. FrankKalis Moderator

    Thanks for feedback. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />

Share This Page