(pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find data type 'TEXT'.

Sasanapuri, Yogesh 1 Reputation point
2021-10-21T06:31:53.983+00:00

While trying to push the data to Database using python in Azure Notebooks, I am getting the error

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,489 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. YufeiShao-msft 7,046 Reputation points
    2021-10-21T08:11:13.753+00:00

    Hi @Sasanapuri, Yogesh ,

    ntext, text, and image data types will be removed in a future version of SQL Server. please avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

    The official documentation does not mention data type 'TEXT', and in addition to the listed, other data types are not supported:
    https://learn.microsoft.com/en-us/sql/machine-learning/python/python-libraries-and-data-types?view=sql-server-ver15

    like: replace

     dbo.test SET desc = ?   
    

    with

    dbo.test SET desc = CAST(? AS VARCHAR(MAX))  
    

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Erland Sommarskog 100.1K Reputation points MVP
    2021-10-22T22:08:15.69+00:00

    It seems that the Completion.to_sql generates a CREATE TABLE statement with the TEXT data type, and you are connecting to something which does not support this data type. That does not seem to be the Azure SQL Database where you originally created the table. What are you connecting to?

    As for why the to_sql method uses TEXT and not nvarchar(MAX) I have no idea, but I do find it a bit embarrassing. However, the answer goes a little beyond the tag sql-server-general.

    I should add that I have no experience with Azure Notebooks myself, so I cannot really test this myself.