[freetds] SQL server default collation

lre75l102 at sneakemail.com lre75l102 at sneakemail.com
Fri Feb 25 01:18:43 EST 2005


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


-------------- next part --------------
A non-text attachment was scrubbed...
Name: dump.log.gz
Type: application/x-tar
Size: 3503 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20050224/90a21eaa/attachment.tar 


More information about the FreeTDS mailing list