How To Get Entire Varchar(max) Column With Python Pypyodbc

I have a Python program that connects to an MSSQL database using an ODBC connection. The Python library I'm using is pypyodbc. Here is my setup: Windows 8.1 x64 SQL Server 2014 x6

Solution 1:

Well, I ended up getting the problem resolved. I found this link regarding a similar problem, just not in python, and they found that the problem was with the SQL Server native client driver. They recommended using the SQL Server standard driver instead.

So I changed my driver in my ODBC connection string from SQL Server Native Client 11.0 to SQL Server and it's working perfectly! I'm getting the entire contents of the VARCHAR(MAX) column in my MSSQL data table.

I really hope this proves to be useful for anyone else who encounters this issue! Good luck!

Here is the link: http://www.sqlservercentral.com/Forums/Topic1534163-391-1.aspx

Solution 2:

The "{SQL Server}" doesn't work in my case. "{SQL Server}" works perfectly well if the database is on my local machine. However, if I tried to connect to the remote server, always the error message below would return:

pypyodbc.DatabaseError: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error')

For those who are still struggling in the VARCHAR(MAX) truncation, a brilliant workaround my colleague came out with is to CAST the VARCHAR(MAX) to TEXT type.

Let's say we have a column called note and its data type is VARCHAR(MAX), instead of using SELECT note FROM notebook, writing in SELECT CAST(note AS TEXT) FROM notebook.

Hope it helps!

Solution 3:

I'm assuming you're using FreeTDS, since I've seen this problem before. In your freetds.conf file, likely under [global]:

[global]
text size = 64512

Depending on your version of SQL Server, change this to:

[global]
text size = 4294967295

You'll also need to change it any of your DSNs.

If this isn't the issue, we'll need more info: are you connecting from Linux or Windows? Which version of SQL Server? What driver are you using to connect?