_tds.OperationalError: Received an invalid column length from the bcp client for colid...

[gizmo93]

When trying to bulk insert a String into a VARCHAR(MAX), NVARCHAR(MAX) field, we get this error message.

CTDS version: 1.11.0
FreeTDS version: 1.00.80
Python 3.7
Windows 10 64 bit

Example:

CREATE TABLE dbo.Test
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FancyCol INT NOT NULL,
[Value] NVARCHAR(MAX)
)

CREATE TABLE dbo.Test2
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FancyCol INT NOT NULL,
[Value] NVARCHAR(4000)
)

Code to reproduce (sorry, really had problems to get the code properly formatted inside the Github Editor)
test.txt

[joshuahlang]

If I'm understanding correctly, you're reporting the behavior documented here: https://zillow.github.io/ctds/bulk_insert.html#text-columns

As far as I know, this is a deficiency in FreeTDS' implementation of BUILK INSERT

[gizmo93]

Hey, there are no problems with "normal" VARCHAR/NVARCHAR columns, they work as expected, if I wrap them in ctds.VarChar().
Problem seems to be only when trying to BULK INSERT into a VARCHAR/NVARCHAR (MAX) column.
Is there any solution / workaround for this?

[kafran]

Avoid varchar/nvarchar (max). Use ntext instead.