Worst. Bugfix. Ever.
I hope that none of you ever, ever have to deal with Sybase ASE ODBC/ADO drivers. Nevertheless, I’m going to document a bug (and a fix/workaround) for using Sybase with Microsoft ADO, because I still can’t believe it.
I first used ADO against a Sybase server about 6 years ago, on a major website project. Over the years, I (and other members of my team) have found that every version of the ADO/ODBC drivers has various issues when used with MSADO. Each new release of ADO and/or Sybase Drivers seems to change subtle behaviors in ADO. We did identify a single version (11.004, I think) that work exactly as it should. Subsequent versions had bugs, so we stuck with what worked.
We recently deployed all new webserver hardware for that original website project. It’s an important website, it processes $12-$15 Billion (yes, that’s a B) in transactions monthly. Needless to say, I spent alot of time installing and testing. Now, the server admins put a new version of the Sybase drivers on ther servers. I was hesitant, but was told that this version worked correctly. And in my testing, it did.
Until we deployed. Seems there was one function I didn’t test, which used ADO‘s .UpdateBatch() against a Recordset opened with a client-side cursor. Shame on me for not testing it; nevertheless, the .UpdateBatch() was failing. Remembering similar issues from the bad-old days of driver shuffling, I tried switching to a server-side cursor. This fixed the problem. Unfortunately, it introduced another… the Sybase server side cursor doesn’t support Recordset filtering (and of course, the Client-side cursor library does).
So now, I was officially damned-if-I-do, damned-if-I-don’t. I immediately (well, right after some choice words) did what any good developer would do… I Googled. And lo, Google was good to me, for I found what I needed immediately. And I was shocked.
The post I found said that you could correct the ‘UpdateBatch failing on client-side cursors’ issue by adding the following line to your data source’s section of your ODBC.ini file:
WorkArounds2=24
Now, I don’t know if this affects the Sybase software or the Microsoft ADO software, but what we have here is a hack. An undocumented hack at that. Why this isn’t a configurable option on the ADO library or the OLEDB/ODBC driver is beyond me. But that’s not the worst part. The person who posted this nugget was in fact posting a solution to a problem they had themselves posted… and they found this answer in a newsgroup post from 1998. Why is this nasty hack still needed, five years later?
So, for the search engines, the above hack will fix and ADO failure cause when trying to run .UpdateBatch against a client-side (adUseClient) Recordset with Sybase ASE drivers. The error message is:
Run-time error '2147467259 (80004005)'; Data provider or other service returned an E-FAIL status.
You may also see a message that the error was raised by the Microsoft Cursor Engine. Also note, ODBC.ini now lives in the registry, at HKLM/Software/ODBC.ini.
Both comments and pings are currently closed.