Translate

Tuesday, 14 February 2017

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;
 <QUERY>
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!