system indicator, update company info via SQL query

DoomhammerDoomhammer Member Posts: 211
Hi, I bumped into strange problem.

Customer has NAV 2016 CZ, we created second database with second service as demonstration/learning/sandbox for users.
We want to create this second demo DB using SQL backup and restore. This is working correctly.
But I want to update system indicator to show users they are in sandbox, not in production.

My script looks like this:
UPDATE [dbo].[MORAVIAPRESS$Company Information] SET [System Indicator Style] = 1
Update command is performed, when I try to select value, it shows correct 1. But in NAV application, I see old value 0. See pic 1.
After I hit update button, value changes to 1 and after restart of RTC, indicator is shown correctly. See pic 2.

Thanks for opinions.
Martin Bokůvka, AxiomProvis


  • Options
    Wisa123Wisa123 Member Posts: 308

    1.) Do NOT perform manual SQL updates onto NAV Tables EVER, if you don't know exactly what you are doing, since you're bypassing all business logic inside NAV this way.

    2.) The System Indicator is loaded on Login into NAV, so you will always need to restart your RTC Client for the changes to show correctly. This was always the case since the release of RTC and 3-Tier.
    Austrian NAV/BC Dev
  • Options
    RockWithNAVRockWithNAV Member Posts: 1,139
    Yes 100% support to what Wisa said, It's not like you should never use SQL but you should be in full confidence that it's not bypassing any Standard NAV Code. IF there is something as custom code then you can go ahed provided no intervention of the standard piece.

    Try your operation from NAV side it will work for sure.
  • Options
    DoomhammerDoomhammer Member Posts: 211
    Tried to use SELECT before and after modifying table, this helped :)
    Martin Bokůvka, AxiomProvis
  • Options
    NavNabNavNab Member Posts: 181
    Hello Doomhammer,

    As already said: do not update NAV data via SQL. Because NAV Service Tier is not aware of the change you made via SQL until you SELECTLASTVERSION or you restart your instance.
  • Options
    Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Doomhammer wrote: »
    Tried to use SELECT before and after modifying table, this helped :)

    It did not help, it was an illusion.

    What you see in NAV client is the data from the service tier cache. Until you invalidate or clear the NST cache, by using SELECTLASTVERSION or you restarting the instance as NavNab mentioned, or by updating the corresponding object definition, the data from the underlying table is not re-fetched.

    If you want to get updated system indicator to be show correctly in NAV then restore the SQL backup, update the System Indicator field, and then start the service.

    Having said that - updating data directly on the SQL level is not supported. In this particular case it will work, but in general it should be avoided - due to problems with NST cache syncing, and as my predecessors have said, if you have any NAV code in the table updating the data the SQL level will not get the C/AL code executed - possibly leaving your data inconsistent (from NAV application point of view)

    On the top of technical reasons listed above there are also legal reasons - your license does not let you accessing the data directly on the SQL level - unless you have purchased special user license for doing that.

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Sign In or Register to comment.