FreeTDS Development Group

[ananthbv AT gmail.com]

Once again, thanks a lot James. Using cp1252 worked for uploading. A
good lesson in charsets for me :)

> Don't worry about it.  What's your name, though?

My name is Ananth. Thanks for asking.


@KJ

I did use the sybase bcp out -J iso_1 and it worked fine (check my
first post). But I had to run this from unix command prompt where
sybase was not installed, only freetds.

On Thu, Jan 6, 2011 at 4:34 AM, KJ <kjatwork AT gmx.net> wrote:
> 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
>