FreeTDS Error while trying to retrieve information from an ntext field

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′, ‘[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:

  1. http://www.freetds.org/userguide/choosingtdsprotocol.htm
  2. http://www.freetds.org/userguide/logging.htm
  3. http://lucasmanual.com/mywiki/unixODBC
  4. http://kipb7.wordpress.com/2008/06/12/pyodbc-unixodbc-freetds-config/ (This page explains how to test the configuration of FREETDS and unixODBC)
  5. http://www.freetds.org/userguide/freetdsconf.htm#TAB.FREETDS.CONF.DEBUGFLAGS

7 Responses to “FreeTDS Error while trying to retrieve information from an ntext field”

  1. Hugo Freire says:

    Thanks a lot!!!! It worked for me XD

  2. chris says:

    Thanks so much for this! I’m truly amazed that ALL the documentation I read never included the underscore! Solved my problems immediately.

    Big ups!

  3. Paul says:

    I had the same experience. I kept trying to unsuccessfully fix the problem, so instead I would create new work arounds. Today I decided to tackle this problem again and I cannot believe that underscore was my problem from the beginning! Thanks so much for taking the time to post this.

  4. [...] FreeTDS Error while trying to retrieve information from an ntext field – http://www.ubuntitis.com/?p=64 [...]

  5. Ricardo says:

    Thanks so much for this! I´m using version 7.0!

  6. Stuart says:

    At last the CORRECT solution. Thanks :)

  7. jack c says:

    Thanks, didn’t realise it was needed in the freetds.conf AND the odbc.ini, AND they are different (“tds version” vs “tds_version”)!

    Thanks

Leave a Reply