none
Unable to alter table that is beeing replicated

    Question

  • Hello

    We are using SQL Server 2008 and transaction replication. I have set up the publication so that it is replicating schema changes but today i discovered that I was unable to add a column to one table that also is an article in the publication. The error I get is this:

    Altering [dbo].[Group]
    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.

    Have anyone any clue of what this can be?

    /Henrik

    hesta96
    Tuesday, February 16, 2010 4:14 PM

All replies

  • what isolation level are you using?
    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Tuesday, February 16, 2010 4:19 PM
    Moderator
  • Hi,

    I am getting the same error as posted by Henrik

    Environment SQL Server 2008 , P2P Replication Setup

    Issue: Getting error as above when trying to add timestamp column to the replicated table

    Transaction Isolation Level is  Serializable

     

    Would apreciate any help or Guidance

     

    Thanks..

    Tuesday, October 19, 2010 11:47 PM
  • We are having the same issue.  We did an in depth discussion with the Microsoft team and the SQL team said that they change the way they perform locks which do not support Serializable transactional isolation level.  They offered no work around for the issue.  However I was able to find a solution, but I doubt it is supported.  I'm trying to find a way to see if Microsoft will implement this change.    I made the following change to our SQL 2008 R2 Standard edition and Enterprise Edition of SQL server and our Alter table commands began working.  I have only used this in a development environment.  I have no idea how this may impact the calling applications.  I am hoping Microsoft will see this as a viable solution and change their triggers to run under the supported isolation levels.   I repeat that you would use this at your own risk as I am sure it isn't supported and I have not tested the impact on the application performing alter table commands.

     

    DISABLE

     

    TRIGGER tr_MStran_altertable on DATABASE

    GO

    CREATE

     

    trigger tr_MStran_altertable_Modified on database

     

    for ALTER_TABLE

    as

    set

     

    ANSI_NULLS ON

    set

     

    ANSI_PADDING ON

    set

     

    ANSI_WARNINGS ON

    set

     

    ARITHABORT ON

    set

     

    CONCAT_NULL_YIELDS_NULL ON

    set

     

    NUMERIC_ROUNDABORT OFF

    set

     

    QUOTED_IDENTIFIER ON

    SET

     

    TRANSACTION ISOLATION LEVEL READ COMMITTED

    declare

     

    @EventData xml

    set

     

    @EventData = EventData()

    exec

     

    sys.sp_MStran_ddlrepl @EventData, 1

     

    • Proposed as answer by dragonspeare Friday, August 17, 2012 2:39 PM
    Friday, November 19, 2010 11:31 PM
  • Had the same problem.  The ISOLATION LEVEL is the problem.

    Before your DDL, add the following:

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    GO


    qwe

    Friday, August 17, 2012 2:40 PM