Export (0) Print
Expand All

You can add and drop columns in a table published in a Microsoft SQL Server publication without dropping and re-creating the publication. If you add or drop a column in a published SQL Server table, that schema change is published to the subscription database in SQL Server Compact 3.5 when the subscription is next synchronized. For more information, see "Schema Changes on Publication Databases" in SQL Server Books Online.

Important   Schema changes like adding a computed column at the publisher can result in failed synchronization attempts by the SQL Server Compact 3.5 subscriber. You should review all SQL Server Compact 3.5 schema limitations before making any schema changes on the publisher.

If a column is dropped from the SQL Server publication, any changes made to data in that corresponding column in the SQL Server Compact 3.5 subscription database that have not already been propagated to the Publisher are discarded during synchronization. When an article is dropped, the following rules apply.

  • If the publication compatibility level is 90RTM or higher, you can drop an article without forcing a reinitialization of the SQL Server Compact 3.5 subscription.

    If the compatibility level is lower than 90RTM, an error is generated and you must reinitialize the subscription.

  • The article must be removed from the publication by using sp_dropmergearticle before it can be dropped from the database.

    When an article is removed by using sp_dropmergearticle, the article is not automatically deleted from the database. It still exists as a local table that is no longer replicated.

You can also add articles to an existing publication. When an article is added, the following rules apply:

  • If the publication compatibility level is 90RTM or higher, you can add an article without forcing a reinitialization of the SQL Server Compact 3.5 subscription.

    If the compatibility level is lower than 90RTM, an error is generated and you must reinitialize the subscription.

  • A table must first be added to the database before it can be added to the publication.

  • An article is added to the publication by using sp_addmergearticle.

  • A table that has been added to the subscription can include constraints to other articles that also exist in the publication.

    For example, a publication contains the Employees, Customers, and Orders tables. You later add the OrderDetails table to the publication. The OrderDetails table might include a foreign key constraint to the Orders table.

The SQL Server Compact 3.5 Subscriber must be reinitialized if the column that is added to an existing published SQL Server table is an identity column.

Show:
© 2014 Microsoft