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

[Guest Jose_Manuel_Jurado]

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.

 

 

 

Regards,

 

Continue reading...