Skip to content
+1-888-319-3663

COMMUNITY FORUM

Smartlist that works against SOP Detail History hangs

Joseph Hohe asked 2 years ago
Our partner recently combined the sales order header & line detail history from another GP company into our main GP company growing the data set significantly.  After this was done any Smartlist run against the line detail (SOP30300) will hang and never complete.  Smartlists run against the SOP30200 run fine.  My suspicion is that the process of bringing in the other company’s data corrupted a record in SOP 30300.  My question is to inquire if there is a way to determine what is causing the Smartlist to fail, and if it is a corrupted record(s), is there a way to identify that record(s)
Answers
Patrick Roth Staff answered 2 years ago
I’m assuming this is a SmartList Builder report that you created vs the canned report.
When SLB executes a report, it generate the SQL created and then runs SQL_Execute() to pull the resultset.
So in your case, either one of two things is likely happening.
1. SQL query timeout.  You don’t say how long this takes to execute – it could be exceeding the default GP sql connection/query timeout.
https://mightycode.blogspot.com/2019/06/
look at your dex.ini SQLQueryTimeout value.
Setting this to 0 means “infinite”
You can test “how long is this running” by enabling your GP DexSQL.Log and then running the report in SmartList.  At that point you can get the exact query from SLB and then run in SSMS.
The dexsql.log might also show any SQL errors that SLB isn’t telling you.
2. You could be returning “too much data” for Dexterity to handle.  When you run a SQL_Execute() all of the rows/columns comes back in as a dataset.  So the report will run for however long it takes to get that resultset and THEN starts to display the data.
So it could be that we get too much data back.  I’d think that GP would crash or throw other unhappy errors if this is happening though.
But worth maybe looking into running the report into a smaller company and verify it finishes.  And if so then make a favorite that has a smaller record count in order to not run it with so much data.
The other thing you could looking is use something like SmartView which uses its own data connection instead of Dexterity SQL_Execute().  SmartView Internal still has limitations due to GP being 32bit.  But SmartView External is 64bit and so these limitations are now “very large” and I shouldn’t expect to see any “memory type” issues.
 
 
Joseph Hohe answered 2 years ago
Unfortunately it is the “canned” report.  My partner is convinced it is not bad data, but it is the clumsy manner in which that query was constructed.  He is attempting to modify it to run better.  I will let you know the result. 

If you would like to submit an answer or comment, please sign in to the eOne portal.