Post reply

SQL 2005 merge replication and foreign key constraints

  • March 25, 2007 at 10:26 pm

    #171807

    Hi,

    We have a VB application that uses SQL DMO to automatically set up SQL merge replication between a publisher and subscriber (left over from SQL 2000). We used to have databases that replicated fine with foreign key constraints in SQL 2000, now when we use SQL 2005, we get foreign key constraint issues when the snapshot is bulk copied to the subscriber.

    In our DMO app, if the oMergeArticle.PreCreationMethod property is set to SQLDMOPreArt_TruncateTable or SQLDMOPreArt_DropTable the synch fails shortly after completion due to a foreign key constraint. If the oMergeArticle.PreCreationMethod property is set to SQLDMOPreArt_DeleteRows then synch succeeds.
     
    This suggests that in SQL 2005 drop table and truncate table commands issued by replication are not being recognized as coming from replication by the process that polices the NOT FOR REPLICATION clause on foreign key constraints. Is this true?? This stuff used to work in SQL 2000.
     
    Any thoughts?
    thanks,
     

  • March 26, 2007 at 6:54 pm

    #697029

    I would start by checking to see if SQLDMOPreArt_TruncateTable or SQLDMOPreArt_DropTable are available in 2005. If not you may have to re-write this application using SMO.

    Kindest Regards,

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply