Merge Replication Not Accounting For Dependencies

We have a SQL Serrver 2000 database that utilizes merge replication. Every
now and then the replication process attempts to insert or update tables out
of order. For instance: We have a TransactionDocuments table that has a one
to many relationship with a TransactionLines table. Sometimes SQL Server will
attempt to instert new TransactionLines before it has inserted the
TransactionDocuments the TransactionLines depend on.
I thought merge replication was supposed to keep track of these
dependencies. If so, is there a bug that has a workaround or something else I
need to check?
Thanks,
Matt
Matt,
you could set the NOT FOR REPLICATION attribute on the FKs to true. In SQL
server 2005 it is possible to specify the merge replication order but not so
in SQL 2000. Usually the order selected in SQL Server 2000 is correct but
not always. Please check these articles:
http://www.replicationanswers.com/Me...derArticle.asp
http://support.microsoft.com/default.aspx?scid=kb;[LN];307356
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Merge replication has a rich set of features regarding dependencies, but
these are typically for sensing join filters. As Paul has pointed out child
records can be inserted before parent records, but normally these are
resolved within a batch and if not the next time the agent runs the
dependencies will be fixed as the parent will get in before the child the
second time around.
The generic work around is to use the not for replication switch on all
constraints.
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
"Matt Sradley" <MattSradley@.discussions.microsoft.com> wrote in message
news:4C82B8F1-0176-4E6B-B0AF-D4F4F840A268@.microsoft.com...
> We have a SQL Serrver 2000 database that utilizes merge replication. Every
> now and then the replication process attempts to insert or update tables
> out
> of order. For instance: We have a TransactionDocuments table that has a
> one
> to many relationship with a TransactionLines table. Sometimes SQL Server
> will
> attempt to instert new TransactionLines before it has inserted the
> TransactionDocuments the TransactionLines depend on.
> I thought merge replication was supposed to keep track of these
> dependencies. If so, is there a bug that has a workaround or something
> else I
> need to check?
> Thanks,
> Matt
|||Thanks,
You both answered my question. It looks like the main problem are self
referencing tables in our DB. Not for replication will fix the problem. Do
you think I should do a constraint check after the replication process is
finished? It makes me nervous that data is getting put in the tables without
the checks in place.
"Hilary Cotter" wrote:

> Merge replication has a rich set of features regarding dependencies, but
> these are typically for sensing join filters. As Paul has pointed out child
> records can be inserted before parent records, but normally these are
> resolved within a batch and if not the next time the agent runs the
> dependencies will be fixed as the parent will get in before the child the
> second time around.
> The generic work around is to use the not for replication switch on all
> constraints.
> --
> 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
> "Matt Sradley" <MattSradley@.discussions.microsoft.com> wrote in message
> news:4C82B8F1-0176-4E6B-B0AF-D4F4F840A268@.microsoft.com...
>
>
|||Matt,
I suppose the best option is to upgrade to SQL 2005, but if this is not an
option currently for you, then it's entirely your decision about the
constraint checking after synchronization - I never do it FWIW.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)