pyodbc/FreeTDS/unixODBC on Debian Linux: issues with TDS Version

Question

I'm having a bit of trouble successfully using pyodbc on Debian Lenny (5.0.7). Specifically, I appear to be having trouble fetching NVARCHAR values (not a SQL Server expert, so go easy on me :) ).

Most traditional queries work OK. For instance, a count of rows in table1 yields

cursor.execute("SELECT count(id) from table1")
<pyodbc.Cursor object at 0xb7b9b170>
>>> cursor.fetchall()
[(27, )]

As does a full dump of ids

>>> cursor.execute("SELECT id FROM table1")
<pyodbc.Cursor object at 0xb7b9b170>
>>> cursor.fetchall()
[(0.0, ), (3.0, ), (4.0, ), (5.0, ), (6.0, ), (7.0, ), (8.0, ), (11.0, ), (12.0, ), (18.0, ), (19.0, ), (20.0, ), (21.0, ), (22.0, ), (23.0, ), (24.0, ), (25.0, ), (26.0, ), (27.0, ), (28.0, ), (29.0, ), (32.0, ), (33.0, ), (34.0, ), (35.0, ), (36.0, ), (37.0, )]

But a dump of names (again, of type NVARCHAR) does not

>>> cursor.execute("SELECT name FROM table1")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (4004) (SQLExecDirectW)')

... the critical error being

pyodbc.ProgrammingError: ('42000', '[42000] [FreeTDS][SQL Server]Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (4004) (SQLExecDirectW)')

This is consistent across tables.

I've tried a variety of different versions of each, but now I'm running unixODBC 2.2.11 (from lenny repos), FreeTDS 0.91 (built from source, with ./configure --enable-msdblib --with-tdsver=8.0), and pyodbc 3.0.3 (built from source).

With a similar combination (unixODBC 2.3.0, FreeTDS 0.91, pyodbc 3.0.3), the same code works on Mac OS X 10.7.2.

I've searched high and low, investigating the solutions presented here and here and recompiling different versions of unixODBC and FreeTDS, but still no dice. Relevant configuration files provided below:

user@host:~$ cat /usr/local/etc/freetds.conf
#$Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same 
# name is found in the installation directory.  
#
# For information about the layout of this file and its settings, 
# see the freetds.conf manpage "man freetds.conf".  

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
        tds version = 8.0
        client charset = UTF-8

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.  
        # Try setting 'text size' to a more reasonable limit 
        text size = 64512

# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft server
[egServer70]
        host = ntmachine.domain.com
        port = 1433
        tds version = 8.0

[foo]
        host = foo.bar.com
        port = 1433
        tds version = 8.0

user@host:~$ cat /etc/odbc.ini 
[foo]
Description     = Foo
Driver          = foobar
Trace           = No
Database        = db
Server          = foo.bar.com
Port            = 1433
TDS_Version     = 8.0
user@host:~$ cat /etc/odbcinst.ini 
[foobar]
Description     = Description
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =

Any advice or direction would be very much appreciated!

Adventurer

I'm getting same thing. Works with FreeTDS/unixODBC on mac and FreeBSD, but no go on CentOS – Tonsillectomy

Answer 1

I encountered the same error with Ubuntu. I "solved" it with a work around. All you need to do is to set the environment variable TDSVER.

import os
os.environ['TDSVER'] = '8.0'

As I said it is not a real "solution" but it works.

Trickle

Is that a typo, or is it really TDSVER ? I'm guessing it's TDSVER. – Activity

@Anoyz: It works for us exactly that way. But maybe it is another environment variable when using a different operating system. We are running ubuntu server. – Trickle

Answer 2

Try to add

TDS_Version=8.0;ClientCharset=UTF-8

in your connection string.

For example,

DRIVER=FreeTDS;SERVER=myserver;DATABASE=mydatebase;UID=me;PWD=pwd;TDS_Version=8.0;ClientCharset=UTF-8

Trencherman

Thanks, this is the only solution that helped me – Tipton

Version 8.0 is still handled by FreeTDS only for compatibility. Please see: freetds.org/userguide/choosingtdsprotocol.htm I used your approach with 7.4 and it works just fine. – Dolorous

Answer 3

Cant you just side step the issue and either Convert or Cast name to something it can handle?

cursor.execute("SELECT CAST(name AS TEXT) FROM table")

Sand

Pragmatically, yes, and that's what I've done. But this system supports (and will be dealing heavily) with Unicode data in the near future. Therefore, I'd prefer to solve the root of the problem as opposed to CASTing indefinitely – Adventurer