SQL Server Performance Forum – Threads Archive

DTS Oracle 10 g to SQL 2000 collation ?

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

Use this command in Query Analyzer to give you a list of collation types in SQL Server SELECT *
FROM ::fn_helpcollations() Try this:
SQL_Latin1_General_CP1250_CI_AS Refer to this article as well:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp Let me know if it works.

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

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

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

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.

…just like me. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Not sure if this helps, but have a look here: http://www.microsoft.com/downloads/details.aspx?FamilyID=B4E57B5E-2CFC-49AE-A184-5705ACF8591F&displaylang=en</a> http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx</a> http://www.microsoft.com/downloads/details.aspx?FamilyID=E35CEE88-C919-463F-B020-81468CD231DA&displaylang=en</a> – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

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=’

‘ />]

Well, yes I obviously do. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] On my testbox I use <pre id="code"> SELECT SERVERPROPERTY(‘Collation’) ——————— Latin1_General_CI_AS (1 row(s) affected) </pre id="code"> On most production boxes we have <pre id="code"> SELECT SERVERPROPERTY(‘Collation’) —————————– SQL_Latin1_General_CP1_CI_AS (1 row(s) affected) </pre id="code"> 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. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

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

Thanks for feedback. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

]]>