Why does the Key in NAV not match SQL key - Deferral Post. Buffer

Rodtmclean

I added a field "Deferral Line No." to the "Deferral Post. Buffer" key, as below. The key appears in NAV from the table designer. However, when I check with SQL Managent Studio, "Deferral Line No." is missing from the primary key.

I have compiled with force, imported the table as text and recompiled and ran Sync-NAVtenant. In the end I added "Deferral Line No." to the key using SQL Management Studio, which I don't like, and my code does as intended.

Original Key
Type,G/L Account,Gen. Bus. Posting Group,Gen. Prod. Posting Group,VAT Bus. Posting Group,VAT Prod. Posting Group,Tax Area Code,Tax Group Code,Tax Liable,Use Tax,Dimension Set ID,Job No.,Deferral Code,Posting Date,Partial Deferral

New Key
Type,G/L Account,Gen. Bus. Posting Group,Gen. Prod. Posting Group,VAT Bus. Posting Group,VAT Prod. Posting Group,Tax Area Code,Tax Group Code,Tax Liable,Use Tax,Dimension Set ID,Job No.,Deferral Code,Posting Date,Partial Deferral,Deferral Line No.

Any thoughts on this one?

Thanks in advance
Roddy

Slawek_Guzek

It is quite possibly the DevEnv error.

The SQL server has (or used to have in pre SQL 2016) a limit, maximum 16 columns could go into primary key definition. The DevEnv is apparently sticking to that limit, passing only the first 16 fields, and ignoring the rest, rather then passing them all and rely on error returned from the SQL Server.

In that case I guess it is pretty safe to have it added via SSMS, as it will not disapper unexpectedly. But i'd report it to Microsoft, anyway.

Rodtmclean

Hi Slawek,

Thanks for answering, the field count has increased from 15 to 16 in that table. I think you are right its a bug somewhere along the line in NAV.

Roddy

Slawek_Guzek

Strange one. What happes if you design the table and save it again - does the "Deferral Line No_" dissapear from the PK when you look at it in SSMS ?

Rodtmclean

Hi Slawek,

I hope you are having a good day?

Deferral Line No_ does not disappear from the key when saving the table from the object designed (I made an unrelated change to the table to force a save). This is on NAV 2018 cu 3 GB with SQL 2016.

Thanks and Regards
Roddy

Slawek_Guzek

It is quite possibly the DevEnv error.

The SQL server has (or used to have in pre SQL 2016) a limit, maximum 16 columns could go into primary key definition. The DevEnv is apparently sticking to that limit, passing only the first 16 fields, and ignoring the rest, rather then passing them all and rely on error returned from the SQL Server.

In that case I guess it is pretty safe to have it added via SSMS, as it will not disapper unexpectedly. But i'd report it to Microsoft, anyway.

Rodtmclean

Hi Slawek,

Thanks for answering, the field count has increased from 15 to 16 in that table. I think you are right its a bug somewhere along the line in NAV.

Roddy