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.
Note Since we ve already discussed the inserting, editing, and deleting capabilities of the Grid View, Details View, and Form View controls, this tutorial will focus on configuring the Sql Data Source control to support these operations.
If you need to brush up on implementing these features within the Grid View, Details View, and Form View, return to the Editing, Inserting, and Deleting Data tutorials, starting with An Overview of Inserting, Updating, and Deleting.
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.
There are other considerations as well which has not been mentioned in the article, but this should be enough to convince you that .
The field is called Rec Id, and it is maintained as an incremental, int64-based positive or negative number.
The results are then returned as either a Data Set or Data Reader, depending on the value of the control s value has been specified, the Enable Inserting, Enable Editing, or Enable Deleting option in the corresponding data Web control s smart tag will become available.
To illustrate this, let s take an example from the table and a Grid View that renders this data.
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 Rec Id in the sequence by a SQL Server stored procedure on the AX DB: Unfortunately it has limitations, because the AX Application Object Server fetches Rec Ids ahead and stores it in a cache to speed up inserting new records in the table.