Skip to Content.
Sympa Menu

freetds - Re: [freetds] SQL server default collation

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddyz77 AT tin.it>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SQL server default collation
  • Date: Sat, 26 Feb 2005 09:10:02 +0100

Il giorno gio, 24-02-2005 alle 22:18 -0800, lre75l102 AT sneakemail.com ha
scritto:
> 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.
>

It should be possible changing some system table and restarting server
(you have a backup, don't you?)
In examples below you use protocol version 7.0 so changing a single db
do not change anything (you have to use 8.0 for this feature).

> 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
... omissis ...
>
>
>
> 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).
>

No, this has to do with protocol version 8.0 and characters coding (no
core just slight different charset was used).

You spot a nasty bug.. or perhaps two...
1. we don't use a default charset conversion so it can happen that
server send varchars before charset change and have a call to iconv
(-1, ...) that lead to your floating point exception
2. cp437 is not in our charset tables so FreeTDS is not able to
initialize charset causing call to iconv(-1, ...) like above

from your dump you can see

21:52:18.266098 tds_srv_charset_changed: what is charset "cp437"?

this cause 2...

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

Expect a fix in a day or two...

freddy77






Archive powered by MHonArc 2.6.24.

Top of Page