BulkInsertCommand failed in Sync Framework 2.1

4
votes

On occasion I get the following error when trying to synchronize from SQL Express to SQL Server using Sync Framework 2.1. Once a client gets this error they have to reinitialize the scope. There can't be anything wrong with the syntax like the error states because it runs no problem for long periods of time (with inserts happening). Any thoughts?

11:18:21 AM Failed to execute the command 'BulkInsertCommand' for table 'XXX'; the transaction was rolled back. Ensure that the command syntax is correct. 11:18:21 AM Microsoft.Synchronization 11:18:21 AM at Microsoft.Synchronization.Data.ChangeHandlerBase.CheckZombieTransaction(String commandName, String table, Exception ex)

From a trace log:

WARNING, OfflineAgentMonitor.vshost, 13, 04/05/2011 11:16:17:224, Bulk command BulkUpdateCommand failed with the following exception. Rows will be retried during single apply. System.Data.SqlClient.SqlException (0x80131904): Trying to pass a table-valued parameter with 19 column(s) where the corresponding user-defined table type requires 20 column(s).

2
c#sqlmicrosoft-sync-framework
do you have multiple scopes defined against the same table? - JuneT
No. I just have one scope defined. It doesn't happen with all the tables. - AKoran
for the failing client, try comparing the columns in the user defined table type (under Programmability->Types->User-Defined Table Types) with the bulkinsert and bulkupdate stored procedures on both sides of your sync pair.if the Table UDT and the stored proc has different column counts, there must be problems with provisioning and keeping both scope definitions in sync between the synching partners. that explains why re-provisioning the scope fixes it since the scope is being recreated with the right number of matching columns in both Table UDT and the stored procs on each synching party. - JuneT

2 Answers

2
votes

try to enable Sync Fx tracing and check if Sync Fx logs the original exception. if i remember it right, the exception is normally raised when the db connection is lost. you should be able to retry the sync though without re-provisioning the scope.

0
votes

This happened to me syncing between 2 SQL Azure databases. The initial cause was that the slave DB grew larger than it's provisioned size. I increased the size, but it was a good 20 minutes before the sync stopped throwing the error