locked
Sync dissimilar tables RRS feed

  • Question

  • Hello,

    Have a need to sync (initial and ongoing) 2 databases:  One is a large ERP legacy database (Sql2008), the second, is a much smaller database which will house a subset of data from the ERP database (Sql2008), I will call this the 'Mobile' database.  From this 'Mobile' database, data will be filtered and 'synced' to multiple mobile devices on an ongoing basis (this will be a bi-directional sync).  I plan on using the Sync Framework to handle this latter sync, as database structures are identical.

    Here is my problem:  Table names and column names in the ERP database are slightly different than those in the Mobile database.  For Example:

    Database     TableName     ColumnName
    -----------     --------------   ----------------
    ERP             Customer       CsName
                                            CsAddr1
                                            CsAddr2

    Mobile          Customers      CustomerName
                                             Addr1
                                             Addr2

    My goal is to write a Window Service that will fire on a timed basis (say once per hour) to perform a bi-directional sync on these 2 databases.

    I have attempted to write a sync service using Stored Procedures to handle these differences, but seems to be an inherent problem with the table names being named differently.  Error reported is at a level that I can't seem to trace. 

    Does anyone know if this scenario is even possible?  I'm new to the framework, and this is my first 'real' project using the tool set.

    Thanks in advance to anyone who may be able to help.
    • Moved by Hengzhe Li Friday, April 22, 2011 2:58 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, June 11, 2009 9:49 PM

Answers

  • Hi,

    The DbSyncProvider in Sync Service ADO.net can support this kind of column name mapping with DbSyncAdapter.ColumnMappings. Please take a look at the section "Peer-to-Peer Synchronization" in link http://msdn.microsoft.com/en-us/library/bb726046.aspx. There is a sample for column mapping as well. It is mentioned in this link too, and you can get it after installing the syncSDK.msi.

    For the SQL Server to SQLCE deployment, you need to setup the ColumnMapping in the SQL Server provider side, let the SQL Server column name be the local name, and the CE column name be the remote name or global name.

    Thanks,
    Dong
    • Marked as answer by Slang7 Monday, June 15, 2009 1:43 PM
    Friday, June 12, 2009 8:06 PM
    Moderator

All replies

  • Hi,

    The DbSyncProvider in Sync Service ADO.net can support this kind of column name mapping with DbSyncAdapter.ColumnMappings. Please take a look at the section "Peer-to-Peer Synchronization" in link http://msdn.microsoft.com/en-us/library/bb726046.aspx. There is a sample for column mapping as well. It is mentioned in this link too, and you can get it after installing the syncSDK.msi.

    For the SQL Server to SQLCE deployment, you need to setup the ColumnMapping in the SQL Server provider side, let the SQL Server column name be the local name, and the CE column name be the remote name or global name.

    Thanks,
    Dong
    • Marked as answer by Slang7 Monday, June 15, 2009 1:43 PM
    Friday, June 12, 2009 8:06 PM
    Moderator
  • Thank you Dong, looks like that will do the trick. Scott
    Monday, June 15, 2009 1:43 PM