Python with MS SQL - truncated output

Question

I try to connect to MSSQL DB with python from Linux box (Python 2.7, Ubuntu 11.04). Output that I receive is truncated to 500 characters. Please, see script and configs below. How it could be resolved? The problem I suppose in ODBC driver or near it.

The code (pyodbc, pymssql):

conn = pymssql.connect(host='my_remote_host', user='ro_user',
password='password', database='current', as_dict=True)
cur = conn.cursor()
cur.execute(sql)
for i in cur:
    print i
conn.close()

cnxn = pyodbc.connect(driver='FreeTDS', server='my_remote_host', database='current', uid='ro_user', pwd='password')
cursor = cnxn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
...
cnxn.close()

I have no write access to MS SQL DB, it's actually remote server that doesn't belong to our system.

SQL:

sql = '''
        SELECT  Req.ID,
        ShReq.Summary AS [Short Name],
        ShReq.ALM_SharedText AS [Text],
        Req.ContainedBy,
        Req.DocumentID
FROM    CurMKS..ALM_Requirement Req
        JOIN CurMKS..ALM_SharedRequirement ShReq ON Req.[References] = ShReq.ID
        WHERE DocumentID = 1111111'''

The problem is with ShReq.ALM_SharedText field. It's truncated to 255 chars, but using conversions like convert(text,ShReq.ALM_SharedText) AS TEXT and CAST(ShReq.ALM_SharedText AS TEXT) I increase truncating to 500 chars. However there are fields with longer text than 500 chars and they are truncated.

ODBC settings:

/etc/odbc.ini:

[MKS]
#Driver=FreeTDS
Driver=/usr/lib/odbc/libtdsodbc.so
Description=Database
Trace=No
Server=my_remote_host
Port=1433
Database=current
UID=ro_user
PWD=password
TDS Version=8.0

/etc/odbcinst.ini:

[FreeTDS]
Description=FreeTDS
Driver=/usr/lib/odbc/libtdsodbc.so
UsageCount=1

/etc/freetds/freetds.conf:

[global]
        tds version = 8.0
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff
;       timeout = 10
;       connect timeout = 10
;       text size = 2097152


[mksserver]
      host = my_remote_host
      port = 1433
      tds version = 8.0
      client charset = UTF-8

Any thoughts how it could be resolved?

Have you tried increasing the text size parameter as directed? — Bryan
– Bryan, Commented Aug 9, 2012 at 13:07
tried increasing and removing it, I didn't notice any change actually. — DominiCane
– DominiCane, Commented Aug 9, 2012 at 14:47
So you tried increasing the value in the configuration file, but did you set TEXTSIZE in the SQL? — Bryan
– Bryan, Commented Aug 9, 2012 at 15:44
@beargle as described in examples - both pyodbc and pymssql. How to set TEXTSIZE in SQL?? — DominiCane
– DominiCane, Commented Aug 10, 2012 at 7:36

Bryan · Accepted Answer · 2012-08-09 15:50:37Z

5

Change the text size in global section of freetds.conf to the maximum (4294967295 bytes):

[global]
    tds version = 8.0
    text size = 4294967295

Also have to set TEXTSIZE in SQL to maximum (2147483647 bytes):

sql = """
    SET TEXTSIZE 2147483647;
    SELECT  Req.ID,
            ShReq.Summary AS [Short Name],
            ShReq.ALM_SharedText AS [Text],
            Req.ContainedBy,
            Req.DocumentID
    FROM    CurMKS..ALM_Requirement Req
            JOIN CurMKS..ALM_SharedRequirement ShReq ON Req.[References] = ShReq.ID
    WHERE DocumentID = 111111;
      """

answered Aug 9, 2012 at 15:50

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anujs · Accepted Answer · 2013-01-08 13:58:04Z

1

If you are using the older version of pymssql (1.0.2), there are certain limitaions.

varchar and nvarchar data is limited to 255 characters, and longer strings are silently trimmed. This is known limitation of TDS protocol. A workaround is to CAST or CONVERT that row or expression to text data type, which is capable of returning 4000 characters.

source: http://pymssql.sourceforge.net/limitations.php

answered Jan 8, 2013 at 13:58

anujs

5710 bronze badges

Comments

Prasad Nadiger · Accepted Answer · 2021-04-23 14:31:56Z

0

You can use the following logic to get the complete output from SQL Server:

rows = connCursor.execute(spQuery).fetchall()
result = ''
result = result.join([row[0] for row in rows])

Here, spQuery is the complete SQL Query we will be executing for the result.
The result, we are getting back gets trimmed after 2033 characters, so we will be joining the complete output into a single string and performing next operations

answered Apr 23, 2021 at 14:31

Prasad Nadiger

3933 silver badges14 bronze badges

Collectives™ on Stack Overflow

Python with MS SQL - truncated output

SQL:

ODBC settings:

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

SQL:

ODBC settings:

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related