I don't quite follow exactly what you described. Yes, I would consider it a
bug to have a command replicated that violates the row filter.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.


[quoted text, click to view]

Would you consider it a bug in transactional replication that UPDATETEXT
statements are replicated to the subscribers regardless of any row
filters on the article with the text column being updated in the
publication?

It just took me a couple days to track down why my transactional
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
columns in existing rows. The Windows client for this vendor app just
uses plain old ordinary UPDATE statements (which will have their own
problems if the text is too long I would think) but this other
maintenance job circumvents the row filters in my publication by using
UPDATETEXT rather than UPDATE and hence TEXTPTR() on the subscriber is
coming up with a null (since the row doesn't exist on the subscriber due
to my row filter) and trying to pass that null pointer into the
UPDATETEXT statement. What a P-in-the-A that was to troubleshoot!

--
*mike hodgson*

Well, I published a table (with a text column) and put a row filter on
the article so only about half the rows end up on the subscriber. This
works fine with the snapshot. But later on the source data is changed
with an UPDATETEXT statement. The rows that get changed on the
publisher do not match the row filter in my publication and as such are
not on the subscriber. However, that UPDATETEXT statement gets executed
on the subscriber anyway! (This is continuous transactional replication
I'm talking about.) The first statement in the batch that gets executed
on the subscriber is a simple

DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(MyTextCol) FROM MyTable
WHERE <some criteria that doesn't match my row filter>

Of course this assigned a NULL to @ptr because the row doesn't exist on
the subscriber. The next statement in the batch that gets executed on
the subscriber is

UPDATETEXT MyTable.MyTextCol @ptr 0 0 WITH LOG 'blah...'

This UPDATETEXT statement raises an error (error 7133 from sysmessages)
and the transaction is rolled back, which breaks the distribution agent
(and a reinitialisation is necessary).

The problem was that the UPDATETEXT statement should never have been
executed on the subscriber because of the row filter on the published
article that prevents those particular rows from being replicated to the
subscriber. But when the UPDATETEXT statement ran on the publisher, the
row filter was essentially ignored (I can understand why, sort of, since
it's not one of the kosher DML statements - INSERT, UPDATE or DELETE)
and the UPDATETEXT statement ended up being executed on the subscriber too.

--
*mike hodgson*
http://sqlnerd.blogspot.com



[quoted text, click to view]