sql server replication: Hi

I have a central DB with several branch DBs. The plan is for each branch
to pull a merge subscription from the central DB each night.

My problem involves replicating 3 tables:

Customer (has FK to Branch and FK to Address)
Branch (has FK to Address)
Address

The Address table holds address info for Customers and Branches.

The Customer table also foreign keys out to the Branch table (denoting
which branches 'own' which customers), and each branch only pulls its
own customers.

The Address table is join filtered on the Customer table so that each
branch pulls the addresses for its customers.

So far so good.

Now, each branch also pulls the full Branch table so that each branch
knows about all other branches. However, I can't get the replication to
pull the addresses for the branches as the merge will only allow the
branch to pull addresses for its customers. It appears that the agent
takes one look at the branch addresses and says to itself 'these
addresses do not belong to any of your customers' and will not replicate
them.

Any ideas?

Thanks in advance

Tony,

I tried to mimic your situation using the script below. As
I understand it, you need to bring all branches and their
addresses, the individual branch customers and their
addresses.
This circular join pathway causes a problem if you are
using join filters, but if you used a normal filter this
should work.

You could have a filter on the address table to select
addresses where:
branchid is not null - branch addresses
customerid in (select customerid from testCustomers where
branchid = x)

I'm not too sure how you are partitioning the data
according to subscriber,
but if you are using dynamic filters, then the x would be
replaced with HOST_NAME() and the filter just applies to
the testAddress table only.

HTH,
Paul Ibison


CREATE TABLE [dbo].[testAddresses] (
[AddressID] [int] NOT NULL ,
[street] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchID] [int] NULL ,
[CustomerID] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[testBranch] (
[BranchID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[testCustomers] (
[CustomerID] [int] NOT NULL ,
[fname] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[BranchID] [int] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[testAddresses] WITH NOCHECK ADD
CONSTRAINT [DF__testAddre__rowgu__30592A6F]
DEFAULT (newid()) FOR [rowguid],
CONSTRAINT [PK_testAddresses] PRIMARY KEY
CLUSTERED
(
[AddressID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[testBranch] WITH NOCHECK ADD
CONSTRAINT [DF__testBranc__rowgu__24E777C3]
DEFAULT (newid()) FOR [rowguid],
CONSTRAINT [PK_testBranch] PRIMARY KEY CLUSTERED
(
[BranchID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[testCustomers] WITH NOCHECK ADD
CONSTRAINT [DF__testCusto__rowgu__2AA05119]
DEFAULT (newid()) FOR [rowguid],
CONSTRAINT [PK_testCustomers] PRIMARY KEY
CLUSTERED
(
[CustomerID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[testAddresses] ADD
CONSTRAINT [FK_testAddresses_testBranch] FOREIGN
KEY
(
[BranchID]
) REFERENCES [dbo].[testBranch] (
[BranchID]
),
CONSTRAINT [FK_testAddresses_testCustomers]
FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[testCustomers] (
[CustomerID]
)
GO

ALTER TABLE [dbo].[testCustomers] ADD
CONSTRAINT [FK_testCustomers_testBranch] FOREIGN
KEY
(
[BranchID]
) REFERENCES [dbo].[testBranch] (
[BranchID]
)
GO