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