NDT output generates a clustered key error

imurphy · 2008-10-14

I've been trying to migrate from Nav 5.0 to Nav 5.1 but have hit *another* problem. I've generated a merge file of the differences but when I import I get an error:

A clustered key can appear only once in a table.
Table: Sales Header

This occurs at line 6276 of my import file which is second last curly brace before the end of the definition of an object - OBJECT Table 36 Sales Header.

So far so good. In the object there is a definition of two clustered keys:

    {    ;Document Type,No.                       ;Clustered=Yes }
    {    ;No.,Document Type                       ;Clustered=Yes }

and in my original Nav V5.0 object file I have just a single clustered key:

{    ;Document Type,No.                       ;Clustered=Yes }

In the new Cronus V5.1 there is a different clustered key:

{    ;No.,Document Type                       ;Clustered=Yes }

well, different, its the other way around.

Now, the question is why would this have been reversed and what effect does it have? I can just remove the first line from my merged object file but is this a problem?

Ian

kriki · 2008-10-14

With 5.0SP1, MS changed the clustered index to "No.,Document Type". For performance reasons, it is a better clustered index because it is WAAAY more selective (in theory:each "No." has ONLY 1 record). So you need to remove the clustered property from "Document Type,No.".

imurphy · 2008-10-14

Thanks for that kriki, so I can safely eliminate the surplus old key.

I'll have to dig through the release notes to see if there were any other changes like this.

Ian

DenSter · 2008-10-14

imurphy wrote:

Thanks for that kriki, so I can safely eliminate the surplus old key.

No you can't eliminate the old key. The "Document Type,Document No." key is the primary key of the table. If you eliminate that key, a LOT of NAV functionality won't work anymore. Leave the key alone, and leave it as enabled, and also leave MaintainSQLIndex turned on for both of them, just uncheck the Clustered property for the first key and turn it on for the second one.

You need both keys, the primary key because that is how the table was designed to have unique records. the second key is there only for selectivity on SQL Server.

So leave it like this:

{    ;Document Type,No.                       ;Clustered=No }
    {    ;No.,Document Type                       ;Clustered=Yes }

imurphy · 2008-10-24

denSter, Never did respond to you, apologies.

When I said eliminate I did mean the clustered attrib rather than removing the key.

I continued on with the highly frustrating attempt at upgrading from 5.0 to 5.1 and have hit yet more problems, so its currently on hold. However the keys problem is solved.

Thanks

Ian

David_Singleton · 2008-10-24

imurphy wrote:

I continued on with the highly frustrating attempt at upgrading from 5.0 to 5.1 and have hit yet more problems, so its currently on hold. However the keys problem is solved.

This is why it is recommended to use an Upgrade Center like Liberty Grove to do this. If you belive the phrase "Time Is Money" you will find it has actually cost you a lot more to do it your self that have it done by someone who does it every day for a living.

imurphy · 2008-10-24

Upgrade Center ?
Liberty Grove?

These are news to me - never heard of them before.... I should add that I haven't been doing this very long and this the my first highly frustrating attempt. I'll check out liberty and see who else is out there doing upgrades. Thanks.

Thus far I am *extremely* disappointed in the toolset provided by MS to handle the whole migration process. The various tools strike me as being something written by a summer student who left before finishing or telling anyone how it worked. The little remid.exe tool is a 16 bit app - implying that noone has even bothered to recompile it in over 10 years!
I've slowly come to the conclusion that I was going to have to write my own app to merge changes as I am finding that ndt is simply silently skipping whole objects.

Ian

DenSter · 2008-10-24

It has nothing to do with the toolset, it has to do with knowing how to use them, and an attitude of "I will have to write something myself to get it right" when upgrades have been conducted with the existing tools for years and years. If you don't know how to do it, maybe you should find someone who can teach you. Right now it seems you are making one bad decision after the other, and each one is only making the problem worse.

Personally I would not use the NDT, I would export all objects as text and use a good text compare tool (like beyond compare or araxis). I've spoken to other people who swear the mergetool is the best tool out there. That only takes care of the object merge though, you also need to run the upgrade toolkit. The trick is that this toolkit only knows about standard NAV, you will have to take care of customizations yourself.

If you are planning to do more upgrades and make that an important part of your business I would say get some help and have someone show you how it is done. If you don't want to ever do this again, get some help and have an expert do it for you. Either way, get some help. You will save your customer time and money.

imurphy · 2008-10-24

The theory of the upgrade is simple enough. Merging software forks is not something specific to navision - you get it in any well managed software project.

Thus far I've only had to do one upgrade and a relatively simple one at that 5.0 to 5.0sp1 but with a large number of big mods. We'll have to migrate our own db sometime soon too. So its not such a problem. I'm treating this as a learning excercise for the moment.

It has nothing to do with the toolset unless the toolset is the recommended way to go (and quite a few people on this forum have recommended ndt in the past) and it crashes constantly, contains serious bugs and you have to spend more time fighting with it than doing the business of merging in the changes.

I'm inclined to agree on your methodology of exporting the objects and using a compare/merge tool.

I wasn't aware of mergetool until your posting so I'm going to give it a spin when I have some time free... thanks for the pointer. As usual it was on mibuso in the downloads section but I hadn't noticed it.

Ian

DenSter · 2008-10-24

http://www.mergetool.com/ although I can't open the website at the moment. There are a whole bunch of goodies in there. I personally don't use the tool, but would give it a serious look if upgrading or version management were an important part of my work.

NDT output generates a clustered key error

Comments

Categories