How to get entire VARCHAR(MAX) column with Python

Question

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 x64
• Python 2.7.9150
• PyPyODBC 1.3.3
• ODBC Driver: SQL Server Native Client 11.0

The problem I'm having is that when I query a table with a varchar(max) column, the content is being truncated.

I'm new to pypyodbc and I've been searching around like crazy and can't find anything on how to prevent this from happening in pypyodbc or even pyodbc. At least not with the search terms I've been using and I don't know what other phrases to try.

I even tried adding SET TEXTSIZE 2147483647; to my SQL query, but the data is still being truncated.

How do I prevent this from happening? Or can you point me in the right direction, please?

UPDATE:

So, I tried performing a cast in my SQL query. When I do CAST(my_column as VARCHAR(MAX)) it truncates at the same position. However, if I do CAST(my_column as VARCHAR(8000)) it gives me a larger set of the text, but it's still truncating some of the contents. If I try to do anything larger than 8000 I get an error saying that 8000 is the largest I can use. Anyone know what might be going on here? It seem strange that using MAX won't work.

Answer 1

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?

Answer 2

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

Answer 3

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!