Geek Answers Handbook

How to get full VARCHAR (MAX) column with Python pypyodbc

I have a Python program that connects to an MSSQL database using an ODBC connection. I am using pypyodbc python library.

Here is my setup:

  • Windows 8.1 x64
  • SQL Server 2014 x64
  • Python 2.7.9150
  • PyPyODBC 1.3.3
  • ODBC Driver: SQL Server 11.0 Native Client

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

I'm new to pypyodbc and I searched around like crazy and can't find anything on how to prevent this from pypyodbc or even pyodbc. At least not with the search terms that I used, 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 truncated.

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

UPDATE:

So, I tried to translate in my SQL query. When I do CAST(my_column as VARCHAR(MAX)) , it truncates in the same position. However, if I do CAST(my_column as VARCHAR(8000)) , it gives me more typing, but it still truncates some of the content. If I try to do anything larger than 8000 , I get an error message saying that 8000 is the biggest that I can use. Does anyone know what can happen here? It seems strange that using MAX will not work.

+6
source share
2 answers

Well, I ended up solving the problem. I found this link regarding a similar problem, just not in python, and they found that the problem was related to the SQL Server native client driver. Instead, they recommended using the standard SQL Server driver.

So, I changed my driver in the ODBC connection string from SQL Server Native Client 11.0 to SQL Server , and it works great! I get all the contents of the VARCHAR (MAX) column in my MSSQL data table.

I really hope that it will be useful to everyone who is faced with this problem! Good luck

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

+7
source

I assume you are using FreeTDS since I have seen this problem before. In your freetds.conf file, probably in [global]:

 [global] text size = 64512

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

 [global] text size = 4294967295

You will also need to change it to any of your DSNs.

If this is not a problem, we will need additional information: are you connecting from Linux or Windows? What version of SQL Server? What driver do you use to connect?

+2
source

More articles:


All Articles