"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels" error message when you add a field to a table in Microsoft Dynamics NAV

Article ID: 981995 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

When you try to add a field to a replicated table in Microsoft Dynamics NAV 2009 Service Pack 1 (SP1) or in Microsoft Dynamics NAV 5.0 SP1, you receive an error message that resembles the following:
The following SQL Server error or errors occurred when accessing the Table_Name table:
650,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels. SQL: ALTER TABLE "NAVDB"."dbo"."CRONUS International Ltd_$MyTable" ADD "field4" VARCHAR(10) NOT NULL CONSTRAINT "$ndodefault$625437302$4" DEFAULT ''
Notes
  • Table_Name is a placeholder for the actual name of the table that you want to use to add the field.
  • This problem occurs on a computer that is running Microsoft SQL Server 2008 and that has the Microsoft Dynamics NAV environment installed. Additionally, this issue occurs only if Microsoft SQL Server Replication is enabled on one of the Microsoft Dynamics NAV tables.

CAUSE

This issue occurs because you cannot change the schema of a replicated table in Microsoft SQL Server 2008 if the relevant transaction is set to use the SERIALIZABLE isolation level. This is a new constraint for Microsoft SQL Server 2008 that did not exist in earlier versions of Microsoft SQL Server. However, the default configuration of Microsoft Dynamics NAV uses the SERIALIZABLE isolation level to change tables.

RESOLUTION

To resolve this problem, configure Microsoft Dynamics NAV 5.0 SP1 and Microsoft Dynamics NAV 2009 SP1 to use the REPEATABLE READ isolation level in Microsoft SQL Server instead of the SERIALIZABLE isolation level.

For more information about how to change the isolation level that is used by Microsoft Dynamics NAV, visit the following Knowledge Base (KB) articles and the associated hotfixes:
  • Microsoft Dynamics NAV 5.0 SP1: 979135
  • Microsoft Dynamics NAV 2009 SP1: 978100

Properties

Article ID: 981995 - Last Review: August 5, 2011 - Revision: 2.0
APPLIES TO
  • Microsoft Dynamics NAV 2009
  • Microsoft Dynamics NAV 5.0
Keywords: 
kbmbsdatabaseandplatforms kbsurveynew kbmbsmigrate kbprb KB981995

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com