Skip to Content.
Sympa Menu

freetds - [freetds] SQL server default collation

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: lre75l102 AT sneakemail.com
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] SQL server default collation
  • Date: Thu, 24 Feb 2005 22:18:43 -0800

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




Archive powered by MHonArc 2.6.24.

Top of Page