CData Sync Truncation Errors

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:

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):

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:

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:

This would multiply the column size in your destination to accommodate the multibyte characters.