MSSQL NVARCHAR(MAX) chopping off first 3 characters

I have an MSSQL database with text, date and integer fields. I access it using SQLDatabaseMBS from macOS using ‘libtdsodbc.dylib’, It connects, creates the database, creates the tables, INSERTs, DELETEs and UPDATEs OK. When I use a SELECT command the date and integer fields return OK, but not the text fields.

When the text field is define as data type NVARCHAR(MAX), the first three characters of every string are missing! If I re-CREATE the same field as NVARCHAR(50) or VARCHAR(50) there are no missing letters in the SELECT. It’s not just one text field, but all of them. When debugging, the characters are missing from the RecordSet. When I view the table using MSSQL Server Management Studio I can see the full text is there.

Is this a Unicode issue? Or is the problem likely to be in SQLDatabaseMBS? Or in libtdsodbc.dylib? I am not an MSSQL guru, but hope someone out there is, as I would prefer to use the MAX and Unicode side of NVARCHAR data type fields.

I’m going to make a guess here… but I would venture to say it is a bug…
What it sounds like is happening is, when you specify “MAX”, it relies on information in the data to determine how much storage space is required (and that information is in those missing 3 bytes), while when you give a specific length, the database stores the data in a different manner.

NVARCHAR always stores 2bytes per character even if the unicode code point does not require it

HI @David Cox
I’ve seen this, its maddening!

add this and see if it fixes it. it did for me.

Option(“ODBCAddLongTextBufferSpace”) = “false”

Thanks you Dave and Russ.

Does it work now?
It links like the first byte gets consumed when we detect the length.

Yes, Russ’ fix works. I just tested the fields as NVARCHAR(MAX) and they no longer chop off the first 3 characters of the text.

for next plugin, I added the option to be set by default.