I use freetds(v0.95.19)/unixODBC(2.3.1)/pyodbc(3.0.7) to access MS SQL server 2012 from ubuntu 12.04. I want to retrieve the value of a varchar (not nvarchar), which contains non-ASCII characters.
If my odbc.ini file is like
[mydb]
Driver = FreeTDS
Description = mydb description
Trace = No
Server = mydb.xxxxx.xxx
Database = XXX
Port = 1433
Charset = UTF-8
TDS_Version = 4.2
Then the retrieved value is '\x841\x84\xc1\xcd\xc5;\x9b*\xf5\xb5\xc1}|\xcbX', which is the expected value.
However, if I just change TDS_Version to 7.1, the retrieved value becomes '?1?\xc1\xcd\xc5;?*\xf5\xb5\xc1}|\xcb', which replaces characters '\x84' and '\x9b' with '?' and is missing the 'X' at the end of the string.
I have tried everything I could think of (including updating freetds, unixODBC, and pyodbc to the latest stable versions) but nothing so far seems to help. Using TDS version 4.2 has some other issues so I really hope to make version 7.1 work, but I cannot figure out why the retrieved varchar values are not correct.
BTW, my freetds.conf file has the following content (whether tds version is 7.1 or not in this file does not seem to affect the results I get with pyodbc).
[global]
tds version=4.2
[mydb]
host = mydb.xxxxx.xxx
tds version = 7.1
port = 1433
client charset = UTF-8
In python (2.7.3) I do
import pyodbc
conn=pyodbc.connect('DSN=mydb;DATABASE=XXX;UID=XXX;PWD=XXX')
cursor=conn.cursor()
cursor.execute("select myVarcharColumn from my.table where id=XXX").fetchone()
Replacing Charset = UTF-8 with ClientCharset = WINDOWS-1252 in odbc.ini (and then run odbcinst -i -s -f /usr/local/etc/odbc.ini) made things work for TDS version 7.1 for me.
Apparently having Charset = UTF-8 in the odbc.ini file does not work for setting the encoding to UTF-8 (see http://www.freetds.org/userguide/odbcconnattr.htm for more information). In fact the default encoding of ISO_8859-1 was used with my previous settings. This works for TDS version 4.2, but not version 7.1, presumably due to TDS transferring data differently since version 7.0 (UCS-2).