Enabling DEXSQL logs with Support Debugging Tool

It must be the Support Debugging Tool week. After the fabulous security article by David Musgrave explaining the inner works of the Resource Information and the Security Information windows and how they aid in finding those pesky security tasks and roles, I thought it would be a good idea to pickup on an old subject that often gets lost in the archives of support: DEXSQL logs.

It still surprises me how many questions are posted on the various community forums asking for guidance on troubleshooting errors in stored procedures, table access errors, and even database connectivity errors. Likewise, it's often forgotten that the main purpose of the DEXSQL.LOG is to capture all interactions between the Microsoft Dynamics GP client and the Microsoft SQL Server database. These interactions can be tailored to the actual ODBC messaging and/or the SQL statements that are executed against the database.

KB article 850996 explains how to setup a DEXSQL.LOG the old fashioned way, this is by editing the DEX.INI. This approach has an inherent problem: it requires that the user exit the Microsoft Dynamics GP system and re-enter to have the settings take place. After all, the DEX.INI is also known as the parameters file and parameters are loaded upon startup of the application. In addition, as the application loads and up to the point of recreating the error, the DEXSQL.LOG file would have already recorded a set of events totally unrelated to the issue at hand.

Note: the DEXSQL.LOG can be edited with Notepad to remove the entries recorded up to the point of an error. The empty file can be saved to proceed with the logging activity.

Support Debugging Tool allows you to create a DEXSQL.LOG without ever having to leave the application.



Furthermore, one of the capabilities I appreciate from the Support Debugging Tool feature is to be able to create logs based on date and time, this is, the log file name will include the date and time the log was initiated. I can even setup my own file name if needed. The following is a sample DEXSQL.LOG:

/*  Date: 08/11/2010  Time: 22:53:01
stmt(597399424):*/
{ CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'sa', -1, 949, 22190, 2 ) }

/*  Date: 08/11/2010  Time: 22:53:01
stmt(597400232):*/
{ CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', -1 ) }

/*  Date: 08/11/2010  Time: 22:53:01
stmt(597401040):*/
{ CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 949, 22190, 2 ) }

/*  Date: 08/11/2010  Time: 22:53:01
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:01
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:01
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:01
stmt(597450192):*/
{ CALL TWO.dbo.zDP_SVC00998SS_1 ( 0.00000 ) }

/*  Date: 08/11/2010  Time: 22:53:01
stmt(597450192):*/
{CALL TWO.dbo.zDP_SVC00998F_1(NULL,NULL)}

/*  Date: 08/11/2010  Time: 22:53:01
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:01
stmt(597446960):*/
{ CALL TWO.dbo.zDP_SVC05501SS_1 ( 'SVC' ) }

/*  Date: 08/11/2010  Time: 22:53:03
stmt(12353984):*/
{ CALL TWO.dbo.zDP_RM00101SS_1 ( 'AARONFIT0001' ) }

/*  Date: 08/11/2010  Time: 22:53:05
stmt(597399424):*/
{ CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'sa', -1, 949, 22218, 2 ) }

/*  Date: 08/11/2010  Time: 22:53:05
stmt(597400232):*/
{ CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', -1 ) }

/*  Date: 08/11/2010  Time: 22:53:05
stmt(597401040):*/
{ CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 949, 22218, 2 ) }

/*  Date: 08/11/2010  Time: 22:53:05
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:05
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:05
stmt(597443728):*/
SELECT COUNT(RETTYPE) FROM TWO.dbo.SVC05501

/*  Date: 08/11/2010  Time: 22:53:05
stmt(597446960):*/
{CALL TWO.dbo.zDP_SVC05501F_1(NULL,NULL)}

/*  Date: 08/11/2010  Time: 22:53:05
stmt(597446960):*/
SELECT TOP 25 RETTYPE,DSCRIPTN,RETSTAT,Received_Status,Shipping_Status,Close_Status,RETPATH,VNDWARRTYPE,RTV_Type,DEPOTTYPE,ORDDOCID,CRDOCID,INVDOCID,LOCNCODE,ITLOCN,ITEMNMBR,SVC_Discrepancy_Status,SVC_RMA_Invoice_Batch_ID,SVC_RMA_Credit_Batch_ID,SVC_RMA_Order_Batch_ID,SVC_Sales_Return_Index,SVC_COGS_Index,SVC_Scrap_Index,SVC_Repair_Sales_Index,SVC_Partial_Received_Sta,SVC_Ready_To_Close_Statu,SVCSCRAPCOGREV,DEX_ROW_ID FROM TWO.dbo.SVC05501 WHERE RETTYPE = 'CREDIT' ORDER BY RETTYPE ASC

/*  Date: 08/11/2010  Time: 22:53:07
stmt(597446960):*/
{ CALL TWO.dbo.zDP_SVC05501SS_1 ( 'CREDIT' ) }

/*  Date: 08/11/2010  Time: 22:53:07
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:07
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:12
stmt(597399424):*/
{ CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'sa', -1, 949, 22244, 2 ) }

/*  Date: 08/11/2010  Time: 22:53:12
stmt(597400232):*/
{ CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', -1 ) }

/*  Date: 08/11/2010  Time: 22:53:12
stmt(597401040):*/
{ CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 949, 22244, 2 ) }

/*  Date: 08/11/2010  Time: 22:53:12
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:12
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:12
stmt(12407424):*/
{ CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) }

/*  Date: 08/11/2010  Time: 22:53:12
stmt(597445344):*/
SELECT COUNT(CUSTNMBR) FROM TWO.dbo.SVC3020V WHERE CUSTNMBR >= 'AARONFIT0001' AND CUSTNMBR <= 'AARONFIT0001' AND SOPTYPE = 3   /*  Date: 08/11/2010  Time: 22:53:12  stmt(597443728):*/ SELECT TOP 25 SOPTYPE,SOPNUMBE,DOCID,DOCDATE,LOCNCODE,CUSTNMBR,CUSTNAME,CSTPONBR,DEX_ROW_ID FROM TWO.dbo.SVC3020V WHERE CUSTNMBR >= 'AARONFIT0001' AND CUSTNMBR <= 'AARONFIT0001' AND SOPTYPE = 3 ORDER BY CUSTNMBR ASC ,SOPTYPE ASC ,SOPNUMBE ASC   /*  Date: 08/11/2010  Time: 22:53:13  stmt(597443728):*/ SELECT TOP 25 SOPTYPE,SOPNUMBE,DOCID,DOCDATE,LOCNCODE,CUSTNMBR,CUSTNAME,CSTPONBR,DEX_ROW_ID FROM TWO.dbo.SVC3020V WHERE CUSTNMBR >= 'AARONFIT0001' AND CUSTNMBR <= 'AARONFIT0001' AND SOPTYPE = 3 AND CUSTNMBR = 'AARONFIT0001' AND SOPTYPE = 3 AND SOPNUMBE = 'STDINV2002' ORDER BY CUSTNMBR ASC ,SOPTYPE ASC ,SOPNUMBE ASC   /*  Date: 08/11/2010  Time: 22:53:14  stmt(597399424):*/ { CALL DYNAMICS.dbo.zDP_SY10000SS_1 ( 'sa', -1, 949, 22239, 2 ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(597400232):*/ { CALL DYNAMICS.dbo.zDP_SY10550SS_1 ( 'sa', -1 ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(597401040):*/ { CALL DYNAMICS.dbo.zDP_SY10800SS_1 ( 'DEFAULTUSER', 949, 22239, 2 ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(12387752):*/ { CALL TWO.dbo.zDP_IV40100SS_1 ( 1 ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(597445344):*/ BEGIN DECLARE @stored_proc_name char(29) DECLARE @retstat int set nocount on SELECT @stored_proc_name = 'TWO.dbo.SVC_Check_Dupe_Return' EXEC @retstat = @stored_proc_name 'STDINV2002', 0, '' SELECT @retstat set nocount on END   /*  Date: 08/11/2010  Time: 22:53:14  stmt(597445344):*/ {CALL TWO.dbo.zDP_SOP30300F_1('STDINV2002',3,-2147483648,-2147483648,'STDINV2002',3,2147483647,2147483647)}  /*  Date: 08/11/2010  Time: 22:53:14  stmt(12354792):*/ { CALL DYNAMICS.dbo.zDP_MC40200SS_1 ( 'Z-US$' ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }   /*  Date: 08/11/2010  Time: 22:53:14  stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( 0, 0, 7, 0 ) }   /*  Date: 08/11/2010  Time: 22:53:16  stmt(12354792):*/ { CALL DYNAMICS.dbo.zDP_MC40200SS_1 ( 'Z-US$' ) }   /*  Date: 08/11/2010  Time: 22:53:17  stmt(12364488):*/ { CALL TWO.dbo.zDP_IV00101SS_1 ( 'PHON-ATT-53WH' ) }   /*  Date: 08/11/2010  Time: 22:53:17  stmt(12407424):*/ { CALL DYNAMICS.dbo.zDP_SY02100SS_1 ( -1, 949, 6, 0 ) }   /*  Date: 08/11/2010  Time: 22:53:17  stmt(597443728):*/ BEGIN DECLARE @stored_proc_name char(29) DECLARE @retstat int set nocount on SELECT @stored_proc_name = 'TWO.dbo.SVC_Check_Dupe_Return' EXEC @retstat = @stored_proc_name 'STDINV2002', 32768, '' SELECT @retstat set nocount on END  

A DEXSQL.LOG is a powerful troubleshooting tool and should be the first line of defense before contacting Support. You can take the thinking out of setting up DEXSQL.LOGs when you use Support Debugging Tool.

---

To obtain the Support Debugging Tool be sure to contact your Microsoft partner for a free copy. Be sure to check the the Learning Resources page on this blog to learn more about Support Debugging Tool and other capabilities. Also, the Microsoft Dynamics GP 2010 Cookbook has an entire chapter dedicated to the tool.

---

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/