Sql Compact 3.5 syncronization fails

Question

I have an application that syncs between 2 database: the central one is hosted on a Sql Server 2008 and the local one is a Sql Compact 3.5 database.

From time to time I need to upgrade the schema of one of the tables included in a sync scope and I do not want to deprovision and provision again, so I used this approach and for Sql Compact I have something like this:

public void UpdateProvisionSchema(string connString, string scopeName, string newConfigData)
{
    _log.Verbose("UpdateProvisionSchema: " + scopeName);

    try
    {
        string query = string.Format("SELECT [scope_config_id] FROM [scope_info] WHERE [sync_scope_name]='{0}'", scopeName);
        Guid config_id = (Guid)SqlCompactManager.ExecuteScalar(connString, query);

        string updateCommand = string.Format("UPDATE scope_config SET [config_data]='{0}' WHERE [config_id] ='{1}'", newConfigData, config_id);
        SqlCompactManager.ExecuteCommand(connString, updateCommand);
    }
    catch (Exception ex)
    {
        _log.Error(ex, string.Format("Failed to upgrade schema for scope {0}", scopeName));
        throw new Exception(string.Format("Update of provisioned schema failed for scope: {0}", scopeName));
    }
}

and I manually change the config scope for my table. I think the scope config I send is the correct one, because I generate it by provisioning an empty table.

I have just added a column to a table included in a sync scope (its data is only downloaded from central database to local database) and now I receive an error on ApplyChangeFailed.

It seems that in the Sql Compact database, the table included in the sync scope with the problem, has this column: __sysTrackingContext which does not exist on the central table.

First time I have created the tables and provisioned them and then I added 2 new columns (BusinessType, HideCommentAndSerialNumber ). If I look at the DbApplyChangeFailedEventArgs and examine the columns, I see the following:

Central:

Local:

What am I doing wrong?

Angela_SB Angela_SB · Accepted Answer · 2012-11-21T14:39:58

I have found the problem. The scope were the problem occurs, it only downloads data from central database to the local database.

When I updated the table schema, I did the following: - add column to both databases - update the value of the new column on both databases - update provisioning schema on both databases

The problem was that I executed a script that updates the column value on both databases. It only needs to be executed on the central database and the data will be downloaded to the client during synchronization.

I have changed this and now it works; and it worked previously as well with similar scenarios (add/remove/change column type for tables already in synced scopes) .

Sql Compact 3.5 syncronization fails

1 Answers