none
Syncronizing Delete operation is not working RRS feed

  • Question

  • Hello,

             I am syncronizing the SQL2005 databases using SyncFx2.1. And now caught in a trouble that the Delete operation is no being syncronized for the table that doesn't have direct field of the Filter.

    e.g. I have two tables A(Auid,A1,A2, SiteUid) and B(Auid, B1,B2). A is the base table ad the table B holds the reference to the table A. The relation is one-to-one. In order to syncronize these table to clients based on the specific SiteUid, I have defined filters as :

    A : [side].SiteUid = 'xxx'

    B : [side].Auid IN (SELECT Auid FROM A WHERE SiteUid = 'xxx')

    This works well for INSERTs and UPDATEs but not for DELETEs of table B.

    I found that the SyncFx is sending the delete operations only for the table A and not for the B. And while deleting the row from table A it throws the error "The DELETE statement conflicted with the REFERENCE constraint "FK_B_A". This errors keeps happening in successive sync cycles too.

    The order of syncronization is A and then B.

    Does anyone encountered such type of issue? Please help me so that I can force the SyncFx to send the deletes for table B too.

    Thanks.

    -Ajinath


    Ajinath
    Friday, January 21, 2011 11:47 AM

All replies

  • Can you clarify who is initially deleting the item from A to begin with ? If the initially delete from A is made by your application without the FK-dependent items getting deleted from B, you should see this behavior. I think Sync Framework would not enforce the datamodel (B refers to A) for your application.

    So knowing how your application is performing deletes would help to understand the problem.

    Sameer

    Friday, January 21, 2011 5:19 PM
  • ajinath,

    your filter in B will not work for deletes. in B, the condition [side].Auid IN (SELECT Auid FROM A WHERE SiteUid = 'xxx') will not return deleted rows since the clause SELECT Auid FROM A WHERE SiteUid = 'xxx' will no longer find the deleted row in your base table.

    have u tried filtering on A tracking table instead of the base table?

    Sunday, January 23, 2011 2:13 PM
    Moderator
  • Thanks JuneT. I have tried using the A tracking table and it works for the tables with One-to-One relation.

    But in case of One-to-Many relationship it does not work. e.g. I have tables A (AUid, A1, A2, SiteUid), B (BUid, AUid, B1, B2), C (CUid, BUid, C1, C2)

    The filters I have used are :

    A : [side].SiteUid = 'xxx'

    B : [side].AUid IN (SELECT AUid FROM A WHERE SiteUid = 'xxx')

    C : [side].BUid IN (SELECT BUid FROM B INNER JOIN A ON A.AUid = B.AUid WHERE A.SiteUid = 'xxx')

    The above criterias works well for INSERTs, UPDATES but fails for Delete for the tables B & C.

    If I modify the filter for table B as :  [side].AUid IN (SELECT AUid FROM A_tracking WHERE SiteUid = 'xxx'), it works for DELETE too. But for table C I dont see any way for using _tracking table in the Filter :(.

    Is there any way that I can include the deletes of table C too without modifying the table structure ?

    I have checked the _selectchanges SP and see that there is condition which is ORed with the the actual filter criteria is OR ([side].[sync_row_is_tombstone] = 1
     AND ([side].[update_scope_local_id] = @sync_scope_local_id OR [side].[update_scope_local_id] IS NULL)  AND [side].[BUid] IS NULL )

    What this condition is for? Why the last AND is required i.e. the AND [side].[BUid] IS NULL for for table C?

    Please help :(

     

    -Ajinath

     


    Ajinath
    Tuesday, January 25, 2011 11:41 AM
  • I got the way for table C (for the previous post) with the Filter : [side].BUid IN (SELECT BUid FROM B_tracking bTr INNER JOIN A_tracking ATr ON aTr.AUid = bTr.AUid WHERE aTr.SiteUid = 'xxx').

    But now caught up in another condition like :

    I have tables => A (AUid, A1, A2, SiteUid), B (BUid, AUid, B1, B2, CUid), C (CUid, C1, C2)

    Here the Filters I have used as

    A: [side].SiteUid = 'xxx'

    B: [side].AUid IN (SELECT AUid FROM A WHERE SiteUid = 'xxx')

    C: [side].CUid IN (SELECT CUid FROM B INNER JOIN A ON B.AUid = A.AUid WHERE A.SiteUid = 'xxx')

    where I can change the B as : [side].AUid IN (SELECT AUid FROM A+tracking WHERE SiteUid = 'xxx'),

    but for C the filter : [side].CUid IN (SELECT CUid FROM B_tracking Btr INNER JOIN A_tracking Atr ON Atr.AUid = Btr.AUid WHERE Atr.SiteUid = 'xxx') as CUid is not in the B_tracking table.

    Please help me .

     

    -Ajinath

     

     


    Ajinath
    Tuesday, January 25, 2011 12:23 PM
  • let's get back up a bit:

    Ajinath, do you use the provisioning class to provision the database ?  it seems to be you are trying to write your own queries. or you are using the dbsyncprovider or the dbserverprovider. if you are syncing only SqlServer databases, you can use the sqlsyncprovider and the provisioing routines so that you won't need to worry about this at the query level.

    Now back to your select delete query, all the queries should be against its own side table, if you have your own tracking system setup correctly, i.e. triggers, side tables etc. any pariticular reason you need to use the subquery for C or B ?

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 26, 2011 8:50 PM
    Moderator
  • Hi Yunwen,

                 Thanks for looking into this. As the sync is between SqlServer databases only, i am using SqlSyncProvider and Provisioning routines to provision the databases.

    I am using the subqueries for the tables B & C because I dont have the SiteUid in these tables. And the SiteUid is the field used to distinguish the site specific records.

     

    -Ajinath


    Ajinath
    Thursday, January 27, 2011 3:55 AM
  • Thanks for sharing the info about the case. what you are trying is essentially the so called "logical record" where two or more rows the tables are related with each other and shall be synced all together.

    as you have experienced, even with two or three tables, the design is already getting complex. I would suggest you re-design the tables to have the filtere columns hence to avoid the complex queries to select the changes.

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, January 27, 2011 6:46 AM
    Moderator
  • ajinath,

    have you tried adding B.CUid as a filter column on B to force it to add it to the tracking table? or make B's Primary Key = AUid + BUid + CUid?

    Thursday, January 27, 2011 2:24 PM
    Moderator
  • ajinath,

    I have a similar situation, and have solved the problem.

    As you discovered, part of the solution is that child tables must employ a filter back to the parent table through the tracking table.  The more complex the relationships the harder this is to do.  For Table C in your example, the filter is going to have to go through the tracking table for 'B' to the tracking table for 'A'.  The only columns you add to FilterColumns are those in the table being filtered ... not those in the tracking tables that you are referencing.

    The other side of the coin is that you have to list the tables to the provisioning code in INSERT order.  Thus, all of the parent tables have to appear before the child tables (A before B before C in your example).  Sync will automatically reverse this when performing delete syncs.

    Those two items should get you past your problems.

    -Kevin

    Thursday, March 3, 2011 9:21 PM