FreeTDS Development Group

[lre75l102 AT sneakemail.com]

Hi all,

recently, we have been running into a very frustrating problem:

Since we upgraded from an earlier version of FreeTDS to 0.63 we have
been unable to read varchar and char columns from some of our MS-SQL
servers using TDS version 7.0 (or 8.0). Doing so results in a
floating point exception. Using version 4.2 has been our workaround
until now but that has stopped being an acceptable solution for
other reasons.

We have been puzzled for the longest time as to why some SQL servers
let us select varchar's as one would expect while others do not. I
should note that all of our servers are either SQL 2K or 7.0.

After countless hours of investigation, we believe we finally
narrowed down the difference in behavior to the code page that is
mentioned as the server's default collation. Those servers "that work"
use the standard SQL_Latin1_General_CP1_CI_AS while the bad ones
use some version of CP437.

It seems that CP437 used to be the default collation when installing
older versions of MS-SQL (6.0, 6.5), and SQL servers that have been
upgraded from 6.5 or 6.0 retain that CP437 collation setting.
While we have tried changing the collation on a database level it
does not make the problem go away as long as the server default is
still CP437. Apparently the only way to change the default server
collation is during SQL Server setup or by rebuilding the master
db which means it's not really an option.

Here are some test cases I have prepared:

8:30PM cougar_98: ./tsql -C
Compile-time settings (established with the "configure" script):
                          Version: freetds v0.63RC10
        MS db-lib source compatibility: no
           Sybase binary compatibility: unknown
                         Thread safety: yes
                         iconv library: yes
                           TDS version: 8.0
                                 iODBC: no
                              unixodbc: yes

1. The case that's causing us trouble (I'm also attaching the
  TDSDUMP):

  We're connecting to a SQL server 2000 (or 7.0) that uses CP437
  as default collation via TDS version 7.0 or 8.0.

###################################################################
8:30PM cougar_99: TDSVER=7.0 ./tsql -H tsunt4 -p 1433 -U sa
locale is "C"
locale charset is "ANSI_X3.4-1968"
Password:
1> use pubs
2> SELECT serverproperty('collation')
3> go
     
SQL_Latin1_General_CP437_CI_AS 
1> SELECT TOP 3 job_lvl FROM employee
2> go
job_lvl    <======  reading columns other than (var)char works fine
35     
89     
140    
1> SELECT TOP 3 emp_id, job_lvl FROM employee
2> go
zsh: 22388 floating point exception  TDSVER=7.0 ./tsql -H tsunt4 -p 1433 
-U sa
###################################################################



2. Reference case showing reading (var)char's from the same
  SQL server works as expected when using TDS version 4.2:

###################################################################
8:31PM cougar_100: TDSVER=4.2 ./tsql -H tsunt4 -p 1433 -U sa
locale is "C"
locale charset is "ANSI_X3.4-1968"
Password:
1> use pubs
2> SELECT serverproperty('collation')
3> go
     
SQL_Latin1_General_CP437_CI_AS 
1> SELECT TOP 3 job_lvl FROM employee
2> go
job_lvl
35     
89     
140    
1> SELECT TOP 3 emp_id, job_lvl FROM employee
2> go
emp_id  job_lvl
PMA42628M       35     
PSA89086M       89     
VPA30890F       140    
1>
###################################################################



3. Reference case showing reading (var)char's from a SQL server
  with a CP1 default collation works as well (TDS version does
  not matter).

###################################################################
8:31PM cougar_101: TDSVER=7.0 ./tsql -H sloth -p 1433 -U sa
locale is "C"
locale charset is "ANSI_X3.4-1968"
Password:
1> use pubs
2> SELECT serverproperty('collation')
3> go

SQL_Latin1_General_CP1_CI_AS   
1> SELECT TOP 3 job_lvl FROM employee
2> go
job_lvl
35     
89     
140    
1> SELECT TOP 3 emp_id, job_lvl FROM employee
2> go
emp_id  job_lvl
PMA42628M       35     
PSA89086M       89     
VPA30890F       140    
1>
###################################################################



I also skimmed over the ChangeLog. Any chance the Sep 15, 2004 change
could have introduced this problem? As I mentioned, it used to work
with earlier versions of FreeTDS.

Thu Sep 15 13:41:10 CET 2004    Frediano Ziglio <freddy77 AT angelfire.com>
       * include/tds.h src/tds/iconv.c src/tds/token.c:
       - fix for some sql collation with a codepage not corresponding
         with lcid (like SQL_Scandinavian_CP850_CI_AS).

I am really hoping that you'll be able to shed some light on this
and provide an explanation or fix of some sort.


Thanks much,

Tino Wuensche

Attachment: dump.log.gz
Description: Unix tar archive