This requirement keeps popping up on the Community Forums by people who are not familiar of how Microsoft Dynamics AX works, typically for third-party integration, data warehousing and reporting scenarios. In this post I would like to give some insights on why is it not recommended to update AX database tables from SQL.
Record identifiers
Each row in Microsoft Dynamics AX database within Microsoft SQL Server has a unique identifier which is by default the primary/surrogate key as well for the records. The field is called RecId, and it is maintained as an incremental, int64-based positive or negative number. The highest number I was able to allocate is 3 below the maximum value of a 64-bit integer, which was 9,223,372,036,854,775,805 and you would get an error for the next value:
If you would ever reach the maximum value, the manual fix is to set the next value to be -9,223,372,036,854,775,807.
It is possible to fetch the next RecId in the sequence by a SQL Server stored procedure on the AX DB:
Unfortunately it has limitations, because the AX Application Object Server fetches RecIds ahead and stores it in a cache to speed up inserting new records in the table. To be able to reliably insert records directly from SQL, you would need to stop all AX AOS instances connecting against the database, to ensure the cached RecId values will not conflict with the ones that you have just actually inserted. If it does happen, you may call the SystemSequence::flushValues() method to flush the RecIds in the cache stored on the AOS:
https://msdn.microsoft.com/en-us/library/systemsequence.flushvalues.aspx
I have found an interesting article which goes around the caching problem by doing Record identifier suspension from X++ code in case the value was updated outside of AX, and sounds like an interesting concept to explore:
https://www.linkedin.com/pulse/how-insert-data-ax-from-external-program-maintain-recid-higginson
Record versions
AX maintains a Record Version for Optimistic Concurrency Control (OCC) reasons to avoid two people trying to change information for the same record, with which AX is able to tell if a record is outdated. Also this record versioning is used to determine if a cached record entry in the AX memory / file space has to be refreshed. You may read up more about this on the following link:
https://msdn.microsoft.com/en-us/library/bb190073.aspx
By updating a record directly in SQL Server, the RecVersion field is not maintained, and if your AX AOS is running, it will not be aware that the record has been updated. You could flush the cache from code. Here are the details about record caching on MSDN:
https://msdn.microsoft.com/en-us/library/bb278240.aspx
Number sequences
In AX many of the fields have a numerically increasing value that we refer as Number Sequences. It is a very complex setup which is affected by many factors. Such as the scope of the number sequence, or whether numbers are reused when there are gaps if it is set as continuous. Also finding the link between what setup drives the number sequence reference for the specific fields could be based on business logic, which is unavailable for SQL Server. I would avoid creating and updating values in SQL if Number sequences are involved. You may read up more about them in the documentation:
https://technet.microsoft.com/en-us/library/hh209457.aspx
https://technet.microsoft.com/en-us/library/hh242127.aspx
Business logic
The most important piece is that in AX we have a bunch of validation and record creation methods on the tables. So when you create a customer, it is not just simply inserting a record in CustTable, but it touches 5-15 other places as well depending on what information have you provided, what is your current system setup and activated licenses, and so on. I would like to highlight Delete Actions, which is again an important part of AX, which would remove records connected to the data you are about to delete, or prevent the removal of an item for example, if there are transactions for the product already.
It is not possible to call such business logic from SQL, and when you do such changes, it is very likely that you will miss out something, unless you are an AX expert and have debugged through the functionality around the table which you would like to touch within SQL.
The same goes on with AX Security, which is completely bypassed if you access the database with an account that is too powerful with privileges to be able to touch the DB. It is also a potential data leak risk that your auditors would not like.
There are other considerations as well which has not been mentioned in the article, but this should be enough to convince you that touching the database is evil.
There are workarounds for some of the problems mentioned above, but it can be a complex matter to correctly insert or update records directly in SQL.
What is the solution then?
If you still insist in doing this, what I can recommend is that you can run PowerShell scripts in SQL Server Jobs, with which you could use the AX Business Connector. The BC allows you to do CRUD operations on AX tables and all of the above would be taken cared of. You can find an example here:
Another option is to use the tools available in AX such as the Data Import Export Framework (DIXF, formerly DMF):
Before you attempt to update anything in the database, please talk to your Senior AX technical staff – he will be able to do what you want in a reliable way with a Job, or using the tools defined above.
The recommendation is that you do not update AX database tables from SQL to avoid missing or breaking something unintentionally.