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

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
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. ]
"HKM" <anonymous@.discussions.microsoft.com> wrote in message
news:04e801c49a0e$f4c9ebf0$a601280a@.phx.gbl...
> 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
>