Thursday 13 January 2011

SQL 2008 - adding a column to a replicated table fails

There seems to be an issue in certain situations with adding columns to replicated tables in SQL 2008 where the publication is set to include schema updates.

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.

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:
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.

2 comments:

Anonymous said...

Good post. You can also simply issue
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
before calling ALTER TABLE and the operation will complete.

Anonymous said...

You don't need to change system procs, just make sure isolation level is Not set to SERIALIZABLE when issuing the alter table statement.

For more info - http://support.microsoft.com/kb/981995

Post a Comment