locked
Failed to execute the command 'BulkUpdateCommand' for table 'xxx'; the transaction was rolled back. RRS feed

  • Question

  • Hi

    I am using SyncFx 2.1 in a collaborative scenario over WCF. Everything worked fine in 2.0. On some clients i get the following error during sync ...

    Error:

    Exception Type
    Microsoft.Synchronization.Data.DbSyncException

    Message
    Failed to execute the command 'BulkUpdateCommand' for table 'users'; the transaction was rolled back.
    Ensure that the command syntax is correct.

    Stack Trace
    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.KnowledgeProviderProxy.ProcessChangeBatch(CONFLICT_RESOLUTION_POLICY resolutionPolicy, ISyncChangeBatch pSourceChangeManager, Object pUnkDataRetriever, ISyncCallback pCallback, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics) at Microsoft.Synchronization.CoreInterop.ISyncSession.Start(CONFLICT_RESOLUTION_POLICY resolutionPolicy, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics) at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(SyncIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, KnowledgeSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCallbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAdapter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32& changesApplied, Int32& changesFailed) at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWayKnowledgeSync(SyncDataConverter sourceConverter, SyncDataConverter destinationConverter, SyncProvider sourceProvider, SyncProvider destinationProvider, Int32& changesApplied, Int32& changesFailed) at Microsoft.Synchronization.KnowledgeSyncOrchestrator.Synchronize() at Microsoft.Synchronization.SyncOrchestrator.Synchronize() at SiliconLabsExplorer2.SyncApplication.SyncHelper.SynchronizeProviders(KnowledgeSyncProvider localProvider, KnowledgeSyncProvider remoteProvider, SyncDirectionOrder direction)

    The client has got the table and does have permission to use it even though the inner exception shows ...

      Inner Exception:

    Inner Exception Type
    System.Data.SqlClient.SqlException
    Inner Message
    Cannot find the object "users" because it does not exist or you do not have permissions.
    Inner Stack Trace
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() 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) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 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)

    Has anyone else come across something like this ?

    regards

    Eliot

    Wednesday, April 6, 2011 7:56 PM

Answers

  • Finally found the answer, and Jin it was permissions.

    All tables appeared to have the same permissions, and scope was generated by code. However for two tables users and usersexceptions the _bulkupdate stored proc that operated on the BulkType failed. I updated the permissions and all is good.  

    • Marked as answer by EliotRayner Thursday, July 21, 2011 6:27 PM
    Thursday, July 21, 2011 6:27 PM

All replies

  • can you check if the user-defined table type for the table exists? or check this out: http://stackoverflow.com/questions/5540345/bulkinsertcommand-failed-in-sync-framework-2-1
    Wednesday, April 6, 2011 11:36 PM
  • Hi June, the user-defined table type for the table exists. I've checked and all sync related tables, triggers, udts, sp's after running a DeprovisionScope and DeprovisionStore and nothing sync related is left in the db. I'll check out the info in the link ...

    Thursday, April 7, 2011 10:37 AM
  • Hi June

    with regard to your post here ...
    http://stackoverflow.com/questions/5430813/syncframework-with-sqlserver could it be an issue as i'm using SQL Server 2008 SP2 to SQL Server Express 2008 clients?

    Eliot

    Thursday, April 7, 2011 2:22 PM
  • try enabling Sync Fx tracing to see if there's anything else it logs: http://msdn.microsoft.com/en-us/library/cc807160.aspx

    is it only happening on that specific table? or all tables in the scope? have you checked the schema name?

    Thursday, April 7, 2011 2:58 PM
  • there are 41 tables in the scope and all work fine, its just this one table (its only got 30 records), all the code to provision is generated by sync framework. The schema name is dbo for all the tables. I'll try the sync fx tracing, but i dont receive this error in my development environment, it is only on certain clients (Windows 7 enterprise/SQL Server Express 2008) and the app is in production so it might take a while.

    Thursday, April 7, 2011 3:13 PM
  • The other things you can consider checking are

     - the permission on these tables

    - is the sync object schema dbo or not dbo?

    Thursday, April 7, 2011 11:58 PM
    Answerer
  • Hi June

    I ran a sync trace and it came back with the following ...

    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:610,       RelationalSyncProvider.BatchedEnum
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:610,       Executing Command: [users_selectchanges]
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:610,          Parameter: @sync_min_timestamp Value: 29395239
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:610,          Parameter: @sync_scope_local_id Value: 1
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:610,          Parameter: @sync_scope_restore_count Value: 0
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:610,          Parameter: @sync_update_peer_key Value: 6
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:626,       Update for row with PK: USER_ID = 21 on MyPeerDb
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:626,          UV: 0,29395254 CV: 0,28636327
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:626,       RelationalSyncProvider.BatchedEnum: Memory data cache size after reading row: 989455 Bytes
    INFO   , MySyncApp2, 9, 04/12/2011 12:55:51:626,    RelationalSyncProvider.BatchedEnum: --- End Table "users" ---
    INFO   , MySyncApp2, 9, 04/12/2011 12:55:51:626,
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:626,       RelationalSyncProvider.BatchedEnum: Closing DataReader
    INFO   , MySyncApp2, 9, 04/12/2011 12:55:51:626,       RelationalSyncProvider.BatchedEnum: Finished enumerating adapter users
    INFO   , MySyncApp2, 9, 04/12/2011 12:55:51:626,       RelationalSyncProvider.BatchedEnum: Adding rows enumerated for adapter users to DataSet.
    VERBOSE, MySyncApp2, 9, 04/12/2011 12:55:51:626,       SyncBatchProducer: Read last row's Sync_row_timestamp value for table users as 29395254.

    which is telling me there is one user row to update with PK = 21. Further on i get the following ....

    INFO   , MySyncApp2, 5, 04/12/2011 12:56:19:597,    ----- Updates for Table "users" -----
    VERBOSE, MySyncApp2, 5, 04/12/2011 12:56:19:613,    Executing Command: [users_bulkupdate]
    VERBOSE, MySyncApp2, 5, 04/12/2011 12:56:19:613,       Parameter: @sync_min_timestamp Value: 29429765
    VERBOSE, MySyncApp2, 5, 04/12/2011 12:56:19:613,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, MySyncApp2, 5, 04/12/2011 12:56:19:613,       Parameter: @changeTable Value: users
    WARNING, MySyncApp2, 5, 04/12/2011 12:56:19:707, Bulk command BulkUpdateCommand failed with the following exception. Rows will be retried during single apply. System.Data.SqlClient.SqlException: Cannot find the object "users" because it does not exist or you do not have permissions.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       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)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       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)
    ERROR  , MySyncApp2, 5, 04/12/2011 12:56:19:707, Transaction has exited due to command execution failure, throwing exception
    ERROR  , MySyncApp2, 5, 04/12/2011 12:56:19:707, Caught exception while applying changes: Microsoft.Synchronization.Data.DbSyncException: Failed to execute the command 'BulkUpdateCommand' for table 'users'; the transaction was rolled back. Ensure that the command syntax is correct. ---> System.Data.SqlClient.SqlException: Cannot find the object "users" because it does not exist or you do not have permissions.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       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)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       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)
       --- End of inner exception stack trace ---
       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)
    ERROR  , MySyncApp2, 5, 04/12/2011 12:56:19:707, Rolling back application transaction.
    VERBOSE, MySyncApp2, 5, 04/12/2011 12:56:19:707, Closing Connection
    INFO   , MySyncApp2, 5, 04/12/2011 12:56:19:707,    EndSession() called on Provider SqlSyncProvider, Microsoft.Synchronization.Data.SqlServer, Version=3.1.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
     
    So it looks like its probably a permission issue?

    Tuesday, April 12, 2011 1:25 PM
  • on the destination database, can you confirm that schema, permissions are set properly? check that the User defined table type exists as well.
    Tuesday, April 12, 2011 4:36 PM
  • the destination database is setup correctly and fyi my windows xp client (just mentioning this causes its the only difference) is able to connect and sync without this error. heres part of its sync trace from an hour ago.

    INFO   , MySyncApp2, 6, 04/12/2011 16:40:42:085,    ----- Updates for Table "users" -----
    VERBOSE, MySyncApp2, 6, 04/12/2011 16:40:42:101,    Executing Command: [users_bulkupdate]
    VERBOSE, MySyncApp2, 6, 04/12/2011 16:40:42:101,       Parameter: @sync_min_timestamp Value: 29429782
    VERBOSE, MySyncApp2, 6, 04/12/2011 16:40:42:101,       Parameter: @sync_scope_local_id Value: 1
    VERBOSE, MySyncApp2, 6, 04/12/2011 16:40:42:101,       Parameter: @changeTable Value: users
    INFO   , MySyncApp2, 6, 04/12/2011 16:40:42:132, Applied 1 of 1 rows with bulk command BulkUpdateCommand
    VERBOSE, MySyncApp2, 6, 04/12/2011 16:40:42:132,    Updated row with PK using bulk apply: USER_ID="9"  on MyPeerDb
    VERBOSE, MySyncApp2, 6, 04/12/2011 16:40:42:132,
    INFO   , MySyncApp2, 6, 04/12/2011 16:40:42:132,       1 Updates Applied
    INFO   , MySyncApp2, 6, 04/12/2011 16:40:42:132,    --- End Updates for Table "users" ---

    The User defined table type exists on the server ... as do its related sps ?

    Tuesday, April 12, 2011 5:01 PM
  • another wild guess, can you check the collation for the failing database?
    Friday, April 15, 2011 10:36 AM
  • i'll take a look. fyi i did a backup and restore, followed by a post restore fixup from a machine without the issue onto a machine that had the issue and it still has the 'users_bulkupdate' error ?? as soon as i find anything i'll let you know

    Friday, April 15, 2011 10:49 AM
  • The server database collation setting is NULL and the client database is 'SQL_Latin1_General_CP1_CI_AS'. For the users table the collation is Null on both databases.

     

    Friday, April 15, 2011 11:02 AM
  • ok. i just thought of checking just in case the client SQL Server or database may have been set to a case-sensitive collation causing "Users" to be different than "users"

    Friday, April 15, 2011 11:25 AM
  • I ran a trace using sql profiler and found that the offending users_bulkupdate  error occurs here ...

    declare @p3 dbo.users_BulkType
    insert into @p3 values(9,
    N'Brad Username',
    N'xxxx                                          ',
    N'XiWDrXgfL3c5RuQNom6bD+B8T78=',
    N'f13cf220-bb85-4133-bcb2-7601ddab849d',
    '2011-04-18 11:43:40.8770000',
    '2011-04-18 11:43:45.5100000',
    NULL,1,1,1,1,1,1,1,1,1,
    '2011-05-12 10:39:54.3700000',
    5,
    N'brad@myCompany.org',
    1,NULL,NULL,1,1,1,1,1,15,1,
    4595621,6,28636327,0)
    insert into @p3 values(24,
    N'John Smith',
    N'xxxxxxx',
    N'UAJNwbhwdpfoMzjEVKz9abW98qk=',
    N'ded6ea11-a7e2-48b1-8e6c-681dc79fce9f',
    '2011-04-18 11:44:07.2100000',
    '2011-04-18 11:44:07.2100000',
    N'NB0009956',1,1,1,1,1,NULL,NULL,NULL,NULL,
    '2011-05-17 12:08:54.8500000',
    5,
    N'johnsmith@myCompany.org',
    1, --BaseLoc
    NULL,NULL,1,0,0,0,0,1,1,
    4522001,6,28636327,0)

    exec [users_bulkupdate] @sync_min_timestamp=29547574,@sync_scope_local_id=1,@changeTable=@p3

    When i try to run the SQL above from management studio i get an 'Conversion failed when converting date and/or time from character string.' error, it looks like the dates are DateTime2 but the DateTime fields are of DateTime. Could this be the source of my issue ? I tried changing all the date values above to only having DateTime precision of 3 characters and it works from management studio.

    Monday, April 18, 2011 3:24 PM
  • this must looks to be a bug in SQL profiler http://florianreischl.blogspot.com/2010/01/bug-in-sql-profiler-when-working-table.html so can't be the issue here.

     

    Monday, April 18, 2011 6:48 PM
  • Finally found the answer, and Jin it was permissions.

    All tables appeared to have the same permissions, and scope was generated by code. However for two tables users and usersexceptions the _bulkupdate stored proc that operated on the BulkType failed. I updated the permissions and all is good.  

    • Marked as answer by EliotRayner Thursday, July 21, 2011 6:27 PM
    Thursday, July 21, 2011 6:27 PM
  • What permissions did you finally have to set in order to get this to work?
    Thursday, September 15, 2011 8:22 PM
  • Hi Eliot, I'd also like to know what permissions you updated to fix this as I'm having similar issue. Thanks, Gary

    Tuesday, October 4, 2011 10:29 AM
  • you might want to check the following based on the docs:

    Synchronization operations require the following permissions:

    • EXECUTE permissions on all the stored procedures that Sync Framework uses to read and write to metadata tables and base tables.

    • SELECT, INSERT, UPDATE, and DELETE permissions for metadata tables and any base tables that will be updated during a synchronization session.

     

    When you provision SQL Server databases that use SqlSyncProvider, be aware of the following permissions requirements for provisioning: 

    • CREATE TABLE permissions to enable creation of the metadata tables: scope_info and scope_config, and the tracking tables that are created for each base table.

    • ALTER TABLE to add triggers to the base tables.

    • CREATE PROCEDURE permissions to create the procedures that Sync Framework requires.

    • SELECT and INSERT permissions for scope_info and scope_config tables.

    • SELECT permissions for base tables.

    Tuesday, October 4, 2011 11:32 AM
  • Hi Guys

    Still don't understand why this only affected two out of thirty tables. But to get it working I granted control (after trying SELECT, INSERT, UPDATE and DELETE) on the SQL Server 2008 box to user for the two tables having the issue. I realise this is not the way to do it , when i have more time i'll try to downgrade the priveleges.

    Eliot

    Tuesday, October 4, 2011 11:47 AM
  • Can you please give more details on for what user need to give permissions? I am admin on machines, still I am getting same error. I am trying to sync data between Local DB and azure db. Azure db is having connection string with user name and password.

    Rosi Reddy

    Sunday, October 20, 2013 10:01 AM