Transactional replication - update does not follow row filter rules

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi

I have an issue where inserts follow row filter rules OK, but updates
and deletions don't seem to. I'm using transactional replication.
Let's assume we have a table called Customers with a simple row filter
of

SELECT <published_columns> FROM <<TABLE>> WHERE custkey > 1000

The snapshot and distribution only copies over records with a key
greater than 1000. Correct.
If a record with a key greater than 1000 is updated or deleted, the
transactions are distributed and the same update or deletion appear on
the replicated database. Correct.

However, if a record with a key less than 1000 gets updated or deleted,
there should be nothing to distribute as the row doesn't meet the
filter requirements and doesn't exist in the replicated database. But,
we get the following error:

Error: The row was not found at the Subscriber when applying the
replicated
command.

This sort of implies it is expecting to find a row with the sub 1000
key in the replicated database. Why should this be? I can create a
new distribution agent profile to ignore this 20598 error, but our
customers are not comfortable with this.

Anyone else experienced this? Any resolution? Am I missing something
obvious??

Regards

Ian Hingley
Swift LG Ltd

.

Relevant Pages

  • Re: Cannot modify horizontal filter
    ... Looking for a SQL Server replication book? ... procedures if the publisher is out of sync with a read-only subscriber. ... reason for the row filter is that certain tables in the publisher will be ... JOIN orders o ON bo.orderid = o.orderid ...
    (microsoft.public.sqlserver.replication)
  • Re: Does Row Filter really keep network load low?
    ... Director of Text Mining and Database Strategy ... Looking for a SQL Server replication book? ... I was given a requirement to replicate data over a dial-up network. ... I am aware of Row Filter in a publication. ...
    (microsoft.public.sqlserver.replication)
  • Re: Using Row Filter
    ... It seems to me your question is, if you use a row filter, does the snapshot ... Looking for a SQL Server replication book? ... From temps where id=1 ...
    (microsoft.public.sqlserver.replication)
  • Re: Transactional replication - update does not follow row filter rules
    ... Looking for a SQL Server replication book? ... Let's assume we have a table called Customers with a simple row filter ... The snapshot and distribution only copies over records with a key ... filter requirements and doesn't exist in the replicated database. ...
    (microsoft.public.sqlserver.replication)
  • Re: Bug in transactional replication (UPDATETEXT)?
    ... bug to have a command replicated that violates the row filter. ... replication kept breaking at 5am each day and it came down to a job that ... was being run each morning that used UPDATETEXT statements to tweak text ... UPDATETEXT rather than UPDATE and hence TEXTPTRon the subscriber is ...
    (microsoft.public.sqlserver.replication)