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!

Advertisements

About cpsaroudakis

IT professional, founder of CreativePeople.gr

Posted on February 14, 2017, in Everyday IT issues, MS Access, sql and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: