Replication Dropping Constraint

• jbraun

  Old Hand

  Points: 350

  More actions

  November 10, 2008 at 9:21 am

  #215088

  Hi,

  I need some help with some replication that I am attempting

  I have transactional replication running between 2 dbs. The subscriber table has a check constraint that ensures only certain types of data are going across, the publisher table does not have this constraint. The publisher article has a row filter that restricts the data going across to the same as the check constraint. Every time I run the replication the check constraint at the subscriber is dropped and I cant seem to find any option that will stop this . I need this check constraint on the table to allow the schema binding that i have on a view. In the article snapshot properties i have checked Delete data in the existing table that matches the row filter statement.

  Has anyone else encountered this problem? Can anybody point me in the right direction

  Many Thanks

  James

• noeld

  SSC Guru

  Points: 96590

  More actions

  November 10, 2008 at 1:04 pm

  #896527

  jbraun (11/10/2008)

  ---

  Hi,

  I need some help with some replication that I am attempting

  I have transactional replication running between 2 dbs. The subscriber table has a check constraint that ensures only certain types of data are going across, the publisher table does not have this constraint. The publisher article has a row filter that restricts the data going across to the same as the check constraint. Every time I run the replication the check constraint at the subscriber is dropped and I cant seem to find any option that will stop this . I need this check constraint on the table to allow the schema binding that i have on a view. In the article snapshot properties i have checked Delete data in the existing table that matches the row filter statement.

  Has anyone else encountered this problem? Can anybody point me in the right direction

  Many Thanks

  James

  What do you mean by "Every time I run the replication ..." ?

  The distribution Agent only drops constraints when applying snapshot.

  Are you re-initializing the subscription DB ?

  You can set on a per article the property "Action if name is in use" to "Keep existing object unchanged"

  
  * Noel

• jbraun

  Old Hand

  Points: 350

  More actions

  November 11, 2008 at 1:47 am

  #896789

  Hi,

  I can add the constraint again after the initial snapshot but will it drop the next time a transaction occurs on the publishers table?

  Thanks

  James

• noeld

  SSC Guru

  Points: 96590

  More actions

  November 11, 2008 at 3:00 pm

  #897187

  jbraun (11/11/2008)

  ---

  Hi,

  I can add the constraint again after the initial snapshot but will it drop the next time a transaction occurs on the publishers table?

  Thanks

  James

  If the initial snapshot is applied the constraint that does *not* exists in the primary will be dropped.

  You can add a post_snapshot script to add it back right after the snapshot is done.

  
  * Noel