system indicator, update company info via SQL query

Doomhammer · 2016-07-11

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:
USE NavDEMO
GO
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.

Wisa123 · 2016-07-11

Hi,

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.

RockWithNAV · 2016-07-11

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.

Doomhammer · 2017-09-04

Tried to use SELECT before and after modifying table, this helped

NavNab · 2017-09-04

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.

Slawek_Guzek · 2017-09-04

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

Howdy, Stranger!

Quick Links

Categories

system indicator, update company info via SQL query

Answers