Push referenced records

Hi,
I am facing the following problem. Please help me to solve it.
My question is: How to force the replication engine to include not only
the updated records but the relevant records as well?
I am using merge replication (SQL2kSP3 with SQL2kCE) with row filtering.
I many cases i have to replicate tables with "many to many"
relationship, where i operate the connection table (BOOKPERSON in the
example bellow). All the rows are filtered by PERSON_ID through out the
database.
Eg.
PERSON records to send:
select * from person where person_id = HOST_NAME()
BOOK records to send:
select * from book inner join bookperson on book.book_id =
bookperson.book_id and bookperson.person_id = HOST_NAME()
BOOKPERSON records to send:
select * from bookperson where person_id = HOST_NAME()
My problem is the following. The newly initialized subsciption
replicates fine, but in case of inserting 1 row to the table BOOKPERSON
(which means associating a book with a person) causes the replication to
fail, because the it replicates only the inserted BOOKPERSON record, and
does not replicate the the relevant BOOK record that is referenced now
by the BOOKPERSON record.
So my question is: How to force the replication engine to include not
only the updated records but the relevant records as well?
Thanks in advance
Pierre
The relevant schema is as follows:
CREATE TABLE Book (
BOOK_ID int not null,
BOOK_TITLE varchar(30) not null,
constraint PK_BOOK primary key clustered (BOOK_ID)
)
CREATE TABLE Person (
PERSON_ID int not null,
PERSON_NAME varchar(30) not null,
constraint PK_PERSON primary key clustered (PERSON_ID)
)
CREATE TABLE BookPerson (
BOOK_ID int not null,
PERSON_ID int not null,
constraint PK_BOOKPERSON primary key clustered (BOOK_ID, PERSON_ID)
)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_BOOKPERSON foreign key (BOOK_ID)
references BOOK (BOOK_ID)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_PERSONBOOK foreign key (PERSON_ID)
references PERSON (PERSON_ID)
Pierre,
presumably the personid value for a bookperson and book are not really
referring to the same entity (a person can be related to an individual book
as eg an author and as a reader), otherwise the related book would already
be replicated? In that case I'd say that the filter clause on the Books
article is incorrect. The simplest way would be to drop this filter and
replicate all the books.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I think I forgot to tell that we have thousands of records in the "BOOK"
table, and our subscribers have very limited storage capacity (~1000
thousand book records).
The relation between the two tables (person, book) is not like "authors
of a book" but "books 'thouched' by person", so there is a real M:N
relationship.
Meanwhile found the solution to my problem. I think I have to create
triggers (for insert, delete and update) on the table BookPerson which
will use a stored procedure (sp_mergedummyupdate) to force the
replication engine to put the relevant book records into the publication.
Am I right?
regards
Pierre
|||Pierre,
this sounds correct. Actually I misread your original post and didn't notice
the inner join, so was thinking that the PersonID was a FK directly to the
books table as an author. You'll need to make sure that the FK relationship
is 'Not For Replication', as you can't guarantee the replication order (in
SQL 2005 the default is PK then FK records but not in SQL 2000).
Regards,
Paul Ibison
|||Thank you for the tip, i`ll try it.
pierre
|||Paul,
we have tried to script the references with the "NOT FOR REPLICATION" option
and it caused SQL Server CE to completly break down at replication. After a
quick search on MSDN we found a reported bug saying that SQL Server CE
doesn't support the option mentioned above. (to be specfic SQL Server CE
supports NOT FOR REPLICATION, but there is a bug so we can't use it.)
If I'm not mistaken we have to drop all our references in these
circustances. Am I right? Is there any way to control the order of bulk
inserts at replication. (after PDA downloaded the appropriate snapshot, it
starts to bulk insert & update the rows)
regards
Pierre
"Paul Ibison" wrote:

> Pierre,
> this sounds correct. Actually I misread your original post and didn't notice
> the inner join, so was thinking that the PersonID was a FK directly to the
> books table as an author. You'll need to make sure that the FK relationship
> is 'Not For Replication', as you can't guarantee the replication order (in
> SQL 2005 the default is PK then FK records but not in SQL 2000).
> Regards,
> Paul Ibison
>
>
|||Pierre,
can you post up your reference for this CE bug (I'll put it on my website).
I haven't seen this reference, but if that is the case, you could increase
the -UploadGenerationsPerBatch
and the -DownloadGenerationsPerBatch parameters (to the max of 2000) to
avoid splitting parent and child changes across generation batches.
See http://support.microsoft.com/default...b;EN-US;308266 for more
info.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||BUG: NOT FOR REPLICATION Clause Causes SQL Server CE Replication to Fail
http://support.microsoft.com/default...b;en-us;300597
...however SQL Server CE Books Online says that "NOT FOR REPLICATION" is not
supported in case of foreign key constriants...
anyway, thanks for the tip, i'll try that.
pierre
sql