HelloWe 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
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
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
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.
TRIGGER tr_MStran_altertable on DATABASE
trigger tr_MStran_altertable_Modified on database
TRANSACTION ISOLATION LEVEL READ COMMITTED
@EventData = EventData()
sys.sp_MStran_ddlrepl @EventData, 1
- Proposed as answer by dragonspeare Friday, August 17, 2012 2:39 PM