Tip #12 - Migrate from Native to SQL Server - Keys

It has been almost two years since I presented a successfull session at Directions USA about migrating from the Navision Native (C/Side or Classic) database to SQL Server. This session was repeated in EMEA half a year later.

You can download the slides here.

It was only recently that I found another thing you have to realise when you migrate. It was related to table 49 Invoice Post. Buffer.

Some of you might know that it is used in both codeunit 80 and 90 to combine G/L entries by (VAT) posting groups and some other stuff. This is done by doing a trick in the uniqueness of the records. If you want to add a criteria you must first add the field to the table and then to the keys.

A classic example is to add the posting description to the G/L Entries.

A native database can have up to 20 fields in the primairy key. However, the SQL database only allows up to 16 key fields in the clustered index. If you try to add more you will get this message:

Even though this table wil never be populated on SQL Server NAV maintains the table definition there.

As far as I know there is no solution for this other than reducing the number of fields.



Comment List
No Data
  • Ok, now I think I understand your problem.

    If you can modify the table object in NAV, bare with me. If not, I have no solution.

    As you cannot create any index with more than 16 columns in SQL Server, the table containing more than 16 PK fields cannot be created/imported as such.


    1. You could import a modified version of the table object with max. 16 PK fields, which works fine in SQL Server. This should be no problem, since the table is empty as it is never written to the database.

    2. After that, you can import another modified version of the table object, WITH the extra fields in the PK, BUT with property LinkedObject = Yes.

    This should do the trick (tested with NAV 5 anyway); the FIND instruction in the posting procedure WILL work according to the PK containing more than 16 fields, and SQL Server (basically using the original version of the table definition) will have no problem whatsoever with the table.

    Hope this helps :-)


No Data