Merge replication error, foreign keys

Question

Post reply

Merge replication error, foreign keys

Christopher Klein

SSCrazy

Points: 2648
More actions
July 21, 2004 at 1:39 pm

#164230

I'm running into an issue with merge replication that has just started to crop up. I wrote a nice process for checking data in/out of a tablet for field work and something was missed in my documenting HOW I set it up.
When I go to run a pull merge subscription from the subscriber I am getting the following error:
The schema script '\\CATAPULT\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\CATAPULT_RealEstate_REALESTATE_DYNAMIC\20040721151641\Propertys_15.sch' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
---------------------------------------------------------------------------------------------------------------
Could not drop object 'dbo.Propertys' because it is referenced by a FOREIGN KEY constraint.
(Source: TABLET002\TABLET002 (Data source); Error number: 3726)
---------------------------------------------------------------------------------------------------------------
Of course its referenced by a foreign key constraint, its the primary parent table for just about every other table in the database, the kicker is that why is it starting to grump now and how do I get around it?
regards,
Chris

Viewing 4 posts - 1 through 3 (of 3 total)

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

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 1

Was a new snapshot recently generated which included object changes?

If so, and the snapshot is being applied then:

When subscribers are set up their either already have the schema and data, or the schema is pushed to them "yes, initialize the schema and data". If when the subscription is set up the "yes, initialize the schema and data" is selected, then when new snapshots are applied, replication will try to drop the tables and recreate them. I believe the problem is that the tables are dropped in no apparent order so parent tables could be dropped prior to the child tables; thereby causing the error you see.

To get around (I don't think you can without dropping the subscription):

Drop the subscription
drop the subscribers database
(option 1)recreate the subscription with the "schema and data" option
or
(option 2)script the database on the subscriber and populate the data; then select "subscriber already has schema and data option"

Option 2 requires that you apply object changes to the subscriber manually.

I'm curious to see if others are using different techniques. This is what we're doing and it seems to work.

Alamir SSCertifiable Points: 6085 More actions · Answer 2

do u have SP3a installed ? it solve many problems in replications

also install MDAC 2.7 SP1 on all clients and cerver machines.

I hope this help u

Alamir Mohamed
Alamir_mohamed@yahoo.com

Christopher Klein SSCrazy Points: 2648 More actions · Answer 3

Hi there,

I have the system and clients patched to 3a and both are running with the latest mdac service patches.

I basically, to get around this, did an evil hackola where I set the article defaults to truncate data instead of drop/creating tables and I do not copy over referential ties. Ugly but it works and I needed to get it out the door.

Going back now that I have some time to figure out what changed that caused this to suddenly start cropping up.

Thanks,

Chris