python – inserting 8k + character strings into azure sql data warehouse using pyodbc

As the title states, im trying to insert an 8k+ string into azure sql data warehouse using pyodbc in a python notebook. I’ve seen a few answers related to changing the driver, and ive updated to latest driver which supposedly supports this, still no luck.

Heres the code i’m using

import pyodbc
connectionString = "Driver={ODBC Driver 18 for SQL Server};Server=myServerInfo;Database=myDB;Uid=user;Pwd={pw};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;LongAsMax=1;"
cnxn = pyodbc.connect(connectionString)

cnxn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
cnxn.setencoding(encoding='utf-8')

cursor = cnxn.cursor()

sql = "insert into myTable (id, veryLongText) values (?, ?)" 
params = (1, 'a' * 8001) 

cursor.execute(sql, params) 

this gives the error

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]104220;Cannot find data type 'text'. (100000) (SQLExecDirectW)")

if i truncate the string to 4k characters, i dont have any issues, but unfort i need all the data as is for the project.

I’ve tried casting the long string to varchar(max) to see if i can force the driver to use varchar(max) instead of text

sql = "insert into myTable (id, veryLongText) values (?, cast(? as varchar(max))" 

which gives the error:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Insert values statement can contain only constant literal values or variable references. (104334) (SQLExecDirectW)')

I’ve also tried using pyodbc.Binary to cast the string into a varchar(max) datatype (chatgpts reommendation lol) but i get this error:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Unsupported data type error. Statement references a data type that is unsupported in Parallel Data Warehouse, or there is an expression that yields an unsupported data type. Modify the statement and re-execute it. (104051) (SQLExecDirectW)')

can anyone shed some light on how i can fix this?