Merge replication error Msg 20092, Level 16, State 1

Recently I created a (fortunately) test environment in SQL 2005 for a merge replication that was to upgrade an existing SQL 2000 installation. I didn't want to re-initialise the subscriber as there was a large amount of data involved (about 115million rows). Once the subscriber was created (and snapshot created), I tested the merge by updating a particular set of data on the publisher first and then the subscriber to check that the replication worked. Publisher update went fine, data replicated successfully. However, when I tried to update the rows at the subscriber I got an error Msg 20092, Level 16, State 1 Procedure MSmerge_disabledml....

Table 'XXXXXX' into which you are trying to insert, update, or delete data is currently being upgraded or initialized for merge replication. On the publisher data modifications are disallowed until the upgrade completes and snapshot has successfully run. On subscriber data modifications are disallowed until the upgrade completes or the initial snapshot has been successfully applied and it has synchronized with the publisher.

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.



Fortunately the solution for this is quite straight forward. It seems that somehow during the creation of the subscriber (and I admit this was done a few times) the merge replication upgrade triggers were not removed.

To correct this scenario, you must run the following SQL on the subscriber, drop the subscription and re-create the subscription again. The script is (OK I know it uses a cursor but it's a one off fix script so who cares!):

DECLARE @sql NVARCHAR(4000),
@trigger NVARCHAR(100)

DECLARE trig_curs CURSOR for
SELECT NAME
FROM sys.triggers
WHERE name LIKE 'MSmerge%' AND PARENT_CLASS=1

OPEN trig_curs

FETCH NEXT FROM trig_curs INTO @trigger

WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'drop trigger [' + @trigger + ']'

print @sql

exec sp_executesql @sql

FETCH NEXT FROM trig_curs INTO @trigger
END

CLOSE trig_curs
DEALLOCATE trig_curs