For a while I have been getting the following error whenever I tried to retrieve an instance of a django model which had a field that used a column of type ntext:
ProgrammingError: (’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)’)
Every time I run into this problem I looked for ways around it, and for the most part I was always able to find one which is why I never had to try to find a solution to the actual problem. When I had to do something for which I couldn’t find a way around it, I had to look for a solution and I almost gave up after reading hundreds of pages. Thankfully I was able to find a solution here it is in case you find it useful.
The problem was due to the fact that I was using TDS version 4.2, the default version, when connecting to a MS SQL 2008 DB when I needed to use TDS version 8.0. Go here for a more in depth explanation of how to choose the right TDS version: http://www.freetds.org/userguide/choosingtdsprotocol.htm
In order to find out what TDS version I was using I followed the steps outlined here: http://www.freetds.org/userguide/logging.htm
After I knew for sure I was using the wrong TDS version I updated my vi /etc/freetds/freetds.conf file to include the following line:
tds version = 8.0
But I kept getting the same problem, I then found that I needed to update my odbc.ini file to include the following line when using FREETDS through unixODBC:
tds version = 8.0
But I kep getting the same problem. It was pretty weird to me that the .ini file would accept a property with a space in it but pretty much every document I read had it like that until I run into this document which replaced the space with an underscore, so I changed the odbc line to look like:
tds_version = 8.0
I tried again, and I no longer got an error. The bottom line is that you need to use the TDS version that is appropriate to the MSSQL server you are trying to connect to and that in order to tell unixODBC, which is what pyODBC uses, what TDS version to use, you need to add the line above in your odbc.ini file.
The following is a list of pages that have good info about FREETDS and unixODBC:
- http://kipb7.wordpress.com/2008/06/12/pyodbc-unixodbc-freetds-config/ (This page explains how to test the configuration of FREETDS and unixODBC)