Insert query to a database results in error: "The statement has been terminated. String or binary data would be truncated."

Symptoms

I am using an insert query to a database on a custom stage or in the export script. The query results in the following error in the logs:

The statement has been terminated. String or binary data would be truncated.

Cause

There are two situations, that can cause the above error:

mceclip0.png

Resolution

  1. Let's assume that the problem can be reproduced by demand. Disable all custom scripting stages and export script stage. Try to process a document to make sure that SQL related issue has gone. Try to enable stages one by one to figure out which one is problematic.
  2. To troubleshoot the stage dump all SQL insert queries into a text file. Then you will be able to debug these queries in Microsoft SQL Server Management Studio (or a similar tool). Insert the code similar to this one before each SQL insert query:
     System.IO.File.AppendAllText(Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory) + "/logfile.txt","INSERT INTO ..."+Environment.NewLine); 
    Now all SQL queries should be present in the logfile.txt on the Desktop.
  3. Run each type of query in Microsoft SQL Server Management Studio and find out which query is causing the error.
  4. Most likely the overloaded column is Description or Text or something that contains a long value. But it can be a numeric value as well. To determine the exact column you can truncate the values of all text columns and run the query in Microsoft SQL Server Management Studio. E.g. a query:
    insert "The very big text","One more big text",5678' into table
    should become:
    insert "The","One",0 into table 
    If this minimized query not causing the error, then you need to find the exact problematic column. Try to increase the columns one by one, for example, let's restore the original value for the first column:
    insert "The very big text","One",0 into table

    Then for the second:

    insert "The","One more big text",0 into table
    And for the third:
    insert "The","One",5678 into table 
    That is how you figure out which columns are causing the issue.
  5. Increase the length for each problematic column:
    ALTER TABLE [dbo].[EventLog_PUT_ANOTHER_NAME_OF_THE_TABLE] ALTER COLUMN [Details_PUT_ANOTHER_COLUMN_NAME] NVARCHAR(4000)
  6. Then finally test the process in FlexiCapture to make sure that the issue was resolved.

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.

Recently viewed