As there is no definitive answer to this ticket, I thought we would share our experience with this issue as it may be of help to others. We are on Qlik Sense but as the issue is, we believe, elicited by the use of the SQL Server ODBC driver, it should be applicable to QlikView users as well.
We would encounter the following error intermittently in our loads on a weekly basis: QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: 01004, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
(Target instance was SQL Server 2012.)
After some research, we tracked the issue down to what seems to be the intermittent incorrect sizing (or the interpretation thereof) of NVARCHAR(MAX) columns by the ODBC driver. It seems the description of the resultset returned does not always size these columns properly for its in-memory schema, and when the processing comes across a value which does not fit into the initially-defined column, a truncation error is encountered.
The solution to this is to CAST the returned NVARCHAR(MAX) columns to NVARCHAR(n) in the SQL portion of the load, where n is a specific value. Our solution peers into the database first, to determine the max lengths of the necessary columns, Peeks them into variables in the load script, then inserts the values in CAST statements in the SQL load portion of the script. Another less robust way to do this would be to set n to an arbitrarily high number (like CAST(column_name AS NVARCHAR(4000))), but this runs the risk of truncating values without any visibility, so it really depends on how well you know your data.
Since we've implemented this pattern, we have not seen this issue again in our loads against the target database.
.png "Former Employee"){kind=icon}
