locked
URGENT RRS feed

  • Question

  •  

    Hi

     

    I am using Sync Framework, in order to synchronize between a server db(sql server 2005 ) and a CE db on a device, running windows mobile 6.

    I'm using VS 2008 c# to create the WCF, WS and the deivce application.

    I have 3 types of Sync: Bidirectional, UploadOnly and DownloadOnly.

     

    1. I have a procedure that to run after the completition of the insert , update and delete processes to the server db, for each table.

    I found two different ways to do it. But honestly I feel like there is a better way.

     

    I can  run the procedure from the "InsertCommand", "UpdateCommand" and "DeleteCommand" of the syncadapter for each table.In this case, I will be running the procedure more than once, which might delay the  synchronization process in general.

     

    I can  check in the "OnSyncProgress" event, whenever the SynStage is "ApplyingInserts" or "ApplyingDeletes" or "ApplyingUpdates" for this table, I run the corresponding proceudre for this table.

     

     

    2. In the download process(server to device), I want it to try to update the record, and if it fails , it will insert it as a new record.I found it simple because all I had to do was taking off the "SelectIncrementalInsertsCommand" from the syncadapter of the table and modify the restriction of the "SelectIncrementUpdatesCommand".

    It seems that the default behavior of the  update is trying to update the record first, and then if it fails, it is inserting it.

    Am I right? or did it happen by chance?

     

     

    I really appreciate any suggestions on any of these 2 points .

    Thanks in advance. 

    • Moved by Max Wang_1983 Friday, April 22, 2011 6:36 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, July 31, 2008 3:03 AM

Answers

  • Is the stored procedure executing inside of a different transaction than the transaction that is Applying Changes?  If it is different, the timeout could be based on the fact that the change application transaction is holding locks that your procedure is waiting on.  You could use 'exec sp_lock' in SQL Server Management Studio to get more info on who is holding what locks.

    You could specify the transaction that change application occurs in using the ApplyingChanges event, then use this same transaction to execute your stored proc in the event, then commit the transaction as part of the ChangesApplied event.

    Could you give some info on what the stored proc is trying to accomplish after each table?

    Let me know if any of this is unclear.
    Thanks-
    Friday, August 8, 2008 5:13 PM

All replies

  • For #1, you should use the DbServerSyncProvider.ChangesApplied event to execute your proc.  This event is fired off after all changes have been applied.

     

    For #2, you can customize the behavior of failed changes as a result of conflicts or any type of failure on the client using the ConflictResolver.  See details here:

     

    http://msdn.microsoft.com/en-us/library/bb725997(SQL.100).aspx

     

    Hope this helps!

     

    Sean Kelley

    Program Manager

    Microsoft

     

     

     

    Friday, August 1, 2008 5:07 PM
    Moderator
  • Hi

    Thanks for your reply.

     

    For #1: As you said , the  DbServerSyncProvider.ChangesApplied  event is fired after all changes are applied.

    However I need to run my procedure after the completition of the upload process( sending data from the device to the server) for each table.

    That's why I was using the SynsProcess.

     

    For # 2: I don't know why, but I've made many testing and as I found the default behavior of the update is trying to update and if there is no rows affected, it will insert a new record.

    I tool off the SelectIncrementalInertCommand totally from the SyncAdapter.

    I think that It's not considered as a failed change.

     

    Any other ideas?

     

    Thanks in advance.

    Tuesday, August 5, 2008 6:29 AM
  •  

    For #1: On the SyncProgress Event, there is a SyncTableProgress object that will tell you table level information such as TotalChanges and ChangesPending.  You can try checking for ChangesPending == 0.

     

    For #2: Is this change application happening on the client or the server?  Could you share your SelectIncremental commands?

     

    Thanks-

    Thursday, August 7, 2008 4:48 PM
  • Hi

    Thanks for your reply.

     

    For #1: I was checking the totalchanges only, now I'm checking the totalchanges and the changespending values:

     

    if (e.TableProgress.TotalChanges != 0 && e.TableProgress.ChangesPending == 0)

    {

    --execute the corresponding procedure for this table

    }

    That's from the  OnSyncProgress event.

    I'm facing another problem now:

    I made some changes to my procedure:

    If the tableprogress is "Ta", and the procedure I'm running here contains a select statement from "Ta".

    It is giving me the following error message:

    Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responsding.

    I set the connectiontimeout to 100 , the commandtimeout to 100 for the procedure.

    and the insertcommand timeout to 100.

    Usually when I run the procedure  manually , it takes less than 2 seconds.

    Still the same error message.

     

    Thanks for your help.

     

     

     

     

    Friday, August 8, 2008 7:01 AM
  • Is the stored procedure executing inside of a different transaction than the transaction that is Applying Changes?  If it is different, the timeout could be based on the fact that the change application transaction is holding locks that your procedure is waiting on.  You could use 'exec sp_lock' in SQL Server Management Studio to get more info on who is holding what locks.

    You could specify the transaction that change application occurs in using the ApplyingChanges event, then use this same transaction to execute your stored proc in the event, then commit the transaction as part of the ChangesApplied event.

    Could you give some info on what the stored proc is trying to accomplish after each table?

    Let me know if any of this is unclear.
    Thanks-
    Friday, August 8, 2008 5:13 PM
  • Hi

     

    Thanks for your help.

     

    Now I'm getting the table name from the OnSyncProgress event. I'm passing it to the

    ChangesApplied event. Then I'm executing the corresponding procedure here.

    I was using a different transaction. Now I'm using the same transaction as the

    ChangesApplied event.

     

    Thanks for your reply, it was very helpful.

     

     

     

    Monday, August 11, 2008 1:41 AM
  • Glad to hear that helped!

    Thanks-
    Monday, August 11, 2008 4:03 PM