locked
Sync Framework 2.1 not updating client correctly on deletes RRS feed

  • Question

  • I have a sync framework application.  The problem that I have is when I delete rows from two tables in the server that have a FK relationship and I sync the data only the data in the parent is deleted in the SQLCE database and the child rows are left.  This causes a constraint violation and I cannot figure out why this is happening.  I have provisioned a template that uses filters and defined multiple scopes using that filter.  I will provide the configuration for the filter template below.  The filter clauses that I use are fairly complex, maybe it is where I am having a problem.  If anyone could shed some light on the issue i would appreciate it. Here is the appropriate information.  I have taken out tables that do not have a filter clause on them and irrelevant columns to save space.  Let me know if you need more information.  Thank you in advance,

    Luke

     

    <SqlSyncProviderScopeConfiguration IsTemplate="true">
      <Adapter Name="[Locations]" GlobalName="[Locations]" TrackingTable="[Locations_tracking]" >
      <Col name="LocKey" type="uniqueidentifier" param="@P_1" pk="true" />
      <FilterParam name="@LocNo" />
      <FilterClause>[side].LocKey IN (SELECT LocKey FROM Locations WHERE DriverLocNo = @LocNo)</FilterClause>
      <FilterCol>LocKey</FilterCol>
     </Adapter>
     <Adapter Name="[Jobs]" GlobalName="[Jobs]" TrackingTable="[Jobs_tracking]" >
      <Col name="JobKey" type="uniqueidentifier" param="@P_1" pk="true" />
      <Col name="LocKey" type="uniqueidentifier" param="@P_2" />
      <FilterParam name="@LocNo" />
      <FilterClause>[side].LocKey IN (SELECT LocKey FROM Locations WHERE DriverLocNo = @LocNo)</FilterClause>
      <FilterCol>LocKey</FilterCol>
     </Adapter>
     <Adapter Name="[Tickets]" GlobalName="[Tickets]" TrackingTable="[Tickets_tracking]" >
      <Col name="TicketKey" type="uniqueidentifier" param="@P_1" pk="true" />
      <Col name="LocKey" type="uniqueidentifier" param="@P_2" />
      <FilterParam name="@LocNo" />
      <FilterClause>[side].LocKey IN (SELECT LocKey FROM Locations WHERE DriverLocNo = @LocNo)</FilterClause>
      <FilterCol>LocKey</FilterCol>
     </Adapter>
     <Adapter Name="[TicketTimes]" GlobalName="[TicketTimes]" TrackingTable="[TicketTimes_tracking]" >
      <Col name="TicketTimeKey" type="uniqueidentifier" param="@P_1" pk="true" />
      <Col name="TicketKey" type="uniqueidentifier" param="@P_2" />
      <FilterParam name="@LocNo" />
      <FilterClause>[side].TicketKey IN (SELECT TicketKey FROM Tickets WHERE LocKey IN (SELECT LocKey FROM Locations WHERE DriverLocNo = @LocNo))</FilterClause>
      <FilterCol>TicketKey</FilterCol>
     </Adapter>
     <Adapter Name="[DisposalManifest]" GlobalName="[DisposalManifest]" TrackingTable="[DisposalManifest_tracking]" >
      <Col name="ManifestKey" type="uniqueidentifier" param="@P_1" pk="true" />
      <Col name="TicketKey" type="uniqueidentifier" param="@P_4" />
      <FilterParam name="@LocNo" />
      <FilterClause>[side].TicketKey IN (SELECT TicketKey FROM Tickets WHERE LocKey IN (SELECT LocKey FROM Locations WHERE DriverLocNo = @LocNo))</FilterClause>
      <FilterCol>TicketKey</FilterCol>
     </Adapter>
    </SqlSyncProviderScopeConfiguration>
    

     

     


    LH
    Wednesday, January 26, 2011 9:10 PM

All replies

  • check out similar thread here: http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/622b37ac-4229-4d34-b093-fbae0731508b

    lets take your example of Tickets and TicketTimes

    assuming you have Ticket Id 100 and you have a corresponding TicketTimes row referencing TicketId 100

    you're filter in TicketTimes to find the TicketKey from Tickets will fail for deletes, because if TicketId 100 has already been deleted, the subquery will not return it.

    although your TicketTimes still has TicketId 100, the subquery to find all TicketIds from Tickets will not have TicketId 100 since its already deleted.

    Thursday, January 27, 2011 2:36 PM
  • Thanks for the response.  How come it deletes the tickets table before the tickettimes table?  It seems to me that it should go in reverse order.  I read the other post and would querying the tracking table be on the best approaches?

    Thanks


    LH
    Thursday, January 27, 2011 3:01 PM
  • i think the problem is not in the application of the deletes itself during sync but rather on the selection of changes. the select changes itself will not pick up the deletes for the reason above.

    even if your TicketTimes tracking table contains the deleted rows, the IN clause will fail to return the deleted rows since you're looking up the corresponding deleted row's parent in the parent base table in which case its no longer there as well as it has been deleted already.

    Thursday, January 27, 2011 3:10 PM
  • what about something like

     

    <FilterClause>[side].TicketKey IN (SELECT [side].TicketKey FROM [side].Tickets WHERE [side].LocKey IN (SELECT LocKey FROM Locations WHERE DriverLocNo = @LocNo))</FilterClause>
    
    
    
    or am i way off base.

    LH
    Thursday, January 27, 2011 3:34 PM
  • as i have mentioned earlier, the sub-query will not return the deleted rows anymore. when a row is deleted from the table, its gone. the way sync fx tracks deletions is via a delete trigger that inserts/updates the tracking table to record the deleted rows.
    Thursday, January 27, 2011 11:45 PM
  • I understand that but I went ahead and implemented a test using the filter clause above and it works on inserts, updates and deletes.  Thanks for the help
    LH
    Friday, January 28, 2011 2:03 PM