Dynamics Community Forum Thread Details

Hello,

In our organization we have an "on premise - Dynamics 365 Business Central" server that runs our main business database.
It runs version 14, that is, Dynamics 365 Business Central Spring Release v14.00 I believe is also called Wave 1 2019.
We now use the "web" client (after many, many years of using the hard client, using it since it was Navision 2.50 late 1990's)

We also have a custom made web application that is written in php and uses its own MySql database.
Until relatively recently the php app wasn't linked to Dynamics 365 at all.
Then after a while, somebody specifically asked if it was possible to get some useful info from Dynamics 365.
I experimented a bit and successfully created a SQL user, connected the php app directly to the Dynamics 365 Microsoft SQL database and wrote some php to access the data they wanted.
That worked without any problems for months. Everybody is happy, yay!
After another while they asked me if it was possible to change a date on a certain table.
I explained that there are some more risks when you actually "write" data to the database (more security risks, data integrity and consistency risks).
They said "well you back up the database every day correct?"... Offcourse I do.
I then gave the php connection write access to a specific table in the Dynamics 365 SQL server database, wrote more php, and now it is possible to change that date field, either from Dynamics 365 or from the php web application... Everybody is happy again.
After a day or so some people started telling me that they couldn't make changes from Dynamics 365 to certain documents.
The specific documents coincidentally are the ones that are related to this date field that is changed from the php application.
By the way: the php I wrote executes an "update" query and then closes the connection, the whole transaction takes a few milliseconds.

Fortunately my question is not related to any data corruption or data breaching.
I did some investigation and it looks to me like there are some cache or buffering issues.
When the php application changes that famous date: then, if anybody opens that same document (even loading it from scratch hours after the change was made) Dynamics 365 shows the original (OLD) date that is no longer current in the Microsoft SQL database.
Then if they try to change it, (again for a span of hours) they get the following error messages from Dynamics 365.

The page has an error. Correct the error or try to revert the change.
Sorry, we just updated this page. Reopen it, and try again.

This literally persists for hours or perhaps even a whole day. Then, next day Dynamics 365 will finally show the actual data present in the Microsoft SQL database and unlock the record.

I understand this also is related to "Record locking" to avoid data inconsistency but it seems a little too prolonged.
It is more likely to be a caching issue.

My question is:
Is there a way to have Dynamics 365 access the data more like in "real time"? I understand there will be a performance penalty on doing so but with the current
hardware we use and the fact that there is never more than a dozen users working concurrently, it is irrelevant.
Is it possible to change some settings either in Microsoft SQL or in the Dynamics 365 instance settings that can mitigate this problem?

Thank you!

No record found.