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

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