Notifications
Announcements
No record found.
Hi,
I need advise or sample codes to create database log setup for all tables in AOT by using code. Anyone can help me on this?
Regards,
John
*This post is locked for comments
Hi, I believe that the only needed thing is to create DatabaseLog records with correct TableId, DomainId and LogType (and to call SysFlushDatabaseLogSetup::main()).
SysFlushDatabaseLogSetup::main())
But I don't recommend to set it up for all tables - it has performance impacts, it generates huge amount of data and I don't believe that anybody needs to log absolutely all tables. For example, do you really want to log changes in NumberSequenceTable or BatchJobHistory?
Hi Martin,
Yes, I'm aware of the performance impact. The purpose I have to do this is to trace which table were used during the process for back to back order in our client's environment. I'm not sure which table were used else I wouldn't use this approach.
Is there a list for all TableId in AX?
Although several ways are possible, I would do it this way:
TableId tableId;Dictionary dictionary = new Dictionary();DictTable dictTable;;tableId = dictionary.tableNext(0);while (tableId){ dictTable = dictionary.tableObject(tableId); if (dictTable.isSql() && !dictTable.isSystemTable()) { //do something } tableId = dictionary.tableNext(tableId);}
TableId tableId;Dictionary dictionary = new Dictionary();DictTable dictTable;;tableId = dictionary.tableNext(0);while (tableId){ dictTable = dictionary.tableObject(tableId);
if (dictTable.isSql() && !dictTable.isSystemTable()) { //do something } tableId = dictionary.tableNext(tableId);}
The code is to traverse all tables in data dictionary tables?
Where should i put the codes to insert into Databaselog table? At the "//do something" comment?
Can you provide me the code to insert a table in Databaselog for insert and update type?
Thanks.
Sorry, I thought that the code was straightforward enough.
It iterates through all AOT "tables" and ignores temporary tables, tables with an inactive configuration key, maps, views and system tables.
In the "do something" section, you have to fill fields of the DatabaseLog table (namely DomainId, LogTable and LogType) and call insert() on the DatabaseLog buffer. LogTable field should be filled by "tableId" variable.
I did test it out and it works. The database log setup has setup correctly but do I need to close and reopen AX in order for the databaselog to take effect because it does not capture the changes?
Did you call SysFlushDatabaseLogSetup?
Thanks, now I'm able to log changes. Thanks for your help again.
When i run the code for insert log type, I got the error:
The upper limit of open cursors for Microsoft Dynamics has been exceeded (90). Use the -OPENCURSORS parameter, or modify the X++ code.
Do you know what is this error about? this doesn't happen on update log type.
If you simply wants to know which table is used (and not the data modified) than you could use the tracing found Tools / Options / SQL tab/ check SQL Trace and then check Table in the first group. The query will be saved in a table and can be browsed in SysTraceSQL form. Of course, you won't have the data updated/inserted.
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
Martin Dráb 4 Most Valuable Professional
Priya_K 4
MyDynamicsNAV 2
