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

[Dylan]

Hi,

I'm having issues with fetching NVARCHAR data from SQL Server 2008
using pyodbc. I'm consistently getting the error:

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)')

I've also posted this over at Stack Overflow:

http://stackoverflow.com/questions/8890427/pyodbc-freetds-unixodbc-on-debian-linux-issues-with-tds-version

Any insight y'all have would be very much appreciated!

Cheers,
Dylan

---------------------------------------

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 a number of relevant
solutions (http://www.seanelavelle.com/2011/07/30/pyodbc-and-freetds-
unicode-ntext-problem-solved/ and http://www.ubuntitis.com/?p=64) 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 =