I am working on an integration with an eCommerce platform, and we would like to use SQL rowVersion (formerly timestamp) in order to keep track of record changes. This option is better than enabling modifiedDateTime on a table or using the built-in database log because it is guaranteed to be unique and is directly on the record. There is no data-type for this in AX so it must be added through SQL.
I have added the column in SQL, and synchronized the AX database, and the column has not gotten dropped from SQL. However, I’m afraid that it will get dropped at some point or that it will cause some other undesired effect in AX. We are using AX 2012 R2 and MSSQL Server 2012.
Anyone have experience with this and can give me a definitive answer as to whether this is supported?
Is it supported? Not at all.
What are you trying to achieve? Wouldn’t SQL server change tracking the way to go?
It is good to understand that SQL rowversion is not supported in AX 2012 R2. When I add it in SQL, it doesn’t show up at all in AX, but it looks like I can retain it in SQL despite this. It makes sense that it could cause problems in the future though.
We have decided to use the built-in modifiedDateTime column in AX to keep track of change information. This option still doesn’t do exactly what we want though. Ultimately, we would want the timestamp we use to do three things:
(1) Be unique within the scope of a table
(2) Automatically have a value (whether or not the record has ever been changed)
(3) Exist as a column on the data table itself to avoid large joins
Our solution modifiedDateTime has the problem of being non-unique, but we can work around this. SQL rowversion seems to be the only one that would do everything we want, but it is not supported.