Merge Replication fails after applying SP4

Question

Post reply

Merge Replication fails after applying SP4

Mahender-233322

SSC Enthusiast

Points: 185
More actions
October 25, 2005 at 9:38 am

#91591

Hi,
Yesterday I applied SP4 on my Publisher/Distibutor Server (SQL 2000 on Win 2003) and all the subscribers (SQL 2000 on Win 2000/2003) simultaneously.
After applying the SP4, in one of the table data is not get replicated from Subscriber to Publisher, but data is getting replicated from Publisher to Subscriber. All the other tables are working fine in the same Publication.
I tried testing by updating single row on the subscriber and start the merge agent, merge agent says 1 row update, but when I check the row, old value is persisting. It doesnt even show in conflicts.
Can anyone Help!
Mahender

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 1

Are there any triggers on that table other than the replication triggers?

Mark

Mahender-233322 SSC Enthusiast Points: 185 More actions · Answer 2

There are 2 AFTER UPDATE NOT FOR REPLICATION triggers on that table.

More info on the problem

------------------------

Prior to SP4, I faced strange problem on this table. 1 to 2% updates from subscriber where failing, some were reported in conflicts and some were not. The strange thing about this is, in one subscriber this is not happening. After brain stroming sometime I noticed there are 2 sets of replication triggers on this table in this particular subscriber, which were not in the rest of the subscribers (10). But in the extra set of triggers the tablenick variable refers to a tableid which doesnt exists in the database. And another difference is ALLOW ANONYMOUS SUBSCRIBERS Option is disable while creating the publication for this subscriber (2 years back). To boost the speed of the replication I had to enable this option for all the other subscribers.

After applying SP4, 100% of the updates by subscribers are failing except from that unique subscriber. And the irony is that this table or rather column is most important and critical in the whole database.

Does anyone faced such problem?

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 3

Check the Server setting properties on the publishing and subscribing servers and make sure the "Allow triggers to fired that fire other triggers (Nested triggers)" is enabled.

We had a case where our "INSTEAD OF" auditing triggers log the change before updating the table and then update the table. Because the table update was done by a trigger and the above feature was disabled the update never replicated. It could be the replication triggers are blocking your AFTER UPDATE triggers.

Mark

Mahender-233322 SSC Enthusiast Points: 185 More actions · Answer 4

Hi Mark,

All the servers are having identical server settings and "Allow triggers to fired that fire other triggers (Nested triggers)" is enabled.

Today I noticed that in one more table 1 column is not getting updated, the update statement issued in the subscriber effects more than 1 column, All column changes are getting updated except 1 column. This is strange behaviour.

Mark Johnson-176476 SSCommitted Points: 1603 More actions · Answer 5

Sounds strange. Does that same column replicate from the publisher to the subscriber successfully?

I assume you have checked the column filters to make sure that column is not being filtered accidentally.

I have upgraded many replication sites to SP4 and not seen this behavior. Very odd.

Mark

Mahender-233322 SSC Enthusiast Points: 185 More actions · Answer 6

Hi Mark,

There were no column filters on the table.

I overcame the problem by deleting the subscription, regenerating the snapshot and then recreating the subscription. It was a painful execise as the tables in the subscription were huge and connectivity was thru analog telephone lines. I had to bring all the servers to Head Office and do the process on LAN.

Thanks

Mahender