none
Question about sync using existing tables RRS feed

  • Question

  • Hello,

     I was trying to apply a sync scope to an existing SQLExpress 2008 and SQLCe databases that were provided to me with the same data in each table. The scope appeared to be applied successfully, but changes to the one DB wouldn't be copied on the other database unless I had created the record after the initial sync.

      I resolved the issue by removing all the data from the SQL CE and letting the initial sync copy it over, but I was wondering if this is natural behaviour?

    Thanks, Hank

    Tuesday, March 22, 2011 3:28 PM

Answers

  • if you have existing data, try setting SetPopulateTrackingTableDefault during provisioning.

    i.e.,

    clientProvision.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);
    serverProvision.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);

    take note that if you have existing data on both databases, you may encounter conflicts as the client tries to upload its rows to the server and vice versa.

     

    Tuesday, March 22, 2011 3:38 PM
    Moderator
  • if you managed to trap the event, then either there's an error or a conflict. check this out on how to handle conflicts: http://msdn.microsoft.com/en-us/library/cc761628(v=SQL.110).aspx

    a couple of things you can check:

    does the sql express tracking tables get populated after provisioning?

    is the select_changes returning anything during the sync?

    check the applyingchanges event on the ce side if its receiving the change dataset.

     

    • Marked as answer by HankAnzis Thursday, March 24, 2011 1:30 PM
    Thursday, March 24, 2011 4:32 AM
    Moderator

All replies

  • if you have existing data, try setting SetPopulateTrackingTableDefault during provisioning.

    i.e.,

    clientProvision.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);
    serverProvision.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);

    take note that if you have existing data on both databases, you may encounter conflicts as the client tries to upload its rows to the server and vice versa.

     

    Tuesday, March 22, 2011 3:38 PM
    Moderator
  • Thanks JuneT,

      I didn't see that method for the clientProvision. I did set it for the server, but had no change in the behaviour. Conflicts shoudn't be an issue since the data is always going to the SqlCe database,

    Hank


    Tuesday, March 22, 2011 5:27 PM
  • sorry, didnt notice the CE part. you dont have the option to setpopulatetrackingtable on sqlcesyncscopeprovisioning.

    so your problem is that the initial set of data on SqlExpress doesnt get downloaded unless the record is updated/inserted after provisioning?

    would you mind posting the provisioning part?

    Tuesday, March 22, 2011 11:30 PM
    Moderator
  • Hi JuneT. IYes, the initial CE is not overwritten (I changes some to make sure)and when I make changes to those records on SQLExpress and do a sync, the changes aren't loaded to the CE, even though the stats do show the rows in UploadChanges.Applied and UploadChanges.Total. If I start wiht an empty SQLCe database and let the initial sync do the download, these changes are reflected in SQLCe. Here is the code:

     

      public SqlSyncProvider ConfigureSqlSyncProviderToClient(string hostName)
    
      {
    
       SyncServerSQL svrConnString = new SyncServerSQL();
    
    
    
       SqlSyncProvider provider = new SqlSyncProvider();
    
       provider.ScopeName = SyncUtils.ScopeNameToClient;
    
       provider.Connection = new SqlConnection(svrConnString.SqlConnString);
    
    
    
       DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(SyncUtils.ScopeNameToClient);
    
    
    
       SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((System.Data.SqlClient.SqlConnection)provider.Connection);
    
    
    
       if (!serverConfig.ScopeExists(SyncUtils.ScopeNameToClient))
    
       {
    
        scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_001", (System.Data.SqlClient.SqlConnection)provider.Connection));
    
        scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_002", (System.Data.SqlClient.SqlConnection)provider.Connection));
    
        scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_003", (System.Data.SqlClient.SqlConnection)provider.Connection));
    
        scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_004", (System.Data.SqlClient.SqlConnection)provider.Connection));
    
        scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("Table_005", (System.Data.SqlClient.SqlConnection)provider.Connection));
    
        serverConfig.PopulateFromScopeDescription(scopeDesc);
    
        serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
        serverConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);
    
        serverConfig.Apply();
    
       }
    
      }
    
    
    
    
    
      public SqlCeSyncProvider ConfigureCESyncProviderToClient(SqlCeConnection sqlCeConnection)
    
      {
    
       SqlCeSyncProvider provider = new SqlCeSyncProvider();
    
       provider.ScopeName = SyncUtils.ScopeNameToClient;
    
       provider.Connection = sqlCeConnection;
    
    
    
       SyncServerSQL svrConnString = new SyncServerSQL();
    
       SqlConnection serverConn = new SqlConnection(svrConnString.SqlConnString);
    
    
    
       DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(SyncUtils.ScopeNameToClient, serverConn);
    
       SqlCeSyncScopeProvisioning serverConfig = new SqlCeSyncScopeProvisioning(sqlCeConnection, scopeDesc);
    
       if (!serverConfig.ScopeExists(SyncUtils.ScopeNameToClient) 
    
       {
    
        SqlCeSyncScopeDeprovisioning clientSqlCeDepro = new SqlCeSyncScopeDeprovisioning(sqlCeConnection);
    
        try
    
        {
    
         clientSqlCeDepro.DeprovisionStore();
    
        }
    
        catch
    
        {
    
        }
    
        serverConfig.Apply();
    
       }
    
       provider.BeginSnapshotInitialization += new EventHandler<DbBeginSnapshotInitializationEventArgs>(provider_BeginSnapshotInitialization);
    
       provider.EndSnapshotInitialization += new EventHandler<DbEndSnapshotInitializationEventArgs>(provider_EndSnapshotInitialization);
    
       provider.BatchApplied += new EventHandler<DbBatchAppliedEventArgs>(provider_BatchApplied);
    
       provider.BatchSpooled += new EventHandler<DbBatchSpooledEventArgs>(provider_BatchSpooled);
    
       return provider;
    
      }
    
    
    
    
    Thanks,Hank
    
    

    Wednesday, March 23, 2011 1:10 PM
  • if the local provider is your sql express and the remote provider is the sql ce, try subscribing to the sql ce side's ApplyChangeFailed event to see if there are any errors or conflicts when initially synching data from sql express to sql ce.

    if both databases have existing data during provisioning and same Primary key values, chances are it's encountering conflicts when applying the data from sql express on the sql ce side.

    Wednesday, March 23, 2011 3:14 PM
    Moderator
  • Thanks JuneT. I will do that, but wouldn't the statistics show the failed rows in it's count?

    P.S. I did trap the event, but got no errors. Is there a white paper or something that could show me how the sync framework does its determinations?

    Wednesday, March 23, 2011 3:16 PM
  • if you managed to trap the event, then either there's an error or a conflict. check this out on how to handle conflicts: http://msdn.microsoft.com/en-us/library/cc761628(v=SQL.110).aspx

    a couple of things you can check:

    does the sql express tracking tables get populated after provisioning?

    is the select_changes returning anything during the sync?

    check the applyingchanges event on the ce side if its receiving the change dataset.

     

    • Marked as answer by HankAnzis Thursday, March 24, 2011 1:30 PM
    Thursday, March 24, 2011 4:32 AM
    Moderator
  • The sql express tracking tables were populated on the sqlExpress side but not the sqlCE side. The selectingChanges and ApplyChange events didn't fire.

    I repeated this experiment on an empty CE database and the only difference was the sqlCE side had their tracking tables populated.

    I'm doing the syncronize as  

    synchronizationHelper.SynchronizeProviders( SQLExpressProvider, CedestinationProxy, SyncDirectionOrder.Upload)

    Could that be causing my problems?

    Thanks,Hank

    • Proposed as answer by Peter Kellner Sunday, November 13, 2011 8:57 PM
    Thursday, March 24, 2011 1:12 PM
  • I'm changed the synchronize to

     synchronizationHelper.SynchronizeProviders( CeProvider, SQLExpressdestinationProxy, SyncDirectionOrder.Download)
    
    
    
    

    and it returned the ApplyChangeFailedEvent. Since this is only one direction, I set the action to ReplyWithForceWrite and everything works.

    Thanks for all your help JuneT.


    Thursday, March 24, 2011 1:33 PM