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?
Change the
text size
inglobal
section offreetds.conf
to the maximum (4294967295 bytes):Also have to set
TEXTSIZE
in SQL to maximum (2147483647 bytes):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