"Background: we are developing a webshop solution for NAV and AX: www.bbayt.com . We use a live business case to test and extend our webshop with various features and are now looking into a more close integration with Dynamics AX. We do not have Dynamics AX expertise, but ASP.NET instead. We use Dynamics AX for our soccer webshop: www.bzoox.com.
We are interested if we can use a SQL timestamp and if so how to implement it in AX datamodel? We want to keep track of all database changes for out interface connection to and from our webshop. We also are thinking about creating a ‘auxiliary table’ for every AX table to hold the timestamp and to create a trigger to update that record. But that is quite cumbersome and maybe a load to the AX SQL server.
Either switching on table properties (created by, created date, modified by, modified date) from within Ax.
==> Thank you. Where can I do that? Setup → ?
Or switching on database log (under Administration → Setup)
==> I found this option. It currently logs ‘rename’ and ‘delete’ of records of item table. However, for changes it will create an large database and will slow down the AX performance. So this is not a go. Above all the data can not be serialized/ mapped easily in C#.NET.
BTW you might also be interested in the data management tool for purging Dynamics Ax database. For more info, please click here.
AF
==> I did look into this a bit. But do not understand properly what it is. It is a kind of ‘cockpit’ tool for Business Warehousing?
It is at the table level. Select the table you want (ex - CustTable) and click properties. You should see the properties mentioned above there.
This tool was showcased in Convergence 2008 at Copenhagen. This tool helps customers in purging, archiving, restoring data etc. For example archiving data for one fiscal year. In other words this tool helps managing Dynamics Ax in high data growth rate scenarios. Hope this helps.
Can you please elaborate what you mean by SQL timestamp?
FYI - switching on the table properties that I mentioned would automatically populate details like created / modified by, created / modified date time etc in SQL Server.
A timestamp is a value that increments every time an object in SQL Server changes. A kind of unique ID for every object in the database. So if you store the timestamp of a set of records in a table now (call it timestamp X) and then do some changes. Then you can request the records based on the interval between timestamp X and timestamp Y of the table easily.
The same could be done with the modifieddate, however, with winter/summer time (GMT) it can swift and thus forget changes. Above all the timestamp is faster, although that is neglectable with the hardware performance these days.
SQL syntax to add:
Alter table item
add RecordTimeStamp timestamp
SQL will automatically fill it since you cannot fill it by code (I believe).
I heard that AX will remove the timestamp or cause the AOT not to work anymore if you add them on SQL level. That is why I wonder if there is not a way to tell AX that a timestamp must be added to a table.
From what you described, it looks like timestamp functionality is similar to switching on table properties. I would suggest switching these properties on and test. If you have any issues, please do not hesitate to let us know.
And yes - it is a master/slave relationship. Ax dictates the schema to SQL server. Therefore any changes made directly on SQL Server will be lost.
I have done some reading. Timestamp is not a primitive data type which is supported by X++: http://msdn.microsoft.com/en-us/library/aa602290.aspx So only option is using the datatime to keep track of changes. I still cannot find a way to connect x++ code with a SQL statement.