While working on a script to find blocking processes on a SQL Server 2008 database, I ran across this error when I tried to execute a query:
$ python find_blocking_processes.py
Traceback (most recent call last):
File "find_blocking_processes.py", line 77, in <module>
find_blocking_processes(brokerConn)
File "find_blocking_processes.py", line 67, in find_blocking_processes
) x""").fetchall()
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)')
After a bit of digging, it appears that you need to tell the ODBC driver which protocol to talk to a server in. Rectifying this was pretty straight forward:
In the file /etc/freetds.conf I added a line to the serverconfig stating to use the version 8.0 protocol:
# A typical Microsoft SQL Server 2008 configuration [DEVDATABASE] host = 10.10.10.100 port = 1433 tds version = 8.0
Then in the file /etc/odbc.ini I added a line for the version as well:
[DEVDATABASE] Driver = /usr/lib64/libtdsodbc.so.0 Server = 10.10.10.100 Port = 1433 Trace = Yes TraceFile = /tmp/freetdssql-foobar.log tds_version = 8.0
* Note the ‘_’ in the tds_version variable in the odbc.ini
After this, the database call ran like a dream.
These were the links that pointed me in the right direction to solve this issue and contain a little more information on the causes of this:
- FreeTDS Error while trying to retrieve information from an ntext field – http://www.ubuntitis.com/?p=64
- PyODBC, UnixODBC, FreeTDS – config – http://kipb7.wordpress.com/2008/06/12/pyodbc-unixodbc-freetds-config/