Msg 8152, Level 16, State 10 String or binary data would be truncated. The statement has been terminated.

It appears that when you want to “insert into” data from an nvarchar(max) field to another nvarchar(max) field, and the table has a lot of data the ms sql query is terminated giving you the error

Msg 8152, Level 16, State 10 String or binary data would be truncated The statement has been terminated.

After narrowing down data fields and understanding which field is the problematic one, and given that the source field is nvarchar(max) and so is the destination, you need to go on troubleshooting this.

Suppose I have the following query:

INSERT INTO ServiceJournal

(PriorityID, TechnicianID, ServiceTaskID, ClientID, ServiceDate, ProblemReportDateTime, EventTypeid, ReminderID, CustomerSiteID, DetailedProblemDescription)

SELECT        3 AS Expr1, Customers.EmployeeResponsibleID, 16 AS Expr2, Customers.CustomerID, GETDATE() AS Expr3, GETDATE() AS Expr4, 2 AS Expr5, 2 AS Expr6, 1 AS Expr7, Customer_Tasks.TaskDescription

FROM            Customer_Tasks INNER JOIN

Customers ON Customer_Tasks.CustomerID = Customers.CustomerID

WHERE        (Customer_Tasks.IsMaintenance = 1)

The field DetailedProblemDescription is the one producing the problem.

I tried creating a new field on the destination table ServiceJournal, called test as nvarchar(max) and changed my query to

INSERT INTO ServiceJournal

(PriorityID, TechnicianID, ServiceTaskID, ClientID, ServiceDate, ProblemReportDateTime, EventTypeid, ReminderID, CustomerSiteID, test)

SELECT        3 AS Expr1, Customers.EmployeeResponsibleID, 16 AS Expr2, Customers.CustomerID, GETDATE() AS Expr3, GETDATE() AS Expr4, 2 AS Expr5, 2 AS Expr6, 1 AS Expr7, Customer_Tasks.TaskDescription

FROM            Customer_Tasks INNER JOIN

Customers ON Customer_Tasks.CustomerID = Customers.CustomerID

WHERE        (Customer_Tasks.IsMaintenance = 1)

The script inserts a number of rows, as it should!

I tried to rename the problematic field to DetailedProblemDescription2 and retry. It does not work.

I backed up the field data to another field and deleted the field. This is where I started losing the table integrity. I had to take the data with an export script, drop and recreate the table (taken from a backup) and restore the data.

Need to mention that concatenating the DetailedProblemDescription field with cast (DetailedProblemDescription as nvarchar(4000)) or other data type, did not work as well.

To make a long story short, I overcame the insert into problem by putting

SET ANSI_WARNINGS  OFF;

SET ANSI_WARNINGS  ON;

As long as the “problematic” field characters don’t go over 4000 characters, no problem occurs. I will need to test it with more in the next months.

Have no time to investigate this further, but worth’s writing it down and sharing:)

Till next time!