Post reply

Merge replication error, foreign keys

  • 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

     

  • July 22, 2004 at 5:27 am

    #515640

    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.

  • July 28, 2004 at 3:06 am

    #516517

    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

  • July 29, 2004 at 8:23 am

    #516795

    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

     

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

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