How is the row filter clause and join filter should be....

Discussion in 'microsoft.public.sqlserver.replication' started by HKM, Sep 14, 2004.

  1. HKM

    HKM Guest

    Hi,

    I am new to replication, I have the following tables in my
    database, and I would like to replicate to other
    subscriber using Merge Replication:


    tblProduct (Product master)
    ----------
    ProdID (PK)
    ProdGrp (FK to Product grouping)
    ProdDesc

    tblProdGrp (Product grouping)
    ----------
    ProdGrp (PK)
    ProdGrpDesc

    tblCustomer (Customer master)
    -----------
    CustID (PK)
    BranchID (FK to branch master)
    OfferGrp (FK to trade offer grouping)
    CustName

    tblBranch (Branch master)
    ---------
    BranchID (PK)
    BranchName

    tblTradeOfferGroup (Trade offer grouping)
    ------------------
    OfferGrp (PK)
    OfferGrpDesc

    tblTradeOffer (Trade offer)
    -------------
    OfferID (PK)
    OfferGrp (FK to Trade offer grouping)
    StartEffDate
    EndEffDate

    tblTradeOfferProduct (Trade offer's product)
    --------------------
    SeqID (PK)
    ProdGrp (PK/FK)
    OfferID (PK/FK)
    OfferQty
    *Note: SeqID + ProdGrp + OfferID is unique

    I need all rows from tblProduct, tblProdGrp, tblBranch,
    tblTradeOfferGroup, tblTradeOffer, tblTradeOfferProduct to
    replicate to subscriber, but only single branch's customer
    in tblCustomer at subscriber. How should I configure the
    row filter and join filter in my Merge Replication?

    Currently, I configure as following:-

    Row Filter:
    tblCustomer row filter tblCustomer.BranchID = '001'
    tblProduct <publish all rows>
    tblProdGrp <publish all rows>
    tblBranch <publish all rows>
    tblTradeOfferGroup <publish all rows>
    tblTradeOffer <publish all rows>
    tblTradeOfferProduct <publish all rows>

    Join Filter:
    Filtered table Table to filter
    tblTradeOfferGroup tblTradeOffer
    tblTradeOffer.OfferGrp = tblTradeOfferGroup.OfferGrp

    tblTradeOffer tblTradeOfferProduct
    tblTradeOfferProduct.OfferID = tblTradeOffer.OfferID

    But, tblTradeOfferProduct not replicated over. A conflict
    occurs saying FOREIGN KEY constraint etc. The weird case
    is, when I synchorise again, the rows publisher's
    tblTradeOfferProduct are deleted.

    Please advice.

    Thank you.

    HKM
     
    HKM, Sep 14, 2004
    #1
    1. Advertisements

  2. HKM

    Paul Ibison Guest

    HKM,
    at first sight this all looks OK. Presumably the FKs are
    declared in the publisher and aren't just logical ones?
    Please can you ensure they are declared as "NOT FOR
    REPLICATION", which will overcome any ordering issues.
    If this doesn't solve the issue, can you post up the
    error message as I'd like to see which constraint is
    causing the problem.
    Regards,
    Paul Ibison


    (recommended sql server 2000 replication book:
    http://www.nwsu.com/0974973602p.html)
     
    Paul Ibison, Sep 14, 2004
    #2
    1. Advertisements

  3. I think, replicating tblTradeOfferProduct should fix your problem.

    Since tblTradeOfferProduct has relations to tblTradeOffer (and I believe to
    tblProduct too ) it is better to replicate this table.
    Otherwise yuo have to declare all those relations as "NOT FOR REPLICATION"

    Since you dont have "NOT FOR REPLICATION" whenever that are constraint
    violatins you will see merge failing with constraint violations.
    And once some entries fail to propagate to the subscriber, in the next
    merge, compensating actions (deletes for all the failed inserts) are made
    and hence you will see that those rows vanish from the database.
    You can either set the constraints to "NOT FOR REPLICATION" or replicate the
    tblTradeOfferProduct table too. One of them should fix the problem

    Hope that helps
    --Mahesh

    [ This posting is provided "as is" with no warranties and confers no
    rights. ]
     
    Mahesh [MSFT], Sep 22, 2004
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.