locked
How to handle exceptions with Sync? Execute Roolback RRS feed

  • Question

  • I'm making a application than Sync with SQL Server Enterprise and SQL Server Express.

    When the Sync can not synchronize, the event ApplyChangeFailed is execute.

    How to run a Sync roolback when the event ApplyChangeFailed  is called?

    Because for example, I have tree records.

    Code    Name
      1        Mark
      2        Paul
      3        Sarah

    The Mark record is inserted withou error, but in Paul record a error happen. But the Mark record was inserted in database. I don't want this.

    Somebody help me?

    Thank you advance.

    Marcos Antonio Aguiar Jr
    • Moved by Hengzhe Li Friday, April 22, 2011 5:30 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Saturday, February 21, 2009 2:07 PM

Answers

  • When you do sync, there are TSQL execution happened on both servers, the SQL 2005 Server and the SQL 2005 Express.

    Would you please run SQL profiler to capture the TSQL statements on both Servers during sync?  At least we should see a COMMIT TRAN statement (indicating rows are updated on the tables).  This will help us find more on your scenario.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 27, 2009 6:18 PM
    Answerer

All replies

  • If you would like to abort the Sync Process if there is such an error, please raise an System.Exception in the ApplyChangeFailed event handler so this Sync will be cancelled.  On the other hand, you should have a try/catch block wrapped around the .Synchronize() method.

    Thanks.

    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, February 22, 2009 5:45 PM
    Answerer
  •  
    that way?

            private void Sync()  
            {      
                try  
                {  
                    SyncAgent syncAgent = new SyncAgent();  
                    syncAgent.Synchronize();  
                }  
                catch(Exception ex)  
                {  
                    throw new Exception(Library.ErrorMessage(e));  
                }  
            }  
               
            private void ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)  
            {  
                    throw new Exception(e.Conflict.ErrorMessage);  
            } 

    thanks.

    Marcos Antonio Aguiar Jr
    Monday, February 23, 2009 11:46 AM
  • You pretty got the idea, logical-wise.  However,
    1. You need to subscribe the ApplyChangeFailed event on the ServerProvider for uploading changes (or the ClientProvider for downloading changes).
    2. Another technical detail is the Commit Transaction is on the Sync Group level.  In your example, you have only 1 table listed in synchronization and your code should work.  If you got more tables and separate them into several sync groups, please be aware of their dependencies.  For example, if you have PK in one table and FK in another table and they are in 2 sync groups.  If there is an error during sync the PK table, and transaction was rolled back.  The sync for the FK table may hit runtime sync failure as well if the dependent PK table data were not presented on the target.  As a result the transaction on the FK table may also got rolled back.

    Please consider all the possible situation could appear when implementing the ApplyChangeFailed(object sender, ApplyChangeFailedEventArgs e)  event call.

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Patrick S. Lee Monday, February 23, 2009 8:39 PM
    Monday, February 23, 2009 8:33 PM
    Answerer
  • Hi Leo, Thanks for your answer.

    In my example, I have one SyncGroup and fifteen SyncTables. All SyncTables is related with this SyncGroup.

    I understand that Sync work with a transaction for each SyncGroup.

    Doing so, I thought that any error that occurs in some of the 15 tables (SyncTable), the roolback should be executed, but this is not happening.

    Example:

    private void SyncProducts()  
    {  
        SyncTable table = new SyncTable("PRODUCTS");  
        table.CreationOption = TableCreationOption.UseExistingTableOrFail;  
        table.SyncDirection = SyncDirection.Bidirectional;  
        table.SyncGroup = GroupSync;  
        syncAgent.Configuration.SyncTables.Add(table);  
     
        ...  

    The GroupSync is a global variable.

    In my post previous, the event ApplyChangeFailed is called by ClientProvider and ServerProvider. I added the same event for both.

    Can you help me again?

    Thanks

    Marcos Antonio Aguiar Jr
    Tuesday, February 24, 2009 1:58 PM
  • So - would you please share the TSQL trace on both SQL Server 2008 and SQL Express 2008 when SyncAgent.Synchronize() is called?

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, February 25, 2009 1:47 AM
    Answerer
  • Hi Leo.

    Sorry for the delay responding. It was holiday in Brazil, Carnaval..rs

    I'm using the SQL Server 2005 Enterprise and SQL Server 2005 Express.

    I don't understand your question. 

    Could you explain better, please?
     
    Thank you advance.

    Marcos Antonio Aguiar Jr
    Friday, February 27, 2009 5:38 PM
  • When you do sync, there are TSQL execution happened on both servers, the SQL 2005 Server and the SQL 2005 Express.

    Would you please run SQL profiler to capture the TSQL statements on both Servers during sync?  At least we should see a COMMIT TRAN statement (indicating rows are updated on the tables).  This will help us find more on your scenario.

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 27, 2009 6:18 PM
    Answerer