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.
There are two situations, that can cause the above error:
Inserting a few rows instead of one https://stackoverflow.com/questions/5880526/sql-exception-the-statement-has-been-terminated: a string you are trying to insert is longer than the max number of characters. E.g. like a string with 4001 characters in a varchar(4000) datatype. The solution is to fix the SQL query itself.
- Inserting data, that is bigger than the size of a column https://www.codeproject.com/Questions/580168/Theplusstatementplushasplusbeenplusterminated The solution is to extend the Column with a query like this one:
ALTER TABLE [dbo].[PUT_THE_TABLE_NAME_HERE] ALTER COLUMN [PUT_THE_COLUMN_NAME_HERE] NVARCHAR(4000) NOT NULL
- 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.
- 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.
- Run each type of query in Microsoft SQL Server Management Studio and find out which query is causing the error.
- 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 tableshould become:
insert "The","One",0 into tableIf 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 tableAnd for the third:
insert "The","One",5678 into tableThat is how you figure out which columns are causing the issue.
- 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)
- Then finally test the process in FlexiCapture to make sure that the issue was resolved.