Error when trying to insert long text into a column of type nvarchar(max) via ODBC

Question

Hello,

I've been previously using "text" type for my column to store long text data, however, since this type will be removed in the future, I have changed the column to nvarchar(max) type. I am now encountering an error when trying to insert a large text (I believe it starts failing at around 400K) in my C++ code using SQL Server ODBC driver. The error is: The text, ntext, or image pointer value conflicts with the column name specified.

Through random searching online, I have stumbled upon this email thread from 2009: https://www.nntp.perl.org/group/perl.dbi.users/2009/01/msg33637.html

Where someone proposes to use SQL_LEN_DATA_AT_EXEC(409600) or SQL_LEN_DATA_AT_EXEC(0) as a workaround. I have tried this and both solutions fixes the problem. However, I do not fully understand all the consequences of doing this.

Is this an actual solution to this problem or just a workaround around a possible bug in SQL Server or the ODBC driver? What are the consequences of specifying 0 instead of the actual size of the text when calling SQL_LEN_DATA_AT_EXEC?

SQL Server version:

Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64)
Jul 8 2017 16:43:40
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 19044: ) (Hypervisor)

ODBC driver:

SQL Server 10.00.19041.01

Thanks.

Answer 1

It is difficult to comment without an example that reproduces the problem. Well, to be honest, it is difficult even with, at least for me, since ODBC is an API, I have not worked a lot with.

But as a starting point, I think you should try upgrading to ODBC Driver 18 for SQL Server, so that you have the most recent bits. Beware that this driver defaults to requiring connections be encrypted by a trusted certificate, so you may have to adapt your connection string.