none
BiDirectional Sync Provisioning Between SQL CE and SQL Server RRS feed

  • Question

  • Hello,

    I have a local SQL CE database and I am trying to sync to a remote SQL Server database using the Microsoft Sync Framework.  I have provisioned both the CE and the Server databases, but when I try to Sync, using SyncOrchestrator, I get an error "The stored procedure '[tablename_selectchanges]' doesn't exist."

    When provisioning the local and remote databases, I first enable tracking on the appropriate tables, then do the following:

    CE:

     

                    var localScopeDescription = new DbSyncScopeDescription(scopeName);
                    foreach (var table in tables) {
                        localScopeDescription.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable(table, remoteConnection));
                    }
    
                    var localDatabaseConfiguration = new SqlCeSyncScopeProvisioning(localConnection, localScopeDescription);
                    if (!localDatabaseConfiguration.ScopeExists(scopeName)) {
                        localDatabaseConfiguration.ObjectPrefix = "Sync";
                        localDatabaseConfiguration.SetCreateTableDefault(DbSyncCreationOption.Skip);
                        localDatabaseConfiguration.Apply();
                    }
    

     


    Server:

     

                    var remoteScopeDescription = new DbSyncScopeDescription(scopeName);
                    foreach (var table in tables) {
                        remoteScopeDescription.Tables.Add(SqlCeSyncDescriptionBuilder.GetDescriptionForTable(table, localConnection));
                    }
    
    		var remoteDatabaseConfiguration = new SqlSyncScopeProvisioning(remoteConnection, remoteScopeDescription);
                    if (!remoteDatabaseConfiguration.ScopeExists(scopeName)) {
                        remoteDatabaseConfiguration.ObjectPrefix = "Sync";
                        remoteDatabaseConfiguration.SetCreateTableDefault(DbSyncCreationOption.Skip);
                        remoteDatabaseConfiguration.SetCreateTrackingTableDefault(DbSyncCreationOption.Create);
                        remoteDatabaseConfiguration.SetCreateTriggersDefault(DbSyncCreationOption.Create);
                        remoteDatabaseConfiguration.SetCreateProceduresDefault(DbSyncCreationOption.Skip);
                        
                        remoteDatabaseConfiguration.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);
                        remoteDatabaseConfiguration.SetUseBulkProceduresDefault(true);
                        remoteDatabaseConfiguration.Apply();
                    }
    


     

    If I remove the SetCreateProceduresDefault(DbSyncCreationOption.Skip) line, which seems to prevent the creation of this procedure, I get the following error: 

    Invalid column name '__sysChangeTxBsn'.

    Invalid column name '__sysInsertTxBsn'.

    Invalid column name '__sysTrackingContext'.

     

    Am I setting up my provisioning wrong, and if so, how can I fix this?  Or could these errors be caused by something else?

    Thanks in advance for any help!

     


    • Edited by J. Stuart Monday, November 28, 2011 6:28 PM
    Monday, November 28, 2011 6:28 PM

Answers

  • how are you enabling change tracking?

    2nd question, why are you retrieving the table description from a remote connection when you have the tables locally already? i mean why are you retrieving the table description from SQL Ce when you're SQL Server already has the tables?

    you are getting the invalid column name error because on your SQL Server provisioning you are grabbing the table structure from SQL CE, so the change tracking columns in SQL CE in turn is being included in the table description.

    • Marked as answer by J. Stuart Tuesday, November 29, 2011 4:10 PM
    Tuesday, November 29, 2011 9:18 AM
    Moderator

All replies

  • how are you enabling change tracking?

    2nd question, why are you retrieving the table description from a remote connection when you have the tables locally already? i mean why are you retrieving the table description from SQL Ce when you're SQL Server already has the tables?

    you are getting the invalid column name error because on your SQL Server provisioning you are grabbing the table structure from SQL CE, so the change tracking columns in SQL CE in turn is being included in the table description.

    • Marked as answer by J. Stuart Tuesday, November 29, 2011 4:10 PM
    Tuesday, November 29, 2011 9:18 AM
    Moderator
  • That was my problem.  When I moved my change tracking to after my server provisioning and changed the SetCreateProceduresDefault() function to Create, I no longer got the same error and the synchronization worked perfectly.

     

    Thanks for the quick response!

     

    Just in case though, Here is how I do my change tracking...

     

    I enable tracking on the server like this:

     

        ALTER DATABASE database_name SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

     

    And each table like this:

     

        ALTER TABLE table_name ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

     

    I then set the local (SQL CE) change tracking like this:

     

        var ceLocalChangeTracking = new SqlCeChangeTracking(localConnection);
        ceLocalChangeTracking.EnableTracking(table, TrackingKeyType.PrimaryKey, TrackingOptions.All);

     


    Stuart Summers

    • Edited by J. Stuart Tuesday, November 29, 2011 4:54 PM
    Tuesday, November 29, 2011 4:10 PM
  • the SqlSyncProvider doesnt use the SQL Server Change Tracking. it uses its own custom tracking. By enabling SQL Change Tracking you're just putting extra load on your server of tracking changes and keeping tracking metadata that will is never used.

    same with SQL Ce, the SqlCeSyncScopeProvisioning takes care of enabling change tracking, you dont have to do it yourself.

    Tuesday, November 29, 2011 11:34 PM
    Moderator
  • Taking out the server change tracking seemed to work fine, but when I tried to take out the local change tracking, it wouldn't let me provision.  I received the following error when I ran SqlCeSyncScopeProvisioning.Apply():    

    The table is not a tracked table. [ Table name = table_name ]

     

    If I don't add any tables (since as you said, they are already on the client and server databases), It won't let me add a ScopeDescription to the SqlCeSyncScopeProvisioning instance.

     


    Stuart Summers
    • Edited by J. Stuart Wednesday, November 30, 2011 4:04 PM
    Wednesday, November 30, 2011 4:03 PM
  • what version of SQL CE are you using? have you tried with a new SDF? if you want to provision based on the scope description from the remote server, you can also start with an empty SDF.
    Wednesday, November 30, 2011 11:30 PM
    Moderator
  • I'm using SQL CE version 3.5.8080.0.  I have been using new SDF files for most tests, although a few tests have used existing databases, with similar results.

     

    I found out here, that in order to get change tracking to work for the current connection, the connection has to be closed and reopened before syncing.  That seems to have fixed the issues I was having, but it doesn't address the fact that I am still using change tracking.  Syncing to the server seems to be working now, however.


    Stuart Summers
    Wednesday, November 30, 2011 11:48 PM