locked
Client tables become System tables after sync RRS feed

  • Question

  • Hi All

     

    After working through the various demos, we've started using Sync Services to sync data between Sql Server Express Edition and Compact Edition. We create the schema in SSCE manually using DDL rather than using the SyncSchema functionality.

     

    All looked good until we wanted to update the schema in the SSCEdatabase. It seems that after completing a Sync operation, the tables in the SSCEdatabase are flagged as System tables and cannot be modified by any sort of DDL statement either in Management Studio or using SSCE programatically. The exact error we get is "DDL operations are not allowed on system tables". In fact, we cannot even drop the tables and re-create them because of the System Table status and I can't find any way of removing the System columns once created (the __sys columns added after the first Sync).

     

    I understand the need for the schema to be protected during sync operations, but surely there must be some way to remove these constraints and/or modify the table schemas especially since Sync Services allows the tables to already exist and several articles state the managing schema changes manually is a preferred method. At the moment, I'm having to resort to re-creating the entire client database after a schema change.

     

    Any help/comments appreciated.

     

    Best regards, Craig

    • Moved by Max Wang_1983 Friday, April 22, 2011 11:00 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Saturday, April 14, 2007 1:17 PM

Answers

  • Hi Graig,

     

    this is the expected behavior for the current CTPs. once the local tables become tracked OCS tables, they cannot be dropped and some DDL operations (such as add/drop columns ) are disallowed. as you already figured out, the solution for shema changes is to drop the db and recreate it use sync or manually.

     

    We are working on supporting drop local table and add columns to local table for the next CTP and the functionalities should be aviable when the next CTP is released.

     

    Good to see you are chooseing the sync service as your sync solution.

     

    Regards,

    Yunwen

    Saturday, April 14, 2007 3:41 PM
    Moderator
  •  

    I have saw serveral posts in this forum talking about schema changes on the client side tables and some got exceptions when doing cerntain ddl operations. the following the the allowd-disallowed ddl operations on the client db. hope this helps. for full reference please read on the sync services BOL: working with Table schema section:

     

    Schema Changes in the Server and Client Databases

    Sync Services does not automatically propagate schema changes from the server to the client. For information about how to handle schema changes on the server, see Deploying and Versioning Applications (Sync Services). The following schema changes are supported in the client database:

    • Drop tables. The behavior during the next synchronization depends on the value that you specified for CreationOption. If you do not want the table on the client at all, remove it from the SyncTableCollection. When you drop a table that has change tracking enabled, you also drop the system table that tracks delete operations for that table. Therefore, any delete operations since the last synchronization are not uploaded to the server.

    • Add new columns.

    • Drop columns for tables that are involved in snapshot synchronization. This change is not supported for other types of synchronization.

    • Add, drop, and rename indexes.

    • Add, drop, and rename primary keys and other constraints.

    • Modify defaults.

    All other changes are not supported and can cause synchronization failures.

     

    thanks

    Yunwen

     
    Tuesday, May 13, 2008 7:26 PM
    Moderator

All replies

  • Hi Graig,

     

    this is the expected behavior for the current CTPs. once the local tables become tracked OCS tables, they cannot be dropped and some DDL operations (such as add/drop columns ) are disallowed. as you already figured out, the solution for shema changes is to drop the db and recreate it use sync or manually.

     

    We are working on supporting drop local table and add columns to local table for the next CTP and the functionalities should be aviable when the next CTP is released.

     

    Good to see you are chooseing the sync service as your sync solution.

     

    Regards,

    Yunwen

    Saturday, April 14, 2007 3:41 PM
    Moderator
  • Hi

     

    Thanks for a prompt reply.

     

    You mention having the facility to drop tables and add columns in the next CTP, will this be via the Sync Services classes or will it be possible to modify the tables manually through Management Studio? Personally I would prefer the latter as this gives me most control over the schema and I can introduce schema changes during application updates and not simply the next time a sync takes place, i.e. I would want to make sure the application know what to do with the modified columns before the data arrived.

     

    It may just be that at the moment (especially during development) I'm doing a lot of work in Management Studio and would like to manage the data there rather than having the tables effectively locked by the Sync operations. Which actually leads me to another question... the Sync operation always fails (saying the database is already open by another user) if I also have a query window open in Management Studio even if the connection string in the application is set to Read/Write. Is either the Sync services or Management Studio opening the SDF file in Exclusive mode?

     

    Best regards, Craig

    Saturday, April 14, 2007 4:06 PM
  •  

    I have saw serveral posts in this forum talking about schema changes on the client side tables and some got exceptions when doing cerntain ddl operations. the following the the allowd-disallowed ddl operations on the client db. hope this helps. for full reference please read on the sync services BOL: working with Table schema section:

     

    Schema Changes in the Server and Client Databases

    Sync Services does not automatically propagate schema changes from the server to the client. For information about how to handle schema changes on the server, see Deploying and Versioning Applications (Sync Services). The following schema changes are supported in the client database:

    • Drop tables. The behavior during the next synchronization depends on the value that you specified for CreationOption. If you do not want the table on the client at all, remove it from the SyncTableCollection. When you drop a table that has change tracking enabled, you also drop the system table that tracks delete operations for that table. Therefore, any delete operations since the last synchronization are not uploaded to the server.

    • Add new columns.

    • Drop columns for tables that are involved in snapshot synchronization. This change is not supported for other types of synchronization.

    • Add, drop, and rename indexes.

    • Add, drop, and rename primary keys and other constraints.

    • Modify defaults.

    All other changes are not supported and can cause synchronization failures.

     

    thanks

    Yunwen

     
    Tuesday, May 13, 2008 7:26 PM
    Moderator