Writing directly in SQL tables

Gonzalo · 2016-03-29

Hi experts,
We have an external application which creates records directly in sql nav tables (I know is highly not recommended).
If I do a run or a COUNT from NAV, I see less records than directly in the sql. (The property DataPerCompany is YES)
Is this normal?? Do you know how can I solve this issue??

(I know that we've to use web services instead of that and we're doing it, but it takes time...)

My client is in NAV 2016, I didn't have this problem with the previous versions. Does Microsoft changed something regarding this??? Any piece of advice??

Thanks

Gonzalo

kylehardin · 2016-03-29

This sounds like a caching problem. Right before your COUNT statement in NAV, try putting a SELECTLATESTVERSION, which tells NAV to ignore the cache in the service tier and re-fetch data directly from the SQL server.

RockWithNAV · 2016-03-29

Hey Gonzalo,

Whenever you create a new company in NAV, SQL too creates a new company so I dont think DatePerCompany will have an effect on this.

Select * FROM [TestompanyOne$Sales Header] For one Company
Select * FROM [TestompanyTwo$Sales Header] For Second Company

Stop and Restart your service from Services.MSC + You can try using SELECTLATESTVERSION as if its a cache memory Issue .

kriki · 2016-04-01

The NAV Servicetier cache does not expect someone else writing to it's database. So it does not always go to the SQL DB for data but uses its cache. SELECTLATESTVERSION fixes this.
Another trick is to define a query object on that table and read from the query object. Query objects go always directly to SQL DB and do not use the cache in the service tier (but it is easier is to use SELECTLATESTVERSION...).

kylehardin · 2016-03-29

This sounds like a caching problem. Right before your COUNT statement in NAV, try putting a SELECTLATESTVERSION, which tells NAV to ignore the cache in the service tier and re-fetch data directly from the SQL server.

RockWithNAV · 2016-03-29

Hey Gonzalo,

Whenever you create a new company in NAV, SQL too creates a new company so I dont think DatePerCompany will have an effect on this.

Select * FROM [TestompanyOne$Sales Header] For one Company
Select * FROM [TestompanyTwo$Sales Header] For Second Company

Stop and Restart your service from Services.MSC + You can try using SELECTLATESTVERSION as if its a cache memory Issue .

kriki · 2016-04-01

The NAV Servicetier cache does not expect someone else writing to it's database. So it does not always go to the SQL DB for data but uses its cache. SELECTLATESTVERSION fixes this.
Another trick is to define a query object on that table and read from the query object. Query objects go always directly to SQL DB and do not use the cache in the service tier (but it is easier is to use SELECTLATESTVERSION...).

Gonzalo · 2016-04-06

Thanks, SELECTLASTVERSION worked properly!!!

Howdy, Stranger!

Quick Links

Categories

Writing directly in SQL tables

Best Answers

Answers