question

SasanapuriYogesh-5842 avatar image
0 Votes"
SasanapuriYogesh-5842 asked ErlandSommarskog commented

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

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @SasanapuriYogesh-5842,

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://docs.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.







· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @yufeishao-msft,

The datatypes are defined as per attached image where no text data type is not defined in the table. But still I am getting error when I insert dataframe to table in database.

142349-a941b9a3-742d-45ae-8e1b-ef239f3c768f.png



Attached is the error log below:
ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find data type 'TEXT'. (104220) (SQLExecDirectW)")
[SQL:
CREATE TABLE [IRM.DataQualityResult] (
[ID] BIGINT NULL,
[DLName] TEXT NULL,
[ApplicationName] TEXT NULL,
[TableName] TEXT NULL,
[FieldName] TEXT NULL,
[DataCompletionPercentage] FLOAT(53) NULL,
[LoadRunDate] DATETIME NULL
)

]
(Background on this error at: http://sqlalche.me/e/f405)

0 Votes 0 ·

The error message makes it clear that you are using the TEXT datatype. Where it origins from, I have no idea, since I don't see your Python script.

The script above runs on my instance, but what are you running this script against?

0 Votes 0 ·

Hi @ErlandSommarskog,
I created table in Azure SQL Database as below in which no 'Text' datatype is defined :

 CREATE Table [IRM].[DataQualityResult]
 (
     [ID] [int] NULL,
     [DLName] [varchar](4000),
     [ApplicationName][varchar](4000),
     [TableName][varchar](4000),
     [FieldName][varchar](4000),
     [DataCompletionPercentage][decimal](5,2),
     [LoadRunDate][date]
 )

In my python script, I created a dataframe as per below script in Azure notebooks where all datatypes are mentioned:

 Completion=pd.DataFrame(data=series)
 Completion.reset_index(inplace=True)
 Completion.rename(columns={'index': 'FieldName', 0: 'DataCompletionPercentage'}, inplace=True)
 Completion['DLName']='CDL'
 Completion['ApplicationName']='Sales'
 Completion['TableName']=i
 Completion['LoadRunDate']=date.today()
 Completion['LoadRunDate']=pd.to_datetime(Completion['LoadRunDate'])
 Completion['ID'] = Completion.index
 Completion['DataCompletionPercentage']=Completion['DataCompletionPercentage'].round(2)
 Completion=Completion[['ID','DLName', 'ApplicationName', 'TableName','FieldName','DataCompletionPercentage','LoadRunDate']]
 Completion.to_sql("IRM.DataQualityResult", con=engine,index=False)

When I am trying to push 'Completion' dataframe to 'IRM.DataQualityResult' table, I am getting the error as stated in my previous post.










0 Votes 0 ·
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I created the table in Synapse DB using Microsoft SQL Server. Even if datatype is not defined as 'Text', still I am facing the issue. I am not sure where exactly is the issue getting arised.

0 Votes 0 ·

Thanks for confirming my suspicion that you are targeting Synapse.

Is the above the full snippet? Or is there more code in the Python script? I would kind of expect to see connection strings and that?

Would it be possible for you to post the full script? I don't need to see the connection strings, but mask them to not disclose anything. I would like to play with this, but I feel uncertain where to start. (As I said, I don't use Azure Notebooks, but I can run it regular Python. ...which I'm also an amateur of.)

0 Votes 0 ·