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:
