"Jim Johnston" <johnston@mounet.com> wrote in message
news:f2797f12.0312311319.317787fe@posting.google.com...
> Questions for you:
> 1. After you made your "Not for Replication" change on the foreign
> key constraint did you remove/republish your publication? Allow you
> can make the changes, they don't take affect until after you
> republish.
> 2. Do you need the foreign key constraint on the subscriber or can
> you
> write your SP's to validate the data being inserted. If so, you might
> want to publish your tables so that the DRI's are not replicated. You
> can do that on the publication properties by clicking on the eclipse
> in the articles tab next to each table and selecting the snapshot tab.
>
> V/R Jim Johnston
> MCSD.NET, MCDBA
>
> "R Spen" <anonymous@discussions.microsoft.com> wrote in message
news:<041c01c3cf25$ef2b5580$a301280a@phx.gbl>...
> > I have set up a dynamically filtered merge replication
> > with pull subscribers. There is a filtered table called
> > [station] that contains the name of the subscriber
> > workstation and the filter is 'WHERE description =
> > HOST_NAME()'. There is a [transaction] table that has a
> > foreign key relation to [station] based on Station ID.
> > The filtering works great for this table and in the
> > subscriber, I only get the transactions for that
> > station. However, the [transaction] table also has a
> > foreign key relation to a [result] table on resultID.
> > When the transactions are pulled to the subscriber, then
> > I only get a value in the [result] table if it exists in
> > the [transaction] table, not the other way around. The
> > [result] table should be an independent entity, and it
> > exists for as a lookup table in the subscriber. I should
> > have all available results to choose from regardless of
> > the values in the [transaction] table. So if I insert a
> > record into the [transaction] table with a differnt value
> > of result, then I get a foreign key error.
> > So far, I have tried the 'not for replication option'
> > on the foreign key, and I still have the same problem. I
> > have also tried to manually edit the scripts in the
> > REPLDATA share, but that led to no foreign key validation
> > at all.
> >
> > Any ideas? Thanks in advance,
> > RSpen

"RS" <rspen@yahoo.com> wrote in message
news:O6tI5bW0DHA.2448@TK2MSFTNGP12.phx.gbl...
> Thanks for answering.
> After enabling the 'not for replication', I tested and then republished
and
> then tested again. Here is what I have so far:
> 1. Updated transactions on subsciber with invalid resultid, got FK
violation
> (expected)
> 2. Updated transaction on the publisher with available resultid in parent
> table, no error (expected)
> 3. New transaction was replicated to subscriber (expected), new resultid
> also replicated to subscriber (not expected, since there was no
> transactional change to it). This is probably a result of the filtering.
> 4. Changed transaction to have new resultid in parent table. Old resultid
> row not deleted. (not expected)
>
> However, I have seen it on #4 that after the change, the old resultid was
> 'filtered' out of the parent table.
>
> So what is the mechanism that would filter out a parent table based on the
> values in the dependent table? But then also
> put it back and leave it if the value is ever encountered in the dependent
> table?
>
> Could it be the order in which the scripts for the snapshot are run,
thereby
> giving different results?
>
> Thanks,
> R Spen
>
>
> "Jim Johnston" <johnston@mounet.com> wrote in message
> news:f2797f12.0312311319.317787fe@posting.google.com...
> > Questions for you:
> > 1. After you made your "Not for Replication" change on the foreign
> > key constraint did you remove/republish your publication? Allow you
> > can make the changes, they don't take affect until after you
> > republish.
> > 2. Do you need the foreign key constraint on the subscriber or can
> > you
> > write your SP's to validate the data being inserted. If so, you might
> > want to publish your tables so that the DRI's are not replicated. You
> > can do that on the publication properties by clicking on the eclipse
> > in the articles tab next to each table and selecting the snapshot tab.
> >
> > V/R Jim Johnston
> > MCSD.NET, MCDBA
> >
> > "R Spen" <anonymous@discussions.microsoft.com> wrote in message
> news:<041c01c3cf25$ef2b5580$a301280a@phx.gbl>...
> > > I have set up a dynamically filtered merge replication
> > > with pull subscribers. There is a filtered table called
> > > [station] that contains the name of the subscriber
> > > workstation and the filter is 'WHERE description =
> > > HOST_NAME()'. There is a [transaction] table that has a
> > > foreign key relation to [station] based on Station ID.
> > > The filtering works great for this table and in the
> > > subscriber, I only get the transactions for that
> > > station. However, the [transaction] table also has a
> > > foreign key relation to a [result] table on resultID.
> > > When the transactions are pulled to the subscriber, then
> > > I only get a value in the [result] table if it exists in
> > > the [transaction] table, not the other way around. The
> > > [result] table should be an independent entity, and it
> > > exists for as a lookup table in the subscriber. I should
> > > have all available results to choose from regardless of
> > > the values in the [transaction] table. So if I insert a
> > > record into the [transaction] table with a differnt value
> > > of result, then I get a foreign key error.
> > > So far, I have tried the 'not for replication option'
> > > on the foreign key, and I still have the same problem. I
> > > have also tried to manually edit the scripts in the
> > > REPLDATA share, but that led to no foreign key validation
> > > at all.
> > >
> > > Any ideas? Thanks in advance,
> > > RSpen
>
>
>

"RS" <rspen@yahoo.com> wrote in message
news:ejESw4W0DHA.1708@TK2MSFTNGP12.phx.gbl...
> Followup to the testing:
> 1) I deleted a value in the parent table on the subsciber, so I only have
> values 1,3, and 4
> 2) The change was replicated to the publisher. (expected)
> 3) Then I updated the transaction to have a resultid of 2 at the
publisher.
> There was no FK violation. (not expected)
> 4) The change was replicated to the subscriber, and there was also not a
FK
> violation.
>
> Thanks,
> R Spen
>
> "RS" <rspen@yahoo.com> wrote in message
> news:O6tI5bW0DHA.2448@TK2MSFTNGP12.phx.gbl...
> > Thanks for answering.
> > After enabling the 'not for replication', I tested and then republished
> and
> > then tested again. Here is what I have so far:
> > 1. Updated transactions on subsciber with invalid resultid, got FK
> violation
> > (expected)
> > 2. Updated transaction on the publisher with available resultid in
parent
> > table, no error (expected)
> > 3. New transaction was replicated to subscriber (expected), new resultid
> > also replicated to subscriber (not expected, since there was no
> > transactional change to it). This is probably a result of the
filtering.
> > 4. Changed transaction to have new resultid in parent table. Old
resultid
> > row not deleted. (not expected)
> >
> > However, I have seen it on #4 that after the change, the old resultid
was
> > 'filtered' out of the parent table.
> >
> > So what is the mechanism that would filter out a parent table based on
the
> > values in the dependent table? But then also
> > put it back and leave it if the value is ever encountered in the
dependent
> > table?
> >
> > Could it be the order in which the scripts for the snapshot are run,
> thereby
> > giving different results?
> >
> > Thanks,
> > R Spen
> >
> >
> > "Jim Johnston" <johnston@mounet.com> wrote in message
> > news:f2797f12.0312311319.317787fe@posting.google.com...
> > > Questions for you:
> > > 1. After you made your "Not for Replication" change on the foreign
> > > key constraint did you remove/republish your publication? Allow you
> > > can make the changes, they don't take affect until after you
> > > republish.
> > > 2. Do you need the foreign key constraint on the subscriber or can
> > > you
> > > write your SP's to validate the data being inserted. If so, you might
> > > want to publish your tables so that the DRI's are not replicated. You
> > > can do that on the publication properties by clicking on the eclipse
> > > in the articles tab next to each table and selecting the snapshot tab.
> > >
> > > V/R Jim Johnston
> > > MCSD.NET, MCDBA
> > >
> > > "R Spen" <anonymous@discussions.microsoft.com> wrote in message
> > news:<041c01c3cf25$ef2b5580$a301280a@phx.gbl>...
> > > > I have set up a dynamically filtered merge replication
> > > > with pull subscribers. There is a filtered table called
> > > > [station] that contains the name of the subscriber
> > > > workstation and the filter is 'WHERE description =
> > > > HOST_NAME()'. There is a [transaction] table that has a
> > > > foreign key relation to [station] based on Station ID.
> > > > The filtering works great for this table and in the
> > > > subscriber, I only get the transactions for that
> > > > station. However, the [transaction] table also has a
> > > > foreign key relation to a [result] table on resultID.
> > > > When the transactions are pulled to the subscriber, then
> > > > I only get a value in the [result] table if it exists in
> > > > the [transaction] table, not the other way around. The
> > > > [result] table should be an independent entity, and it
> > > > exists for as a lookup table in the subscriber. I should
> > > > have all available results to choose from regardless of
> > > > the values in the [transaction] table. So if I insert a
> > > > record into the [transaction] table with a differnt value
> > > > of result, then I get a foreign key error.
> > > > So far, I have tried the 'not for replication option'
> > > > on the foreign key, and I still have the same problem. I
> > > > have also tried to manually edit the scripts in the
> > > > REPLDATA share, but that led to no foreign key validation
> > > > at all.
> > > >
> > > > Any ideas? Thanks in advance,
> > > > RSpen
> >
> >
> >
>
>