Alter Table Mystery Fail

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s