FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

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