Journey to AX 2009 Knowledge

Captains Log STAR DATE unknown.

Recently I moved out of the MS GP world and entered a strange new territory, MS AX2009. This is a completely different ball game now. AX it is my opinion 10 times the size of GP. It requires a lot more resources for support and administration that any other ERP I have ever worked with. The interface is much the same as GP with the exception that it has a lot more going on within the HCI than GP does. From a developer perspective AX has an AOT (application object tree), this tool/function is a good sent to understanding how stuff works. I am currently working on building some custom reports and this AOT is like a bible for out AX2009 implementation.

I will be posting my SQL syntax for a Sale Report by Sales Persons (sales group) showing invoice lines not just invoice total.

Using DEX SQL LOG | DEX.ini

Prequel:

Recently I encountered a problem within GP where if you select a particular link to open a window (eg. Cash Receipts (Transaction --> Sales --> Cash Receipts)) the system would go unresponsive and you will have to close GP using other means (task manager).

Engage Warp Speed:

I was baffled for sometime as to why this was happening so I spoke to a buddy of my mind (Cowmaster - http://cowmasterscorner.blogspot.com/) and he suggested i look at the SQL locks.
He even gave me a sql scipt that will clear the locks:

delete from DYNAMICS..ACTIVITY where USERID not in (select loginame from
master..sysprocesses)delete from tempdb..DEX_SESSION where session_id not in
(select SQLSESID from DYNAMICS..ACTIVITY)
delete from tempdb..DEX_LOCK where session_id not in (select SQLSESID from
DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00800 where USERID not in (select USERID from
DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00801

I used the script but that didnt work.

 

So i decide to try an SQL logging approach (yeh i know you would have done this first.  Thanks for telling me now smart a$$).

To do this logging i used the DEX.ini (C:\Program Files\Dynamics GP\Data) file to engage the SQL logs which will be written to the DEXSQL.log (C:\Program Files\Dynamics GP\Data) file.

How did i engage the logs?

Within the DEX.ini file you can set the following variables to TRUE.

SQLLogSQLStmt=TRUE
SQLLogODBCMessages=TRUE
SQLLogAllODBCMessages=TRUE  

 

I then just view the DEXSQL.log file and found that my problem is somewhere between the TX30000 and RM00401 tables.

 

Oh an dont forget the set back the DEX.ini files variables back to false.

 

References:

More info on Dex.ini

http://www.meritsolutions.com/meritmatters/index.php?/archives/98-Dex.Ini-Settings-to-Make-Dynamics-GP-More-Comfortable.html 

General Table info

http://victoriayudin.com