Re: Transactional Replication - filtering records in child depending on data in parent table

Tech-Archive recommends: Speed Up your PC by fixing your registry

Could you script out your two tables, the parent and child and post them
here, or send them to me offline. It looks like I need the Customers and
Orders table as well as your filtering criteria.

I'll post the solution back here.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



<ian.hingley@xxxxxxxxxxx> wrote in message
news:1159958230.936469.197620@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Hilary

Thanks for your reply.
I've looked at merge replication, but this is a non-starter as SQL
Server tries to add its unique identifier column to each table. Our
application doesn't like that!!

When you say 'post your schema' what's my best method of doing this?

regards

Ian


Hilary Cotter wrote:
The perhaps good news is that this is done in merge replication and the
name
of this feature is called join filters. It is not so easy to do in
transactional replication.

Basically what you need to do is create a custom sync object involving an
indexed view. If you could possible post your schema for the parent and
child I'll show you how to do this.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



<ian.hingley@xxxxxxxxxxx> wrote in message
news:1159795382.995021.317800@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi.
I've set up transactional replication, distributing on a daily basis.
Let's pretend I have two related tables, customers and orders. I set up
a row filter on orders as follows:

SELECT <published_columns> FROM <<TABLE>> WHERE exists (
select * from customers where customers.verified=1 and
customers.custkey =
<<TABLE>>.custkey)

This works fine with the snapshot, only showing orders records where
the verified customer flag has been set. If there are orders for
unverified customers, these are not shown. Fine.

However, if subsequently a previously unverified customer record has
verified set to 1, the existing orders are still not displayed after
the next distribution. I assume this is because there is a transaction
against the customers table, but no transactions against the orders
table. There is therefore nothing to run the row filter against.

Is my assumption correct? If so, is there any way to get my 'child'
order records to be updated every time the 'parent' customer record is
changed? I have lots of data in lots of tables, and don't want to
re-snapshot on a daily basis to get round this. Am I missing something
obvious??

My schema and filter clause are much more complicated, but I hope the
example is simple enough to get the issue across.

Any help appreciated.

Regards

Ian Hingley
Swift LG Ltd




.

Relevant Pages

  • Re: Transactional Replication - filtering records in child depending on data in parent table
    ... Hilary Cotter wrote: ... Looking for a SQL Server replication book? ... Let's pretend I have two related tables, customers and orders. ... There is therefore nothing to run the row filter against. ...
    (microsoft.public.sqlserver.replication)
  • Re: Innocent Download of kp
    ... people who sell images of abuse would *have* to sell it to an increasing ... In order to provide an incentive for an increase in child abuse, ... I am speaking only of *Internet* kp. ... trusted customers sales can be discovered and traced, ...
    (uk.legal)
  • Re: Advanced Filtering Data Question
    ... example, when you filter the Customers DataTable, then you could use ... One of> the issues we have is trying to show the programming team the advantages> of using Datasets with tables instead of directly generating datasets with> a single table with SQL commands, and so encapsulating all the DB related information into the> Data Access Logic Components. ... > <<Is there a way to reflect a filter on a table to a related table?>> ... But this action only filters the Addresses DataView. ...
    (microsoft.public.data.ado)
  • Re: After removing filter on subform, how to maintain synchronicity
    ... Are there more actions available in the Macro ... > When you remove the filter, the main form's ApplyFilter event is fired ... >>customers data on the other pages where I have subforms with Stringing ...
    (microsoft.public.access.gettingstarted)
  • Re: Refresh combobox data upon recordset change
    ... as you change the Filter or RecordSource of FormA. ... If the "all customers" RowSource is: ... CustName from tblCustomers order by CustName; ...
    (microsoft.public.access.formscoding)