Sync won't update Server, failed to execute 'BulkInsertCommand'

Jun 4, 2014 at 8:25 AM
I have a remote SQL server containing the tables Visit and Visit_tracking, but when the tables are populated the sync fails with some internal errors. When I delete the records in Visit and Visit_tracking and manually add an entry it works however. It is possible that my tables are corrupted somehow, but I don't know what to look for. Here are the errors generated by the sync request:
System.Exception: Microsoft.Synchronization.Data.DbSyncException
Failed to execute the command 'BulkInsertCommand' for table 'Visit'; the transaction was rolled back. Ensure that the command syntax is correct.
at Microsoft.Synchronization.Data.ChangeHandlerBase.CheckZombieTransaction(String commandName, String table, Exception ex)
at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ExecuteTVPCommand(IDbCommand cmd, DataTable applyTable, DataTable failedRows)
at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ApplyBulkChanges(DataTable dataTable)
at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChangesInternal(DbSyncScopeMetadata scopeMetadata, IDbTransaction transaction, FailedDeleteDelegate_type failedDeleteDelegate, DataSet dataSet, ChangeApplicationType applyType)
at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, IDbTransaction applyTransaction, DataSet dataSet, DbSyncSession DbSyncSession, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
at Microsoft.Synchronization.Data.RelationalSyncProvider.SingleTransactionApplyChangesAdapter.Apply(DataSet dataSet, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, DataSet dataSet, DbSyncSession dbSyncSession, Boolean commitTransaction)
at Microsoft.Synchronization.Data.RelationalSyncProvider.ProcessChangeBatch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, Object changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessionStatistics)
at Microsoft.Synchronization.Services.SqlProvider.SqlSyncProviderService.ApplyChanges(Byte[] serverBlob, List1 entities)
at Microsoft.Synchronization.Services.UploadChangesRequestProcessor.ProcessRequest(Request incomingRequest)
at Microsoft.Synchronization.Services.SyncService
1.ProcessRequestForMessage(Stream messageBody)

System.Data.SqlClient.SqlException
String or binary data would be truncated.
The data for table-valued parameter "@changeTable" doesn't conform to the table type of the parameter.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource
1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ExecuteTVPCommand(IDbCommand cmd, DataTable applyTable, DataTable failedRows)
Jun 5, 2014 at 12:05 AM
So I found out what the problem was. As it turned out one of the strings in the SQLite database was longer than the allotted 255 characters and while SQLite was cool with that, the SQL server wasn't. The problem is that it is next to impossible to figure that out from the error message above and it cost us nearly a week of work. Am I not reading the error in the post above correctly or could it be a little more descriptive?
Coordinator
Jun 6, 2014 at 10:07 AM
No because the error message come from SQL SERVER