Text field ouptut is truncated to 500 chr

[GoogleCodeExporter]

using pymssql 2.0.0 with python 2.7, Ubuntu 11.04:
pymssql-2.0.0b1_dev_20111018-py2.7-linux-x86_64.egg 

import pymssql
print pymssql.__version__
$ 2.0.0

Output of long text fields truncated to maximum 500 characters.

What steps will reproduce the problem?
1. Connect to MS SQL
2. Select query

Example code:

doc_id = 1775286
conn = pymssql.connect(host='remote_server_IP', user='user', password='pass', 
database='current', as_dict=True)
cur = conn.cursor()
cur.execute('''
        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 = %s''' % str(doc_id))
for i in cur:
    print i, len(i[2])
conn.close()

When output is not full, we can see len = 500.
The problem is with ShReq.ALM_SharedText field. Used "AS TEXT" - doesn't help. 
Originally it was "AS [Text]". Any solution or workaround?
Thanks in advance!

Original issue reported on code.google.com by eina...@gmail.com on 7 Aug 2012 at 3:41

[GoogleCodeExporter]

Could this be related to "SET TEXTSIZE"? One of the MSSQL session options 
limits the number of characters returned from BLOB fields. Try adding a "SET 
TEXTSIZE=16536;" statement or some other reasonable size before your query 
execution.

Note that "AS TEXT" or variants thereof just changes the name of the column to 
text: it does not affect the type of the column in any way. Try using 
"CAST(ShReq.ALM_SharedText AS TEXT) AS SharedText" or 
"CAST(ShReq.ALM_SharedText AS VARCHAR(MAX)) AS SharedText" to actually convert 
the type.

I take it that CurMKS..ALM_SharedRequirement.ShReq is some kind of SQLCLR type 
with properties? What type is 
CurMKS..ALM_SharedRequirement.ShReq.ALM_SharedText defined as?

What version of MSSQL are you connecting to?

See also:
http://msdn.microsoft.com/en-us/library/ms186238.aspx
http://freetds.schemamania.org/userguide/troubleshooting.htm

Original comment by csmith32@gmail.com on 17 Jun 2013 at 12:35

[GoogleCodeExporter]

I can't reproduce this with a simple script that inserts 8000 bytes into a 
VARCHAR(8000) field and reads them back out.

vagrant@lucid64:~/dev/git-repos/pymssql$ cat test_long_text.py from os import getenv import pymssql

server = getenv("PYMSSQL_TEST_SERVER") user = getenv("PYMSSQL_TEST_USERNAME") password = getenv("PYMSSQL_TEST_PASSWORD") database = getenv("PYMSSQL_TEST_DATABASE")

field_size = 8000 text_length = 8000

conn = pymssql.connect( server=server, user=user, password=password, database=database, ) cursor = conn.cursor(as_dict=True)

try: cursor.execute(""" CREATE TABLE text_test ( stuff VARCHAR(%d) ); INSERT INTO text_test (stuff) VALUES (%s); """, (field_size, "x" * text_length,)) cursor.execute(""" SELECT * FROM text_test """)

row = cursor.fetchone()
# print("row = %r" % (row,))
print("len = %d" % len(row['stuff']))
assert len(row['stuff']) == text_length

except Exception as exc: print(exc) finally: cursor.execute("DROP TABLE text_test")

vagrant@lucid64:~/dev/git-repos/pymssql$ python test_long_text.py len = 8000

I'm closing this.

Original comment by msabr...@gmail.com on 25 Sep 2013 at 1:23

• Changed state: Invalid

[GoogleCodeExporter]

Just to add some input, I'm fairly certain this has to do with the tds version 
being used. The default in FreeTDS is 4.2 (so this means the user is using an 
older version of pymssql). Using your script and modifying pymssql to allow a 
tds_version to be passed in I get:

$ python test_long_text.py 
len = 255

Original comment by dam...@gmail.com on 25 Sep 2013 at 5:50