Inventory Trx History Runtime Error Macexe.exe abnormal program termination

[proksy]

Hello everyone,

I have run into this problem in the past and was able to resolve the issues but it has resurfaced and I am completely stumped. I need help!

DESCRIPTION:
Client: Macola Progression SQL Explorer 7.6.400
Database: MS SQL Server 2005
When a user goes to [Inventory Management] >> [View] >> [Inventory Trx History] then enters an item number and location the Macola client crashes. In the past the issue was invalid data and was very easy to identify. I am betting invalid data is still the issue.

ACTIONS TAKEN:
In the past what would happen is a custom software application wasn't automatically casting item numbers to upper case before writing to the macola database tables. These issues were easy to find and simple to fix as all I had to do was view the iminvtrx_sql table and identify a lowercase letter. I've looked in iminvtrx_sql and everything seems to look fine. I then ran the [File Validation] tool and identified and fixed a lot of PRODUCT CATEGORY MISMATCH issues in the iminvloc_sql table. After running [File Validation] and fixing those issues the Macola client would now work but only if "ALL" locations was chosen. If any specific location was chosen the program would crash. I have gone into other [Inventory Management] tables and made sure the product category was the same for all the records for a particular item number but with no success.

Because the client now only crashes when a user selects a location and not "ALL" I am inclined to think that something is wrong with the iminvloc_sql or imlocfil_sql tables. But when I run [File Validation] on iminvloc no errors are returned and when I view those tables everything looks fine. What are all of the tables used or involved in the [Inventory Trx History] module? I can only think of three fore sure 1) imitmidx_sql, 2) iminvloc_sql, 3) iminvtrx_sql Many, many thanks for any help!

Thanks

 

[dgillz]

Do you get this error on the Macola Electronics company? If you do, this rules out a data problem. Please test this and let us know.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[proksy]

Sorry for my ignorance but..."electronics company?" What/Where is this?

 

[dgillz]

When you install Macola, a demo company called "Macola Electronics" is automatically installed. The database is called "demodata" and is company 0. If this isn't there, someone purposefully removed it or purposefully did not install it (it istalls by default), which is a TERRIBLE idea. It exists for just these types of events. If the report runs in the Macola Electronics company, it points to a data issue, if it returns the same error, it points to your environment.

So do you have this company or not?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[proksy]

Oh!.....yes we do have it. What report should I be running? I am specifically having problems with [Inventory Management] >> [View] >> [Inventory Trx History]. The item numbers we have problems with will not exist in this demo company "Macola Electronics". I will run File Validation as I assume this is the "report" you are referring to. Thanks for the help!

EDIT: Just so I am clear...the majority of our item numbers we have no problems with what-so-ever. Of course we have 10s of thousands of item numbers but we are experiencing problems with maybe say 100 of them. Macola client crashes in Inventory Trx History and the inventory counts are always off for these item numbers.

 

[proksy]

We do have the database "demodata". I do not experience this problem using the "Macola Electronics Company" using [Inventory Management] >> [View] >> [Inventory Trx History]. I am well versed in SQL and know that if I knew all of the tables that are involved in the Inventory Trx History module I could identify the problem and fix it. Once I resolve this issue I will of course post the solution to this thread and my blog. Thanks for the help dgillz I very much appreciate it

 

[dgillz]

The only table you need to look at is the IMINVTRX_SQL table. Yes I would run file validations reports on the IMITMIDX and IMINVLOC for these items. There is no file validation for the IMINVTRX. Also open up the table in SQL Management studio and see if there is "garbage" data at the very top or the very bottom of the table. If there is, delete those records.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[proksy]

Alright here is my update. The IMINVTRX_SQL table looks fine. Checked the records at the top and bottom and found no garbage data. I ran file validation on both IMITMIDX_SQL and IMINVLOC_SQL for a list of item numbers that are known to have the issue I am describing but the file validation reports came back with "no errors were found" on all of them.

 

[dgillz]

Can you run inventory transaction history reports? This is just the report version of the view you are trying to run.

Also here are some queries that will make sure that the locations in various tables actually exist in the imlodfil_sql:

select item_no, loc from imitmidx_sql where loc not in (select loc from imlocfil_sql)

select item_no, loc from iminvloc_sql where loc not in (select loc from imlocfil_sql)

select item_no, loc from iminvtrx_sql where loc not in (select loc from imlocfil_sql)

I would also consider running the file validations against the entire IMITMIDX and IMINCLOC tables.

Finally are there any error messages when this crashes? If so can you post screenshots?

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[proksy]

Believe it or not I have already run those queries. There are a few in there that do not have a location and I am fixing those. Only problem is none of these are the item numbers that are crashing Progression Explorer. I did run file validations for the entire imitmidx and iminvloc tables and none of those in the report are the item numbers I am having problems with/crashing Macola. All that are reported work just fine. I ran the inventory trx history report for a problem item number and it crashed with exactly the same error. "Runtime Error Macexe.exe abnormal program termination" and "Abnormal Cleanup of MacSqlConnMan in MacMSS.dll, No Server Exit Op provided! Some Client and Server resources may be let in an unknown state." The report left me with a clue however because it gives me the id_no of the line item that crashes Progression SQL Explorer. Here are some screenshots of the errors:

 

[dgillz]

Go to RMA setup in Order Entry and make sure this screen is filled out and saved.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[proksy]

Checked RMA Setup in O/E. Only two fields there, "Next RMA No." and "Days To Receive". Both of these fields have a value of 0 (zero). I have no idea what these values should be and I will tell you that I really don't think we use the O/E functions very often here as we have custom software integrated into Macola. I apologize for my ignorance on this module. I went into [Order Entry] >> [View] >> [RMA View] >> [By Item No.] searched all item numbers and "No Records On File" I did this as well for [Order Entry] >> [View] >> [RMA History View] >> [By Item No.] and got the same result. I am not 100% sure what tables our custom orders program writes to or uses but I know for sure oeordlin_sql, oelinhst_sql, oeordhdr_sql and oehdrhst_sql are. I can track down the source code and verify all tables used. The orders program we use was not developed/coded by me, is pretty old at this point and has been a source of errors in the past. I have my suspicions it is the culprit again. Let me know what you think and if you need any more info about this I'll get it to you asap. Thanks a lot dgillz(don) for the help and advice.

 

[dgillz]

Put in 1 for next RMA and 30 for days and save it.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[dgillz]

A few more things to try or think about:

In your screenshot, you were printing to file. If you print to a printer does this make a difference?

Were you using Progression Explorer or PWE? Log in to the "other" interface and run these reports or views again. Are the errors any different? If so please post screenshots.

Can you rebuild the iminvtrx table? This would have to be done while no one else is in the system. Do the same with the imitmidx and imlocfil.

In your screenshot, I cannot see what report options you choose on the right side of the screen. Can you show these options?

In your screenshot, T:\ is the mapped drive for Macola. Is this actually the drive letter in Company Setup?

You mentioned lowercase items and product category mismatches. Did you check for these in EVERY table, or just the IMITMIDX and IMINVLOC? Product categories in particular are captured on the OEORDLIN table.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[proksy]

The RMA setup didn't work. We are using Progression SQL Explorer and I personally have never used PWE and no one I work with here have ever used it. When I try to open it I receive the error "Unexpected error; quitting". T:\ is the mapped drive for our Macola "TEST" server that we restore backups to in order to test the integrity of them and troubleshoot issues without disrupting users on a live database. Our production server uses M:\ As far as rebuilding iminvtrx, imitmidx and imlocfil, I can totally do this but in the past I think I remember running this and when it reaches a record with a column value of null it wipes the entire table out. Since I'm on a test server I don't mind running a rebuild on these at all as I can just restore from a backup. You mentioned whether I checked EVERY table or just imitmidx and iminvloc...I have checked oeordlin and some others and noticed some issues in there. That is why in my OP I wanted to know if there were more tables involved in the [Inventory Trx History] module than imitmidx, iminvloc, imlocfil and iminvtrx. I think the problems could totally be in the OE tables and think we are on the right track. This makes sense too because the custom orders program we use is 1) OLD/I didn't develop it and 2) has presented invalid data issues in the past with lowercase letters and what not. I am going to try to dig up the most current source code I can for that custom application and see what tables are used. I will report back what I find. I will try running rebuilds on the IM tables you listed earlier and report back as well. Can't tell you how much I appreciate your help. If I could send you an adult beverage through the computer, I would make it a top shelf drink for sure.

 

[proksy]

Here is a screenshot of the options or preferences used in the report

 

[dgillz]

Open up company setup and make it is set to T in the drive letters.

Also open up macsql.cfg and make sure the server for the company you are working in is pointed to the test server and database. Making a separate shortcut to T:\Macsql is not enough.


Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[dgillz]

On your customer orders application, you should consider the Wisys API for Macola. Their objects follow Macola business logic 100% and you would totally eliminate any data issues. Also it seamlessly upgrades to Macola 10, no re-write needed. Finally, depending on exactly what needs to be done, you may not even need to write any code. I know that sounds unbelievable but it is true.

If you are interested in a demo of this let me know.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[proksy]

The company setup is not the issue I can assure you. Everything is setup appropriately. It is a data issue for sure. Even if I were to go with wisys api it wouldn't solve the issues I am having currently. Not to say I'm not interested in that solution in the future but I have to fix what is occurring now. Do you know what tables, any and all, that are used in the Inventory Trx History Module? If I can know what tables are involved I can fix the data issues and once I do I can have these conversations with upper management about wisys api and an upgrade to Macola 10. I know I'm repeating myself but seriously, thanks for your input and help figuring this out.

 

[dgillz]

PWE was released in 1999 there is zero reason why you are not using it. You do not need to upgrade to Macola 10 to use Wisys. I have already mentioned the tables used. If you contact me offline I will look at it for free, I have done pretty much all I can do in a forum.

Software Training, Implementation, Programming and Support for Macola Progression, Macola ES, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html