locked
Changes in AFTER INSERT and Sync Framework SQL 2008 RRS feed

  • Question

  • We use the Sync Framework to let the users work offline. When he gets online the offline data is synchronized with the central database. Some tables include a name attribut, that is generated by the central database when a record is inserted. To do so I update new records in the after insert trigger. Unfortunately this changes are not registred by the Sync Framework. As result, the changes do not get back to the client database.

     

    How can I force the Sync Framework from within a (insert) trigger to register a record as altered?

    • Moved by Max Wang_1983 Thursday, April 21, 2011 5:58 PM forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
    Friday, October 31, 2008 9:17 AM

Answers

  • Urs,

     

    I suspect that you are not getting these changes as it thinks that this particular change is a loopback and the query used to enumerate ignores it. Specifically, the query filters out any changes that originated from the client currently trying to sync with the server which typically makes sense.  You might want to use the WITH CONTEXT clause to store a different value in this column.  The syntax associated with this clause is described here:

     

    http://msdn.microsoft.com/en-us/library/bb895330.aspx

     

    The link below provides samples in the context of using change tracking with sync services for ADO.NET:

     

    http://msdn.microsoft.com/en-us/library/cc305322.aspx

     

    Hope this helps,

     

    Sean Kelley

    Program Manager

    Microsoft

     

     

     

    Saturday, January 10, 2009 1:35 AM
    Moderator

All replies

  • Does this trigger insert new rows to the same table or a different table?

    If the same table, then are the other metadata (creationTime if create, updateTime if update) getting set correctly so that it appears as a new change?

    If it is a different table, is it part of the same sync group?

    Do these changes make in the next sync or they never do?

    Friday, October 31, 2008 5:02 PM
  • The trigger changes exclusively the new inserted row (the "name" of the new inserted record is generated).
    The changes are visible in the server database and in a different client. The client that created the row doesn't get the changes until the row is changed by a different client (or directly in the server database). If the original client changes the row, the "name" on the server database is overriden (becomes empty).
    Saturday, November 1, 2008 9:44 AM
  • Are you setting the metadata correctly in the after trigger?

    Essentially what should happen is this trigger after updating the "name" attribute/column, should increase the metadata (basically the update tick count and maybe the originator_id too) to reflect that this is a new change. Then when the original client syncs, this change appears as a new change that the client has not seen yet and will be downloaded.

     

    Now if the metadata is not set correctly, this row does not apper as an updated row and hence the original client does not get the row with the new "name" when it syncs. Now if the server updates this row once more, it appears as a new change that the server did and it does get downloaded.

    For the other clients, they see this change as a whole (since they last synched) and get the updated row with the new name - whether the server updates the name column explicitly or with the trigger.

     

    In this state, now if the original client re-updates this row,this row goes to the Server (with still empty name) and overwrites the one in the Server and I think you have only an after insert trigger for populating the name and hence see it NULL.

     

    Please check the metadata updating/setting in the triggers.

    Monday, November 3, 2008 11:14 PM
  • Using the Sync Framework with SQL 2005 we added the metadata attributes (and the tombstone tables) manually. With SQL 2008 the syncronization metadata is managed by the server itself. How do we see and alter the system managed metadata?

    Wednesday, November 5, 2008 6:55 AM
  • What I meant was, if you were using SQL 2005 and added the custom columns, after the trigger fires and updates the name attribute, can you look up that row and see if its update timestamp, originator id etc (all the metadata columns that you added) have been updated? Basically snaposhot of these columns before the trigger fires and after to see the delta and make sure that it is done correctly so that when sync happens, these get enumerated.

    If you are using SQL 2008 and the in-built change tracking, then again after the trigger fires, look at the change table to see that these change have been reflected correctly and that they will get enumerated on the next sync.

    Thursday, November 6, 2008 7:27 AM
  • You mention a "change table". My (hopefully only) problem is, that I don't see such a "change table".

    Where can I find it?

    Thursday, November 6, 2008 9:17 AM
  • If you have added the metadata columns, then you know what those columns and tombstone tables are.

    If you used the SQL Server 2008 Change Tracking, then the CHANGETABLE I am referring to is this one mentioned in this article: http://msdn.microsoft.com/en-us/library/bb934145.aspx

     

     

    Monday, November 10, 2008 7:50 AM
  • Thank you for your response... and your patience!

    As I mentioned already in the subject of my first post: I'm using the SQL Server 2008. We migrated from version 2005: There we could manage all information by ourselves and all worked fine. My goal is to have the same functionality using version 2008.

    With your help, I now can see informations about changed and the newly inserted records. My intention is to force the synchronization back to the originator CE-database of a record that is newly inserted and is altered in the AFTER INSERT trigger. The version of the newly inserted record is not changed by a UPDATE in the AFTER INSERT trigger. As consequence the originator CE-database does not get these changes back. Is there a possibility to increase the version of a single (or a few) record(s) without synchronizing all data back to the originator again? It seems to me, that the version is based on a transacion and so the changes made in the AFTER triggers belong to the same transaction as the insert. Because the transaction belongs to the sync process, that inserts the record, the originator can't see these changes made by the central database.

    Do you see my problem? And do you see an answer?

     

    Monday, November 10, 2008 9:49 AM
  • Urs,

     

    I suspect that you are not getting these changes as it thinks that this particular change is a loopback and the query used to enumerate ignores it. Specifically, the query filters out any changes that originated from the client currently trying to sync with the server which typically makes sense.  You might want to use the WITH CONTEXT clause to store a different value in this column.  The syntax associated with this clause is described here:

     

    http://msdn.microsoft.com/en-us/library/bb895330.aspx

     

    The link below provides samples in the context of using change tracking with sync services for ADO.NET:

     

    http://msdn.microsoft.com/en-us/library/cc305322.aspx

     

    Hope this helps,

     

    Sean Kelley

    Program Manager

    Microsoft

     

     

     

    Saturday, January 10, 2009 1:35 AM
    Moderator