sql server - How to get entire VARCHAR(MAX) column with Python pypyodbc

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

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