FreeTDS Development Group

["KJ" <kjatwork AT gmx.net>]

Hi,

we faced the very same problem when transfering data from Sybase to SQL 
Server. Our Sybase server's character set is iso_1 case insensitive, SQL 
Servers are configured Latin1_General_CI_AS.

I'll just figure out how we transfered the data. We used the standard Sybase 
and Microsoft bcp utilities, but the same applies to other clients.

The clue is on sybase side. Because euro symbol is not defined in iso_1 
character set (even though it might be inserted by clients) it is absolutly 
necessary to prevent Sybase Server to do some character set conversion. This 
can be done by specifying the server's character set on client side (e.g. bcp 
-Jiso_1 or just bcp -J). That's the same way as data came in. If another 
character set is specified Sybase will always translate the euro sybmbol to ? 
because the is no other rule for undefined characters.

Once the data is out, the rest is quite straightforward. Copy data in using 
cp1252 character set, which is a super set of iso_1.

KJ



-------- Original-Nachricht --------
> Datum: Wed, 5 Jan 2011 23:14:11 -0500
> Von: "James K. Lowden" <jklowden AT freetds.org>
> An: ananthbv AT gmail.com
> CC: freetds AT lists.ibiblio.org
> Betreff: Re: [freetds] freebcp from Sybase to SQL Server 2008 (euro symbol)

> ananthbv AT gmail.com wrote:
> > Extremely sorry about that. I thought the freetds dump file would
> > contain logs of all commands executed in the session.
> 
> Don't worry about it.  What's your name, though?  
> 
> > Attached is the log for the freebcp out command (Again, including your
> > id in the cc list in case the server blocks the attachment).
> 
> No, not attached. :-(  The mailing list doesn't strip off attachements,
> and I run my own mail server.  Maybe gmail is doing it?  But tsql told us
> what we need to know: 
> 
> > $ tsql -S NJBODSYB2B -U user -P pass
> > locale is "C"
> > locale charset is "ANSI_X3.4-1968"
> > Msg 2401, Level 11, State 2, Server NJBODSYB2B, Line 0
> > Character set conversion is not available between client character set
> > 'iso646' and server character set 'iso_1'.
> > 
> > Msg 2411, Level 10, State 1, Server NJBODSYB2B, Line 0
> > No conversions will be done.
> > 
> > Msg 5704, Level 10, State 2, Server NJBODSYB2B, Line 0
> > Changed client character set setting to '<NULL>'.
> > 
> > 1> quit
> > 
> > $ tsql -S MSSQL_SERVER -Uuser -Ppass
> > locale is "C"
> > locale charset is "ANSI_X3.4-1968"
> > 1> quit
> 
> This actually tells us a lot of what we need to know:
> 
> 1.  You're not configured for any special locale; you're using
> ANSI_X3.4-1968, which, my friend, doesn't say 1968 by accident" that's
> ASCII, all 7 bits of it.  No values over 127, no Euro, no dice.  That's
> why you can't display it on your xterm.  
> 
> 2.  Sybase refuses to translate from its charset to ASCII because it
> can't, reliably.  ASCII is 7 bits and iso_1 (8859-1, same thing) is 8
> bits.  Can't put 10 pounds of flour in a 5-pound sack.  So it passes you
> the data verbatim.  
> 
> 3.  Without looking at the log, I'm quite sure the 0x80 is on your Sybase
> server and is passed to the client unchanged.  That's what "Character set
> conversion is not available" means.  IOW the data on your server is
> technically corrupt: it's defined as 8859-1 but contains values outside
> that domain.    
> 
> > So, to fix this, I should add a cp1252 (or equivalent) to the client
> > charset field in the sql server entry in the conf file, is that
> > correct?
> 
> To *fix* the Sybase side will take a lot more work than that.  
> 
> To just load what Sybase sent you into the Microsoft server:
> 
> 1.  First verify that iconv(1) can convert your data file to UTF-16 or
> UCS-2.  
> 
> 2.  In freetds.conf, set "client charset"  for your Microsoft server to
> (something like) "cp1252".  If you don't get the name exactly right it
> won't work.  "iconv -l" will show the list of available names if you're
> using GNU iconv.  You need to have linked with libiconv when you built
> FreeTDS.  
> 
> 3.  Define the target column as nvarchar *or* CP1252.  The latter is
> tricky on a Windows SQL Server because Microsoft insists on using weird
> terminology.  Look for something about "collation" cf.
> http://msdn.microsoft.com/en-us/library/aa214408(v=sql.80).aspx.  
> 
> 4.  Connect with tsql with TDSDUMP defined and make sure the log shows
> your client character set is cp12532.  
> 
> 5.  Upload.  
> 
> 6.  Profit?  
> 
> That should oughta do it.  
> 
> --jkl
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

-- 
Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de