locked
MS Sync: Table in multiple sync scopes. RRS feed

  • Question

  • Hello

    I try to synchronize data between a SQL Express and a SQL Compact database. 

    I have created a synchronization scope that includes all three tables: Sync_Scope1 that uploads data from client to server (tables A, B, C, D).

    I want to add a new sync scope that Sync_Scope2 (that includes 2 tables: A, B that exist in Sync_Scope1) and which downloads data from server to client.

    Is this possible?

    In [scope_info] table I can see both my scopes but I receive this error message when I try to download data from server:

    The transaction can not be aborted if there are any opened cursors in the scope of this transaction. Make sure all data readers / result sets are explicitly closed before aborting the change.


    sb_angela

    Monday, March 19, 2012 11:15 AM

All replies

  • are you running concurrent syncs?

    yes, its possible to have the same table on more than one scope. the columns in the second and subsequent scope though has to much the columns included in the first scope.

    is Scope 1 for a different client than Scope 2? if its not, why not create 1 scope for A&B with sync direction set to Bidirectional, then another scope for C&D for Upload only?

    Monday, March 19, 2012 1:28 PM
  • First time I start each client (and create the local database) I need to download some data from central database.: table A, B.

    Afterwards the client will change/add new values in the tables A, B and I want to upload these new data to the central server. The upload scope contains other tables beside A and B.

    I have created a test application and as soon as I have this situation: 2 tables in 2 different scopes I receive this error message:

    The transaction can not be aborted if there are any opened cursors in the scope of this transaction. Make sure all data readers / result sets are explicitly closed before aborting the change.


    sb_angela

    Monday, March 19, 2012 1:40 PM
  • try sync framework tracing on to see where the error is coming from... looks like its encountering an error and is aborting the transaction but the abort transaction cannot be done as well.

    btw, have a look at this link on some considerations when grouping tables in scopes:

    Sync Framework Scope and SQL Azure Data Sync Dataset Considerations

    Monday, March 19, 2012 1:51 PM
  • I have enabled the framework tracking and this is what I received.

    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:253, ----- Enumerating Changes for Scope "PosFloatData" -----
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:253,          Source Scope Id: 8c67a9d8809049aeb51df4abff7f5015
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:253,     Destination Scope Id: b2788af4eea44a13953586f163c7ad09
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:253,    Change Tracking Model: Decoupled
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:253,    ----- Table "vcrFloat" -----
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,       Inserts: 1
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,       Deletes: 0
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,       Updates: 0
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,       Changes Enumerated: 1
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,    --- End Table "vcrFloat" ---
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393, 
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,    ----- Table "vcrFloatAmount" -----
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,       Inserts: 5
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,       Deletes: 0
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,       Updates: 0
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,       Changes Enumerated: 5
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:393,    --- End Table "vcrFloatAmount" ---
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409, 
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409,    ----- Table "vcrReceiptNumberSequence" -----
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409,       Inserts: 1
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409,       Deletes: 0
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409,       Updates: 0
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409,       Changes Enumerated: 1
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409,    --- End Table "vcrReceiptNumberSequence" ---
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409, 
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409, --- End Enumerating Changes for Scope "PosFloatData" ---
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409, 
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:409, Committing transaction
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:456, ----- Applying Changes for Scope "PosFloatData" -----
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:456,         Source Scope Id: 8c67a9d8809049aeb51df4abff7f5015
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:456,    Destination Scope Id: b2788af4eea44a13953586f163c7ad09
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:456, 
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:456,    ----- Deletes for Table "vcrReceiptNumberSequence" -----
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,       0 Deletes Applied
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,    --- End Deletes for Table "vcrReceiptNumberSequence" ---
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471, 
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,    ----- Deletes for Table "vcrFloatAmount" -----
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,       0 Deletes Applied
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,    --- End Deletes for Table "vcrFloatAmount" ---
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471, 
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,    ----- Deletes for Table "vcrFloat" -----
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,       0 Deletes Applied
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,    --- End Deletes for Table "vcrFloat" ---
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471, 
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:471,    ----- Inserts for Table "vcrFloat" -----
    ERROR  , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:487, Caught exception while applying changes: System.NullReferenceException: Object reference not set to an instance of an object.
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeResultSetHandler.BuildDataTableFromResultSet()
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeResultSetHandler..ctor(SqlCeSyncAdapter adapter, SqlCeTransaction transaction)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeChangeHandler.PrepareForAdapter(DbSyncAdapter adapter, DbSyncTableProgress tableProgress)
       at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChangesInternal(DbSyncScopeMetadata scopeMetadata, IDbTransaction transaction, FailedDeleteDelegate_type failedDeleteDelegate, DataSet dataSet, ChangeApplicationType applyType)
       at Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, IDbTransaction applyTransaction, DataSet dataSet, DbSyncSession DbSyncSession, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
    ERROR  , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:487, Rolling back application transaction.
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:503,    EndSession() called on Provider SqlCeSyncProvider, Microsoft.Synchronization.Data.SqlServerCe, Version=3.1.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
    INFO   , ConsoleApplication1.vshost, 9, 03/19/2012 13:58:38:503,    EndSession() called on Provider SqlSyncProvider, Microsoft.Synchronization.Data.SqlServer, Version=3.1.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91


    sb_angela

    Monday, March 19, 2012 2:02 PM
  • anything special with table vcrFloat? is sync tracing in verbose mode?
    Monday, March 19, 2012 2:42 PM
  • The framework tracing is in verbose mode, the above text is the only error I received.

    I receive the same error with all the tables I have tried. If I change and keep the tables in a single sync scope it works, but if I want to have them in 2 scope, I receive the below error.

    Caught exception while applying changes: System.NullReferenceException: Object reference not set to an instance of an object.
     

    Here it states this should work: Scopes can be distinct or they can overlap with each other. Two scopes overlap if they share common data between them.


    sb_angela

    Monday, March 19, 2012 2:51 PM
  • yes, multiple scopes against the same table is supported but the scopes should have the same set of columns.

    for example, scope 1 has table 1 with column 1 and column 2

    scope 2 can have table 1 again, but the columns should still be columns 1 and 2.

    you can't have a scope where in scope 1, you have columns 1 and 2 and in scope 2, you have columns 1, 2 and 3.

    Monday, March 19, 2012 3:04 PM
  • one other thing to test, i think you're setup of having the same tables on different scopes and synching the same databases might cause a sync loop.

    you download changes in scope 1 and that works fine.

    scope 2 detects changes to upload and detects the changes downloaded in scope 1

    scope 2 reuploads the same rows you downloaded in scope 1

    scope 1 detects the changes uploaded by scope 2 and so on...

    try testing it, by looking at the rows applied. if you sync scope 1 and sync scope 2 without any changes and the sync on scope 2 shows rows where uploaded, you have a sync loop

    Monday, March 19, 2012 3:25 PM
  • Hello

    Yes. I want to sync same tables (same columns) on different scopes on the same databases. 

    I tried with a single row: download it from central server, change it locally and upload it again and it fails. Same error as above.

    If I have this sync loop, is there a way to avoid it?


    sb_angela


    • Edited by sb_angela Tuesday, March 20, 2012 8:07 AM
    Tuesday, March 20, 2012 6:31 AM
  • can you subscribe to the ApplyChangesFailed  event and see if it show something there?
    Tuesday, March 20, 2012 8:51 AM
  • I have subscribed to ApplyChangesEvent but the error appears before this event is triggered. It fails when ApplyingChanges is triggered with the error listed above.

    sb_angela

    Tuesday, March 20, 2012 10:59 AM
  • are you sure you are synching the correct sync scope pairs? scope 1 to scope 1, scope 2 to scope 2?

    can you post the code snippets for provisioning and synching?

    Tuesday, March 20, 2012 11:31 AM
  • This is the provisioning I use for central server:

    private Dictionary<string, DbSyncScopeDescription> _syncScopes;
    public Dictionary<string, DbSyncScopeDescription> SyncScopes
    {
        get
        {
            if (_syncScopes == null)
                InitializeSyncScopes();
            return _syncScopes;
        }
    }
    
    public void InitializeSyncScopes()
    {
        _syncScopes = new Dictionary<string, DbSyncScopeDescription>();
    
        foreach (var item in SyncData.PosSyncTableList)
        {
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(item.Key);
    
            foreach (var tableName in item.Value)
            {
                DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, sqlConnection);
                scopeDesc.Tables.Add(tableDesc);
            }
            _syncScopes.Add(item.Key, scopeDesc);
        }
    }
    
    public DbSyncScopeDescription GetScopeDescription(string scopeName)
    {
        DbSyncScopeDescription scopeDescription = new DbSyncScopeDescription(scopeName);
        foreach (var tableName in SyncData.PosSyncTableList[scopeName])
        {
            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(tableName, sqlConnection);
            scopeDescription.Tables.Add(tableDesc);
        }
        return scopeDescription;
    }
    
    /// <summary>
    /// Database needs to be provisioned, before starting the sync process
    /// </summary>
    public void ProvisionDatabase()
    {
        foreach (var item in SyncScopes)
        {
            string scopeName = item.Key;
    
            SqlSyncProvider provider = new SqlSyncProvider();
            provider.ScopeName = scopeName;
            provider.Connection = sqlConnection;
                   
            DbSyncScopeDescription scopeDesc = SyncScopes[scopeName];
            SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((SqlConnection)provider.Connection);
    
            //determine if this scope already exists on the server and if not go ahead and provision
            if (serverConfig.ScopeExists(scopeName) == false)
            {
                scopeDesc = GetScopeDescription(scopeName);
                serverConfig.PopulateFromScopeDescription(scopeDesc);
                serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                serverConfig.Apply();
            }
        }
    }

    And for compact database using same scopes:

        public void ProvisionDatabase()
            {
                foreach (var item in SyncScopes)
                {
                    string scopeName = item.Key;
    
                    SqlCeSyncProvider provider = new SqlCeSyncProvider();
                    provider.ScopeName = scopeName;
                    provider.Connection = _sqlCeConnection;
                    
                    DbSyncScopeDescription scopeDesc = SyncScopes[scopeName];
    
                    SqlCeSyncScopeProvisioning serverConfig = new SqlCeSyncScopeProvisioning((SqlCeConnection)provider.Connection);
                    
                    if (serverConfig.ScopeExists(scopeName) == false)
                    {
                        scopeDesc = GetScopeDescription(scopeName);
                        serverConfig.PopulateFromScopeDescription(scopeDesc);
                        serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
                        serverConfig.Apply();
                    }
                }
            }


    sb_angela

    Wednesday, March 21, 2012 1:07 PM
  • on your server, can you confirm how many selectchanges sp do you have against each table that is part of two scopes?

    you should have two selectchanges sp for a table that is part of scope 1 and scope 2

    Wednesday, March 21, 2012 1:19 PM
  • I don't have 2. For each table that is part of two scopes, I have only one _selectchanges store procedures. Can I enforce the creation of the other one?

    sb_angela

    Thursday, March 22, 2012 9:50 AM
  • when you provision an additional scope you should use SetCreateProceduresForAdditionalScopeDefault method

    see: Provisioning additional scope using Sync Framework’s sync scope provisioning

    Thursday, March 22, 2012 10:04 AM