Cannot find data type 'ntext' error in Azure SQL Data Warehouse

Question

I am running a UPDATE statement against Azure SQL Data Warehouse using pyodbc:

cursor.execute(
  "UPDATE dbo.test SET desc = ? WHERE id = ?", desc, id
)

This works fine when desc value is simple. But when the value of desc is more complex (longer text), executing the above spits out the following error:

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

I thought escaping will solve any issues but it didn't. Any idea/insight?

Answer 1

The new parameter - 'LongAsMax' in connection string that was added for ODBC Driver 18, helped me to solve similar issue. It works for me even without using cursor.setinputsizes:

connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={HOST};DATABASE={DB};UID={UID};PWD={PWD};Authentication=ActiveDirectoryPassword;LongAsMax=yes;'

Answer 2

Could you try this:

cursor.execute(
  "UPDATE dbo.test SET desc = CAST(? AS NVARCHAR(MAX)) WHERE id = ?", desc, id
)

I guess the pyodbc is somehow converting the large string to ntext.

If this doesn't help, check the following github link - it might help you as it is explain that if:

you're using an old SQL Server (WDAC) driver which treats nvarchar as ntext and that's why you're experiencing the issue because there's no reference to ntext type inside django-pyodbc-azure