The first error we saw was:
Msg 650, Level 16, State 1, Procedure sp_MSfixup_base_columns, Line 40
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
I'm also aware of a similar error mentioning a different system proc:
Msg 650, Level 16, State 1, Procedure sp_MSreinit_article, Line 66
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
It doesn't look like there is currently any supported workaround for it, but I managed to get it work by doing the following.
Step 1 - In the database where you are trying to change the table, script out the database trigger called "tr_MStran_altertable" as a create statement
Step 2 - Give the trigger a new name in this script, e.g. tr_MStran_altertable_ReadCom.
Also, in the code you will see it has several SET statements, and to this section add:
Also, in the code you will see it has several SET statements, and to this section add:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Step 3 - run this script to create a new trigger.
Note the reason for creating a new trigger is that you will get an error if you try to just ALTER the existing trigger to add this setting saying:
Msg 21598, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 60
Modifying DDL triggers created by replication is disallowed since these are required to track DDL changes.
Step 4 - disable the original trigger
DISABLE TRIGGER [tr_MStran_altertable] ON DATABASE
You should now find the original ALTER TABLE statement completes without an error, and the new field is also added to the replication.