Post reply

Snapshot failing due to duplicate records

  • August 31, 2005 at 11:50 am

    #94721

    Here's what I want to do.  Set up a table at the publisher with 3 fields (Field1, Field2, Field3), that replicates to the subscriber that has 4 fields (Field1, Field2, Field3, Field4). That's fine.

    Then, I want to make sure I never lose the data at the subscriber because Field4 will be updated in a separate process, so I set the publication to "Keep the existing table unchanged". Can I disable the DELETE part of replication so deletes don't occur at the subscriber ?  Perhaps I can use the  "Replace DELETE commands" witha dummy SP that doesn't do anything ?

    To see what would happen, I reintialized the subscription, re-ran the snapshot, then ran the pull subscription job. Since the data at the publisher was already at the subscriber, and I'm not deleting the subscriber, it failed trying to insert duplicate data.    Makes sense

    So, I truncated the publisher table, added some new rows that don't exist at the subscriber, deleted the snapshot files, re-ran the snapshot, and tried to replicate the new records. I still get the error about inserting duplicates, so I guess the original duplicates are still in the distribution database ??

    I suppose that the subscriber should never be reinitialized, then I wouldn't have this problem ?

    Any thoughts on how I can resolve this ?  ......   Or a better approach

  • September 1, 2005 at 5:08 am

    #586772

    do a no-sync subscription or use the keep table unchanged option.

    If you want new deletes (deletes that occur on your publisher after the snapshot is applied) to only occur on the publisher, right click on your publication, select properties, click onthe articles tab, click on the browse button to the right of your table, and in the commands tab, write the word NONE for the delete command.

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

     
    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

     

  • September 1, 2005 at 7:58 am

    #586822

    Thanks .... Using a dummy SP for the deletes worked, but using NONE is a lot clearer.

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

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