Monday, March 12, 2012

Replication and FK Constraint

Let me preface this w/ the fact that I may be stupid...

In trying to test replication in SQL 7 I created a staging database to hold one table (tblStaff) that I would try to replicate into the "live" version of tblStaff in another DB (on the same server).

The "live" version of tblStaff obviously (perhaps) has a number of dependent tables (and stored procedures - which are really only select queries) that rely on the StaffID key. I have turned off the "Enable key for replication" in all the dependent tables and, for good measure, in tblStaff. I then created a publication of the stagingDB tblStaff and a pull subscription on the live DB. But...(snapshot) replication *still* fails w/ an error that dropping the live tblStaff would violate foreign key constraints.

Thanks in advance.What was the error?|||"Could not drop/truncate (depending on the option chosen earlier in the process - tried it both ways) table due to foreign key constraint."

As an (inelegant) workaround I wrote two scripts to drop and add the constraints and then synch. in the middle. It works and the table only has to be replicated every now and again but... I would still like to know the "real" way to do it.|||Use the NOT FOR REPLICATION Option and refer to BOL for more information.|||I already did that.|||May check this Article (http://www.windowsitlibrary.com/Content/77/11/1.html) about do's and dont's.

No comments:

Post a Comment