« Legguffin My Eggmcmuffin | Main | Bicycle Shorts »
June 15, 2006
From the Valley
Our ERP package (the software we use to run the company) uses Oracle 8 tables to store data.
We use several tools to slice and dice the data -- though we've never been able to get the julienne fries attachment to work -- so we're able to create output in and eyeball- an brain-friendly format.
One of the tools we use is Microsoft Access.
We have hundreds of databases using Access as the front end. When these were created, everyone in the building had the Oracle 8.0.something ODBC driver installed. And everything worked well for a good long time. (I want to say it was 8.0.1).
Years would pass, the amount of data we had to sort through grew, Bush would take a holiday in Iraq, decide his friends at Haliburton could use come cash, invade, and we started getting ODBC errors. The errors were related to queries running too long and timing out; the Iraq thing may have been coincidence.
The solution was to install the Oracle 8.1.7 ODBC driver. I know what you're thinking. How could Bush be such a continual f*ck up? I don't know.
Or ... 8.1.7 is only 0.1.6 newer than 8.0.1 -- how could it make such a difference? Well, there's a checkbox that comes with this driver that says: Enable Query Timeout. Just uncheck it.
And that works some of the time. Other times you also have to edit the registry and tweak one of the query settings for Microsoft Access. I don't know why this isn't necessary all of the time.
Oh! And a single OH doesn't begin to provide enough exclamation for this ... it really needs something like, Oh! oh! oh!.
Oh! Oh! Oh! You might think that installing the new ODBC driver/ Oracle client would be as easy as starting the install and choosing the Uninstall option. This option will find the old files, go through a song-and-dance routine, and assure you everything has been removed that needs removed. AND when you go to install the new version things look good ... progress bars move from left to right showing progress ... there's another song and dance (some soft-shoe, this time, if I'm not mistaken) ... and a big finish.
And then, of course, it doesn't work.
To make it work you have to go through the registry and remove the remnants of the stuff that the uninstaller missed. Do this before running the new install. It may be that you can run the uninstaller and then Microsoft's Registry Cleaner to put things right -- I've only tried this once.
So now you've got the new Oracle driver installed and working.
You run many, many, many Access reports without any sort of problem. In the back of your mind a tidbit of knowledge is floating around reminding you that once in a while the new driver causes a report that ran well with the old driver to not run at all.
To summarize/clarify:
- The first driver ran all the reports. As the data grew, we started getting timeout errors and some reports quit working.
- The new driver does not bomb with query timeouts ... but some of the queries started kicking out ODBC errors involving oddball outer-join errors.
Recently, month-end rolled around and all of the accountants were in as was the CEO. The CEO called me at home, apologized for bothering me, and said he was receiving an ODBC error. I thought it was the timeout error and was a bit embarrassed as I'd sat in his office and fixed it for him just the month before -- nothing screams incompetent IT guy like screwing up the month-end and the CEO's ability to generate reports.
Fortunately, our CEO is a pretty nice guy.
On Monday he demonstrated the ODBC error. It kicked out too quickly to be a timeout issue.
I looked at the database and then the query in the privacy of my own office. A sort in a make-table query was causing the ODBC error. This shouldn't happen. Fixing it was as simple as removing the sort -- but then our data was stored out of order in a table ... a table that will probably never be looked at (it's there just in case), but out of order nonetheless.
Fixing it after the query was run would add two steps for people higher up the food chain than I.
I eventually added a search-criteria string to the portion of the query that did the sorting. The string converted a text value to a numeric value. A sort without the string caused the query to bomb. The same query, with the same sort, and the addition of the unneeded-otherwise search criteria and we have smooth sailing.
Things should be okay so long as none of our product codes begin with negative numbers.
Things like this should not happen.
Posted by delmer at June 15, 2006 8:40 AM
Comments
Wow, taht was a whole lot of nerdyness. impressive performance my man ;O) Though it sounds a lot like being productive, watch yourself on taht would ya? :O)
Posted by: Chief Slacker at June 15, 2006 12:05 PM
Man, if you had been employed at GE, they would've fired you right there if the CEO couldn't get his reports done. But then again, that's why they called him "Neutron Jack."
I'm so relieved I don't work there anymore.
Posted by: The Phoenix at June 15, 2006 3:46 PM