SQL Server pyodbc large text insertion issue

Question

I have a large string text to be inserted into a column in SQL Server 2014 Express (v12.0.2000). Initially I defined the field as text type and the data was inserted into the table without any issues. Later I found out that the text datatype is deprecated and not supposed to be used. Converting the datatype to varchar(max) started a weird issue in pyodbc. When the same data is inserted to the varchar(max) column, it throws an error with the following message:

[HY000] [Microsoft][ODBC SQL Server Driver]Warning: Partial insert/update. The insert/update of a text or image column(s) did not succeed. (0) (SQLPutData)

but the same insertion works fine with the text column.

I was using {SQL Server} driver with windows authentication, when I changed it to {SQL Server Native Client 11.0} it started to fail in authentication, so I switched it back to {SQL Server} as it was working with the text data type.

I looked into some of the discussion regarding similar issues, but could not find a case that helps in debugging the issue.

I checked the sizes of strings where the data is successfully inserted and failed:

• TEXT: all data inserted successfully.
• VARCHAR(MAX): string with length 98566 inserts but with length 326235 fails.

Any guidance even debug tips will be highly appreciated. Thanks in advance.

Answer 1

The "Warning: Partial insert/update." error is related to

• the ancient DRIVER=SQL Server ODBC driver (SQLSRV32.DLL),
• [n]varchar(max) columns, and
• strings longer than 204800 characters.

It does not occur with newer drivers like DRIVER=ODBC Driver 11 for SQL Server or DRIVER=ODBC Driver 17 for SQL Server.

You should update your driver and then ask a new question if the authentication error persists and you need help with that.