April 17, 2004 at 9:34 pm
In the Transaction replication Publication agent is failing with the Error as mentioned below.
Error number: 3726, Could not drop object 'Tablename' because it is referenced by a FOREIGN KEY constraint.
Can we fix this error without dropping the table or any other method to fix please let me know.
--sntiwary
April 19, 2004 at 6:12 am
It appears as though the subscriber has a foreign key defined against the table you are attempting to snapshot. As the snapshot drops and then recreates the remote table having the foreign key constraint prevents this from happening.
Go to the subscriber and run a sp_help against the table to see what references it and then drop the constraint, this should allow the snapshot to run without a problem.
April 19, 2004 at 6:56 am
If the table already exists in the subscriber, you shound't specify the snapshot to drop it.
You can change that in the properties of each article.
If you need to re create the table each time you apply the snapshot, you can use a pre snapshot script to drop the constraint, let the snapshot to drop and re create the table, and with a post script, you can re create the foreign key.
April 19, 2004 at 6:57 am
thank for reply. So you mean we cannot replicate the tables with foreign key or subscribers cannot have referential integrity. I Checked a KB article which tells:
When applying the snapshot, the schema files are applied in such a way that the child tables are dropped, and then re-created before the parent tables are re-created. So, even if there are foreign key references, tables can be dropped and re-created successfully.
http://support.microsoft.com/default.aspx?scid=kb;en-us;303218&Product=sql2k
April 19, 2004 at 7:04 am
If you have the integrity enforced on the publisher (and you are replicating both tables out and these are the tables involed in the problem you are having) you don't have to enforce it on the far end.
However if you are using the replicated table on the far end to enforce the integrity on a table that only exists on the subscriber then as Racosta said, use a pre and post snapshot script to drop and recreate the constraint.
April 19, 2004 at 7:22 am
Even if you published all tables wich are involved in the integrity validation, if you specify the snapshot to drop the table before applying the snapshot, it will fail.
You shoudn't drop the table in the snapshot.
April 19, 2004 at 9:44 pm
Thanks to stacenic & racosta for the help and your clarification!!
From your message what I understood is that if I am replicating both the tables related to Foreign key then I don't require that foreign key to be exist at the subscriber and that can I do by unchecking the option "Enforce Relationship for Replication" in their tables design ?
other option, if I want to enforce integrity at subscribers end also then I just need to include a pre and post snapshot script to drop and recreate the constraint, do you mean I need to include that in snapshot agents which has 3 steps as mention below, before the first step to drop the constraint and after its last step to recreate the constraint.
Snapshot agent steps:
1.Snapshot Agent startup message.
2.Run agent.
3.Detect nonlogged agent shutdown.
Please let me know if I have misunderstood.
April 20, 2004 at 5:20 am
If you actually go into the properites of the publication, to the Snapshot tab it allows you to specify locations for scripts to execute before and after the snapshot is run, this would allow you to drop and recreate the constraints.
If you are enforcing referential integrity on the publisher, replicating both tables and not making data changes on the tables on the subscriber then you don't have to Enforce the relationship for replication as by virtue of having the integrity on the publisher side and transactionally replicating the data you will never run into the problem of parent/child relationships not working.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply