Lesson Learned #347: String or binary data would be truncated applying batch file in DataSync.

Posted by

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, we got a service request that our customer using DataSync to transfer data from OnPremise to Azure SQL Database they got the following error message: Sync failed with the exception 'An unexpected error occurred when applying batch file sync_aaaabbbbcccdddd\\aaaaa-bbbb-dddd-cccc-8825f4397b31.batch.


  • See the inner exception for more details.Inner exception: Failed to execute the command 'BulkUpdateCommand' for table 'dbo.Table1'; the transaction was rolled back.
  • Ensure that the command syntax is correct.Inner exception: SqlException Error Code: -2146232060 - SqlError Number:2629, Message: String or binary data would be truncated in object ID '-nnnnn'. Truncated value: ''.
  • SqlError Number:8061, Message: The data for table-valued parameter '@changeTable' doesn't conform to the table type of the parameter. SQL Server error is: 2629, state: 1 SqlError Number:3621, Message: The statement has been terminated. 


We reviewed the object ID exposed in the error and we found that a column that belongs to the table1 in OnPremise has been changed of data type from NCHAR(100) to NVARCHAR(255). Once the sync started again there is not possible to update the data in the subscribers of DataSync. 

In this case, our recomendations was: 
     1. Remove the affected table from the sync group.
     2. Trigger a sync.
     3. Re-add the affected table to the sync group.
     4. Trigger a sync.
     5. The sync of Step 2 would remove the metadata for the affected table, and would re-add it correctly on Step 4.



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.