locked
How to sync relational table from SQL Compact 3.5 to SQL Server 2005 from Device RRS feed

  • Question

  • Hello

    Here is my problem :

    I already successfuly test the application from "Walkthrough: Creating an Occasionally Connected Smart Device Application" which sychronizing one table from sql compact inside the device to sql server 2005 through WCF.

    I try to sync 3 table (Customer, OrderMaster, OrderDetail) which the OrderMaster have Foreign Key of Customer(CustomerID), OrderDetail have Foreign Key of OrderMaster(OrderMasterID)

    all ID is uniqueidentifier

    I also have manualy create the table relation of 3 table above for the compact database (.sdf)

    After insert several data in all table, I try to sync the table using SyncGroup see code below

    SyncGroup syncGroup1 = new SyncGroup("Sales");

    SyncTable customerSyncTable = new SyncTable("Customer");
    customerSyncTable.CreationOption =
    TableCreationOption.UploadExistingOrCreateNewTable;
    customerSyncTable.SyncDirection =
    SyncDirection.UploadOnly;
    customerSyncTable.SyncGroup = syncGroup1;
    syncAgent.Configuration.SyncTables.Add(customerSyncTable );

    SyncTable orderMasterSyncTable = new SyncTable("OrderMaster");
    orderMasterSyncTable.CreationOption =
    TableCreationOption.UploadExistingOrCreateNewTable;
    orderMasterSyncTable.SyncDirection =
    SyncDirection.UploadOnly;
    orderMasterSyncTable.SyncGroup = syncGroup1;
    syncAgent.Configuration.SyncTables.Add(orderMasterSyncTable );

    SyncTable orderDetailSyncTable = new SyncTable("OrderDetail");
    orderDetailSyncTable .CreationOption =
    TableCreationOption.UploadExistingOrCreateNewTable;
    orderDetailSyncTable .SyncDirection =
    SyncDirection.UploadOnly;
    orderDetailSyncTable .SyncGroup = syncGroup1;
    syncAgent.Configuration.SyncTables.Add(orderDetailSyncTable );

    After synchronize complete, in the SQL Server 2005, only Customer and OrderMaster table have data and OrderDetail is Empty.

    Is there are other way to sync the relational table instead of using SyncGroup

    Thanks for the future advice

    Wednesday, February 17, 2010 7:16 AM

Answers

  • how is the table order for the syncAdapters ?

    can you enable trace for the sync and check the trace file to see if the rows in OrderDetails got sent to the server ?

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by puterasyaf Wednesday, February 17, 2010 9:42 AM
    Wednesday, February 17, 2010 8:11 AM

All replies

  • how is the table order for the syncAdapters ?

    can you enable trace for the sync and check the trace file to see if the rows in OrderDetails got sent to the server ?

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by puterasyaf Wednesday, February 17, 2010 9:42 AM
    Wednesday, February 17, 2010 8:11 AM
  • do you mean the sequence of class reside in ..Cache.Designer.cs which similar like this :
    public partial class ..CacheServerSyncProvider : Microsoft.Synchronization.Data.Server.DbServerSyncProvider { ..
    public partial class CustomerSyncAdapter : Microsoft.Synchronization.Data.Server.SyncAdapter { ..}
    public partial class OrderMasterSyncAdapter : Microsoft.Synchronization.Data.Server.SyncAdapter { ..}
    public partial class OrderDetailSyncAdapter : Microsoft.Synchronization.Data.Server.SyncAdapter { ..}

    and how to enable trace for the sync?
    I have check the SQL profiler ,Insert statement for OrderDetails is apear:

    declare @p12 int
    set @p12=0
    exec sp_executesql N'INSERT INTO dbo.OrderDetails ...
    Wednesday, February 17, 2010 8:37 AM
  • Can you execute the insert statement itself you got from SQL Profiler and see if it inserts the row?

    Wednesday, February 17, 2010 9:13 AM
  • Thank Yunwen Bai, I rearange the order of the Cache Table in Configure Data Synchronization dialog, and it work fine.

    the insert statement is appear in profiler but is before the parent table insert, after the arrange the table order the sync work fine

    Thanks
    semoga dirahmatiNya
    Wednesday, February 17, 2010 9:48 AM
  • how is the table order for the syncAdapters ?

    can you enable trace for the sync and check the trace file to see if the rows in OrderDetails got sent to the server ?

    thanks
    Yunwen
    This posting is provided "AS IS" with no warranties, and confers no rights.

    Hi Yunwen,

    just for added clarification:

    So which one determines the order of processing of the tables? is it the order you they are added to the SyncAdapters collection or the order they are added to SyncTables collection?
    Wednesday, February 17, 2010 1:01 PM
  • Thank Yunwen Bai, I rearange the order of the Cache Table in Configure Data Synchronization dialog, and it work fine.

    the insert statement is appear in profiler but is before the parent table insert, after the arrange the table order the sync work fine

    Thanks
    semoga dirahmatiNya

    the wizard lists the tables in alphabetical order? :)
    Wednesday, February 17, 2010 1:03 PM
  • the wizard is automatically sort alphabetically, but we can rearange using the arrow
    semoga dirahmatiNya
    Wednesday, February 17, 2010 2:14 PM