Using freeTDS to connect to SQL Server database working with TSQL but not completely in python

Question

I've successfully setup a connection from a stock raspberry pi 4 to our local database running on windows 10.

In the terminal if I type:

tsql -S *servername* -U *username* -P *password*
select * from testlist_table
go

I get the result for the query and all is good for that test. It's probably worth mentioning I recieve back after the first tsql line:

default encoding is en_us.utf-8 or something odd like this and then: using default encoding utf-8,

When using python code if I type

import pyodbc
cnxn = pyodbc.connect("Driver={freeTDS};Server=*servername*;DATABASE=*databasename*;UID=*userid*;pwd=*pwd*")

cursor = cnxn.cursor()
cursor.execute("Select * from TestList_Table")

I get the following error:

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.

Any ideas? I've tried setting encoding / decoding but that hasn't help. If I select a particular field so far that's worked. Not excited to try and change all the code as I have a lot of columns and am referencing by column number sometimes in a big mess of code.

for reference I followed this guide: tryolabs.com/blog/2012/06/25/… — Adam Schulz
– Adam Schulz, Commented Feb 4, 2020 at 21:38

FlipperPA · Accepted Answer · 2020-02-05 19:05:21Z

1

I'm going to assume you're running a relatively recent version of FreeTDS (version 0.95 or above; you can find your version with tsql -C). If you're using an earlier version and this doesn't work, trying changing the TDS_Version to 7.2 or 7.1.

Give this a try:

import pyodbc
cnxn = pyodbc.connect(
    "Driver={freeTDS};Server=*servername*;DATABASE=*databasename*;"
    "UID=*userid*;pwd=*pwd*;TDS_Version=7.3;CHARSET=UTF-8;"
)

cursor = cnxn.cursor()
cursor.execute("Select * from TestList_Table")

answered Feb 5, 2020 at 19:05

FlipperPA

14.4k5 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adam Schulz Over a year ago

Thank you! I'm using version 4.2. This actually resolved a number of the problems I was seeing. Do you know what these changes to the connection string did? I guess I'm wondering from an academic sense.

FlipperPA Over a year ago

There are two version numbers to consider. There's the FreeTDS version (which will be very likely be something between 0.91 and 1.1.24), and the TDS Version (which will be 4.2, 5.0, or for SQL Server, 7.0 through 7.4). The FreeTDS version is the number of the FreeTDS driver library, whereas the TDS Version is the version of the protocol defined by Microsoft for SQL Server. It is very confusing! By using TDS_Version 7.3, we're saying "use a recent Microsoft supported version", and by passing the CHARSET as an option, we're saying to use UTF-8.

FlipperPA Over a year ago

I've found it is best to be explicit in the connection string with pyodbc rather than mucking about with freetds.conf or odbc.ini; it keeps all the fiddly bits in one place instead of several. Here's the documentation on TDS Version (partially written by yours truly after I figured out this mess myself!): freetds.org/userguide/choosingtdsprotocol.htm

Collectives™ on Stack Overflow

Using freeTDS to connect to SQL Server database working with TSQL but not completely in python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related