We recently received a support case regarding the MO Scheduler utility in the MO Generator Suite. The consultant reported that when attempting to reschedule an MO they received a SQL error:
Conversion failed when converting the varchar value ‘50.01’ to data type int.
Since all WilloWare code has custom error handling (we have our own window for displaying errors) it was immediately clear the error was coming from GP Manufacturing.
“50.01” is a Routing Sequence. We set up a routing with a similar sequence, but the MO Scheduler ran fine. We couldn’t duplicate the issue. The usual suspects for an error like this are either a VBA customization or a SQL table trigger, but neither of those were present.
The consultant provided a SQL trace that showed the scheduling process was failing on a stored procedure called mmopUpdatePicklist. It has three input parameters:
- MO Number
- First Routing Sequence
- End Routing Sequence
The trace also showed that “50.01” was the First Routing Sequence. We changed our routing to make it the first sequence, ran MO Scheduler…and got the error!
Our code was calling the stored procedure through the manufacturing module, and it was (1) correctly finding the first and last sequences, and (2) passing everything in correctly. We then executed the stored procedure in SQL Management Studio:
That also generated the error, so we knew it was not MO Scheduler causing the problem.
A trace of rescheduling an MO in the MO Entry window, which uses mmopUpdatePicklist but does not create an error, produced something odd. The call to the stored procedure looked like this:
mmopUpdatePicklist “MO0196”, “-1”, “-1”
“-1”? Those two fields are supposed on contain the first and last routing sequences. In this case we expected it to look like this:
mmopUpdatePicklist “MO0196”, “50.01”, “900”,
Digging into the Manufacturing source code revealed that mmopUpdatePicklist is used in six different places in the code and five of them do NOT pass in the routing sequence, they pass in a hard-coded value of “-1”! However, the Manual Scheduling window does use the First and Last Routing sequences. We tried rescheduling from that window and got the same error:
Digging further, we investigated the mmopUpdatePicklist stored procedure and discovered that it is hard-coded to recognize “-1” as a magic value.
A “magic value” is a parameter you pass into code to make something happen, and it is a very bad idea. In this case since the input parameters for mmopUpdatePicklist are MO Number, First Routing Sequence and Last Routing Sequence, you should expect those are the values used by the code. Instead, it is using the magic value of “-1” to determine if it should schedule a specific sequence/range (the top case) or all sequences (the bottom case). However, since the magic value of “-1” is an integer and FirstSeq is a string, the IF statement requires SQL Server to do an implicit data type conversion from string (varchar) to integer, and that means if you have routing sequences with letters, or decimal places, it will create an error because there is no way for it to compare “A001” to “-1”.
What would be preferable is to have the code always expect and use two inputted routing sequences. If you give it the first and last it reschedules everything. Or you could give it the 1st and 2nd, or 3rd and 7th to reschedule that range. It would even be reasonable to allow passing in blanks to have it reschedule the entire MO.
If you have routing sequences with letters (or other characters) and you have encountered this issue, there is a relatively simple fix. Just edit the stored procedure to replace -1 with ‘-1’, SQL will treat it as a string (varchar) and everything works fine.