locked
Updated information during ApplyInsert Not coming down to device RRS feed

  • Question

  • Hello,

    I am using Sync Services to sync data between sqlce 3.5 and sql 2005. The table that I am synching has a fire_name column which stores the unique name (e.g. ALC9001) for each record. To make this system work, whenever the user creates a record in this table on mobile device, the application assigns the fire_name in the following format-"MOBxxxx". Here MOB indicates that the record was created on mobile device and should be assigned a real name by server. For assigning xxxx, the application checks the last assigned MOB name and increments its xxxx prefix.

    On synching, within the applyinsert stored procedure, we change each MOBxxxx to actual name. This works fine and the real name gets inserted on the server. But the same change is not coming down to device.

    Is it because the sync services are not looking this as an update and so deosnt come down to device? I have also removed the check for originator_id from incremental update stored procedure so that everything comes down as update.

    Any help on this is appreciated.

    Thanks

    Apurva
    • Moved by Max Wang_1983 Thursday, April 21, 2011 11:07 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, October 9, 2009 9:04 PM

Answers

  • Hi Apurva,

    the behavior you described is the expected behavior, i.e. for incremental changes, the sync services look the originator_id and the change timestamp to see if a row should be send to the current client -- to aovid loop back for rows just updated by the clients.

    what you did to remove the originato_id check can make it work but I won't recommend it --- this would cause some potential issues as i described above. I would suggest to use a dummy update on the particular row or change the originator ID to 0 ( or the one you designed for server ) after the row was inserted in the InsertRow Proc.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, October 10, 2009 11:26 PM
    Moderator
  • without looking into the big picture of your system and application, I am afraid I cannot provide much insight on this particular proc. However, what you described is right logic wise. both orig_id and the timestamp need to be changed in order to get the row sync back to the client. -- as you may notice already in the selectIncremental changes commands.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Tek_Tim Wednesday, October 14, 2009 1:14 PM
    Wednesday, October 14, 2009 8:19 AM
    Moderator

All replies

  • Hi Apurva,

    the behavior you described is the expected behavior, i.e. for incremental changes, the sync services look the originator_id and the change timestamp to see if a row should be send to the current client -- to aovid loop back for rows just updated by the clients.

    what you did to remove the originato_id check can make it work but I won't recommend it --- this would cause some potential issues as i described above. I would suggest to use a dummy update on the particular row or change the originator ID to 0 ( or the one you designed for server ) after the row was inserted in the InsertRow Proc.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, October 10, 2009 11:26 PM
    Moderator
  • Hello Yunwen,

    Thank you so much for your reply. I was thinking on the same lines and I am glad I was not on wrong path :) So what I did was to write a trigger on insert for the table. This trigger just updates the originator id to 0. However this alone didnt help. I had to remove following timestamp checks from update sync script:

    where i.create_timestamp <= @sync_last_received_anchor
          and i.update_timestamp > @sync_last_received_anchor
          and i.update_timestamp <= @sync_new_received_anchor

    After I removed these checks then it started working. Will this affect the sync process apart from making it longer as the table grows? Here is my modified update script now:

    Thanks!

    Apurva

    ALTER procedure [IFRIS_ADMIN].[sp_wildfire_incident_applyincrementalupdate]
    (
        @sync_client_id_hash int,
        @sync_last_received_anchor timestamp,
        @sync_new_received_anchor timestamp
        )
    as
    select
        i.[fire_id],
        i.[employee_id],
        i.[fire_origin_type],
        i.[cty_fips],
        i.[fire_number],
        i.[fire_name],
        i.[fire_is_false_alarm],
        i.[federal_agency],
        i.[state_id],
        i.[fire_location],
        i.[fire_comments],
        i.[fire_mutual_aid],
        i.[fire_latitude],
        i.[fire_longitude],
        i.[fire_total_acres_burned],
        i.[fire_forest_acres_burned],
        i.[fire_artificial_regen_acres],
        i.[fire_non_forest_acres],
        i.[fire_acres_estimated_by],
        i.[fire_created_user_id],
        i.[fire_created_date],
        i.[fire_edited_user_id],
        i.[fire_edited_date]
       
    from IFRIS_ADMIN.tbl_wildfire_incident AS i


    INNER JOIN
    IFRIS_ADMIN.vw_WildfireIncidentStatuses AS s
    ON i.fire_id = s.fire_id
    AND s.fire_status NOT IN ('Approved - Closed', 'Assist - Imported', 'Assist - Pending Import')


    where
         
        
          i.update_originator_id <> @sync_client_id_hash
    order by i.update_timestamp desc
    Tuesday, October 13, 2009 9:13 PM
  • without looking into the big picture of your system and application, I am afraid I cannot provide much insight on this particular proc. However, what you described is right logic wise. both orig_id and the timestamp need to be changed in order to get the row sync back to the client. -- as you may notice already in the selectIncremental changes commands.

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Tek_Tim Wednesday, October 14, 2009 1:14 PM
    Wednesday, October 14, 2009 8:19 AM
    Moderator
  • Thanks Yunwen. Appreciate your help!
    Wednesday, October 14, 2009 1:14 PM