Error - Cannot create more than one clustered index on table

This post describes one scenario in which this error occurs. There may be others.

In the late 4.0 versions of the NAV executables, all tables in NAV had a clustered index created on SQL for the primary key (Index 0).
However, the table objects in 4.0 versions did not have the Clustered property set on any of the keys.
It was also possible on 4.0 to toggle that property (on/off) and NAV would allow you to leave the table with none of the keys flagged as clustered, but the primary index on SQL remained clustered.

In the 5.0 versions, this behavior has changed. It is no longer possible to modify a table object to turn off the Clustered property on all keys. If you do this, save and compile the table, and then go back into designer and view keys, you will see that the 5.0 (and SP1) client has automatically turned on the Clustered property on the primary key. It is not possible in 5.0 to create a table with no clustered index.

 So, consider a scenario where you have a 4.0 database that has been converted to 5.0 SP1 executables. This is usually done one of two ways...

1) Open the 4.0 database with the 5.0 SP1 executable and click Ok to convert
2) Create a new database with the 5.0 SP1 executable and restore the 4.0 database backup using the NAV Client.

With 5.0 SP1, using either of these two methods, the Clustered property on the 4.0 object's primary key is automatically flagged.

If for any reason a 4.0 table object is later imported into the converted database, you end up with a situation where the clustered index remains on SQL, but the table object no longer has the Clustered property flagged. This doesn't cause any immediate problem, but let's say you later want to do a full object upgrade to 5.0 SP1 objects. In step 8 of the Upgrade Quickguide, you are instructed to import the new customized objects created in the compare and merge process. Some of the 5.0 SP1 table objects, for example Sales Header, have had the clustered index changed to a secondary key. When these tables are imported into the database, NAV will not drop the primary clustered index but will try to create the secondary clustered index, triggering the above error.

The workaround for this issue is to go into the table object in design mode and flag the primary key as clustered, then try importing the 5.0 SP1 table object again. Because the Clustered property is set, NAV will drop and recreate the primary key (index on SQL) before creating the secondary clustered index.

Laura K. Lake

Microsoft Dynamics NA

Microsoft Customer Service and Support (CSS) North America

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.