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.

Anyone?

Enjoy.

Comment List
  • 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.

    However:

    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 :-)

    /Rgds

  • Kadee,

    Thank you very much for this information.

    Yes, I know these workarounds work. I was/am looking for a SQL/Database side solution.

    Cheers,

    Mark

  • As you know, this posting buffer table is used for grouping amounts and quantities, not for its sorting order. Because of that, there are some ways to solve this quickly and easily:

    1. If not all fields are filled to the max, you could concatenate some of them into one combined field, replace the fields in the PK by the combined field, and modify the posting procedures (i.e. codeunits 80 and 90 in std. NAV) to work accordingly.

    2. If this does not suffice, you could use a cryptographic hash algorithm on the combined field to transform it into a compressed representation of it (e.g. by using MD5, which is conveniently available by using the Navision Attain Hash component :-) ).

    3. Alternatively, you should theoretically be able to use a lossless compression algorithm instead, but I haven't tried this yet.

    Granted, the hashing method may not be waterproof, but the probability that the same hash value is generated by two different sets of field contents is ridiculously low, which probably makes it usable for Posting Descriptions, etc. (And then, of course, you could add e.g. two different combinations and resp. hashes to the equation; the probability of them both colliding at the same time will be even lower.)

    Note that the hashing/compression method also works around another possible issue (both native and SQL); if you need to add more than 252 bytes of data into your primary key (which is the max. in NAV), hashing/compressing (part of) the fields will also be a way out.

Related
Recommended