8

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.

CM-Dev
  • 373
  • 1
  • 5
  • 15
  • 1
    I was able to recreate your issue using pypyodbc: my 9386 characters of text from my VARCHAR(MAX) column was truncated to 2047 characters. The exact same Python code returned the complete string when I used pyodbc instead of pypyodbc. – Gord Thompson Nov 24 '15 at 00:46
  • @GordThompson Thanks for your comment. I recently attempted to use pyodbc instead of pypyodbc and for some reason I couldn't get it to return my data set completely. It's possible that issue could have been caused by something unrelated, but I found working solution (I just posted it as an answer) shortly after trying pyodbc and I ended up scrapping the pyodbc idea. If I get some time, I will try to fully implement pyodbc as a test. That way I can post my findings for others who may encounter this problem; in the event that my other solution does not work for them. Thanks again! – CM-Dev Nov 24 '15 at 01:34

3 Answers3

12

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

CM-Dev
  • 373
  • 1
  • 5
  • 15
9

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!

Castiel Wong
  • 119
  • 2
  • 3
2

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?

FlipperPA
  • 13,607
  • 4
  • 39
  • 71
  • I don't believe that's the issue, isn't that only applicable if i'm on Linux? I'm connecting from a Windows machine. It's SQL Server 2014. This is the driver I'm using for my ODBC connection: `SQL Server Native Client 11.0`. – CM-Dev Nov 23 '15 at 19:24
  • Is there a similar way for me to change the text size attribute for the pypyodbc connector? – CM-Dev Nov 23 '15 at 19:29
  • Ah, it doesn't apply in that case. You hadn't stated you were on Windows, which is why I put the "I'm assuming you're using FreeTDS", because I had seen this issue crop up before. Could you amend your question to include: that you're on Windows (and version, and 32 vs 64), your SQL Server version (and 32 vs 64), Python version (and 32 vs 64), pypyodbc version, and the driver? That information will definitely help. – FlipperPA Nov 23 '15 at 20:00
  • I completely understand. Yes, I will definitely add that to my question. While i'm doing that, are you aware of a Windows solution that I can try? Unfortunately, I haven't found one out there yet. – CM-Dev Nov 23 '15 at 20:04