So I’ve got a bit of code in one of my products that does about 30 Alter Table Add Column commands in a row. Has worked fine for 15+ years, but today it starts failing for one of my clients.
The failure is inconsistent – some fields won’t get added some times, other times other fields aren’t added. It was usually 2-4 fields out of the 30 that wouldn’t add. Even more bizarrely, there’s no error message. The command acts like it works, carries on merrily, but the field is not added to the table.
I know about the problem with Alter Table and Cursors, but this is a table. Tried a FLUSH. Tried a NOVALIDATE. Those didn’t help.
In the end, I simply had to add some code to double-check if the field was actually in the table (the Field() worked reliably). If the field did not exist, I paused for a second and then retried the Alter Table command. I maxed out the retry at 10 times, but I never saw it take more than 1 try to work.
This was a brand new laptop running Windows 10. No idea what the underlying cause is, but I’m thankful I do all my Alter Table commands through a generic utility rather than having to search through all my code, for all my clients, and fix all of them.