Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Accept

Popular articles

SQList version history

Download a previous version of SQList

How do I install or upgrade AxioWorks SQList?

Configure authentication methods to SharePoint

Security Considerations for Installing SQList

More support options:

Use the search page;

Email us at support@axioworks.com;

Send us a message using the Contact Us page.

Error: "The transaction failed and was rolled back. String or binary data would be truncated."

This error is raised when SQList tries to truncate the length of a column in a SQL Server table, but some entries contains data that would be lost (i.e. reducing the length of a VARCHAR(100) to VARCHAR(30) when there are entries with strings longer than 30 characters).

SQList sets the length of columns to the same length defined by the "Maximum number of characters" when configuring a column for a SharePoint list. If the length of a list's column is reduced but the list contains existing values longer than the newly set maximum length, SharePoint will not truncate the values but keep them as they are. When SQList tries to copy these values into a SQL Server table, the length of the value being inserted is too long for the column and SQL Server throws the "String or binary data would be truncated" exception.
 
Resolution
 
This is standard behaviour for SQList and the correct way to resolve this problem is to set the maximum length for the column to their original value so that no data will be truncated. We strongly recommend you adjust the maximum length by following these steps:

  1. In the Windows event report, find the SQL list truncation error message;
  2. In the message there is the offending SQL statement enclosed in square brackets;
  3. By comparing the values in the SQL statement and the structure of the table, you should be able fairly quickly to find the fiend that is causing the truncation error;
  4. Compare the length of the value being inserted with the maximum length of the corresponding column in the SharePoint list and adjust the latter accordingly;
  5. On the next run, SQList will reflect the new length in the SQL table and the truncation error should not occur;
  6. if the error occurs for another item, repeat this entire process. 
However, SQList can be configured to ignore this error and truncate. Should you wish to allow truncation, override this behaviour by checking the "Ignore ANSI warnings" setting, this will allow the truncation. For details aboutthis setting see Tuning SQList for optimal performance.