locked
Syncronization fails when not all columns from a table are included in the scope RRS feed

  • Question

  • The observable behavior during SQL2005 to SQL2005 is as the following:

    1. If no columns are specified as a parameter to GetDescriptionForTable, or ALL columns the table contains are present in the parameter list, unidirectional syncronization works all right, no problem.

    2. If at least one column is excluded from the list, the syncronization doesn't work as expected. It runs with no exception thrown, but nothing happens to the datasets, silently. They remain unchanged.

    I've tried this a dozen of times, allways cleaning up the databases and re-provisioning them from the scratch.

    The provisioning code is usual and trivial. The commented line is the focus of the question: if the comment present, the sync doesn't happen

     

    var sqlSyncScopeProvisioning = new SqlSyncScopeProvisioning();
    var scope = new DbSyncScopeDescription(scopeName);
    
    var cols = new Collection<string>
                 {
                   "Id", "GlobalId", "Version",
                   "Region", "Type", "Status", "ModifiedDate",
                  //"IncomingSessionId", "OutgoingSessionId"
                 };
    var desc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Table", cols, sqlConnection);
           
    scope.Tables.Add(desc);
    sqlSyncScopeProvisioning.PopulateFromScopeDescription(scope);
    sqlSyncScopeProvisioning.SetCreateTableDefault(DbSyncCreationOption.Skip);
    if (syncOptionSkip)
    {
      sqlSyncScopeProvisioning.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);
      sqlSyncScopeProvisioning.SetCreateTriggersDefault(DbSyncCreationOption.Skip);
      sqlSyncScopeProvisioning.SetCreateProceduresDefault(DbSyncCreationOption.Skip);
      sqlSyncScopeProvisioning.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
    }
    sqlSyncScopeProvisioning.Apply(sqlConnection);

    Since the code is basically copy-pasted from MSDN, it's assumed to be tested and working. However, it's not the case as I can see.

    Could someone explain what's the problem here?

    Thanks in advance!

     

    Tuesday, June 8, 2010 7:11 AM

Answers

  • Just tried out using a subset of columns in a small application I've written and everything works fine.

    I do have a few things you could check that may help determine the problem:

    • Are the columns that you are removing from the collection nullable?  If they are not specified in the column list and the columns are not nullable, then inserts will obviously fail.
    • How are you checking for failures?  Have you got an event handler for the ApplyChangeFailed event and checking for errors?  You can get an insert failing, for example, with no exception thrown, but this event will fire with error info.
    • You can check the provisioning T-SQL that is generated to see if that is as expected.  As well as calling Apply(), call Script() and look at the T-SQL in the string.  In particular, take a look at the "Table_insert" and "Table_update" stored procedures to see if the column lists match the columns listed in the collection.

    Let me know how it goes.

    Regards, Mark

    • Marked as answer by JSwano Wednesday, June 9, 2010 8:09 AM
    Tuesday, June 8, 2010 3:36 PM

All replies

  • Just tried out using a subset of columns in a small application I've written and everything works fine.

    I do have a few things you could check that may help determine the problem:

    • Are the columns that you are removing from the collection nullable?  If they are not specified in the column list and the columns are not nullable, then inserts will obviously fail.
    • How are you checking for failures?  Have you got an event handler for the ApplyChangeFailed event and checking for errors?  You can get an insert failing, for example, with no exception thrown, but this event will fire with error info.
    • You can check the provisioning T-SQL that is generated to see if that is as expected.  As well as calling Apply(), call Script() and look at the T-SQL in the string.  In particular, take a look at the "Table_insert" and "Table_update" stored procedures to see if the column lists match the columns listed in the collection.

    Let me know how it goes.

    Regards, Mark

    • Marked as answer by JSwano Wednesday, June 9, 2010 8:09 AM
    Tuesday, June 8, 2010 3:36 PM
  • Many thanks, Mark. The answer is simple. :)

    The first guess was the key to the resolution. I've made the columns nullable and sync went successfully.

    Wednesday, June 9, 2010 8:13 AM