Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →CData Sync Truncation Errors
This entry covers the cause of truncation errors as well as various solutions to resolve them.
Date Entered: 2/13/2020 Last Updated: 2/28/2020
Truncation errors are common issues that occur when the column size in your source connection data exceeds the destination column size. The exact error message differs slightly by destination; here are some of the actual error messages you might see:
SQL Server: "String or binary data would be truncated"
PostgreSQL: "value too long for type <type>"
Snowflake: "<value> is too long and would be truncated"
DB2: "The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use."
Access: "Data type mismatch in criteria expression"
MySQL: "data too long for column <column> at row <row>"
In addition to the errors above, you will see warnings in Sync's log files similar to:
Warning: The size of data for the column 'columnname' with size '2000' is too large and may be truncated to '255.'
The solutions below will discuss potential options to resolve these errors.
LongTextSize
In your advanced job settings, add the following to Additional Options:
LongTextSize=2000;
This will convert every column size of 2000 or greater to type NVARCHAR(MAX).
DefaultColumnSize
In your source connection's advanced connection settings, add the following to Other (Optional):
DefaultColumnSize=4000;
This will change the default length for all string columns to the specified size.
Auto-Truncate Strings
In your advanced job settings, enable the Auto-Truncate Strings checkbox.
This will automatically truncate values that would be too large for the destination.
Specifying Column Size in REPLICATE Statement
You can customize your REPLICATE query to specify column type and size. For example:
REPLICATE Account (name nvarchar(2000))
This would change the type of the name column to nvarchar(2000).
BytesPerCharacter
In situations when your source data has multibyte characters, you may need to configure the BytesPerCharacter property. This only affects replicating to Redshift, DB2, and Oracle destinations.
Under Additional Options >> Advanced >> Job Settings, you can set the property by adding:
BytesPerCharacter=2;
This would multiply the column size in your destination to accommodate the multibyte characters.
We appreciate your feedback. If you have any questions, comments, or suggestions about this entry, please contact our support team at [email protected].