I just blogged about CSFBL moving to its new server. All was going well, until I tried to kick off the sim engine, a big part of which is written in Visual Basic 6.0 (cringe, I know, but who has time to rewrite legacy code?).
The sim engine is implemented as a DLL which gets invoked via COM. This part works fine; the DLL is activated, properties are sent to it, and it runs… then crashes, with the following obscure error:
Error Number: -2147217887
Description: Multiple-step operation generated errors. Check each status value.
I searched, and searched… nothing. I recompiled, and checked settings… nothing. I prayed, and finally found this post: http://www.developersdex.com/sql/message.asp?p=581&r=4737805
Here’s what caught my eye first in the post:
Database: SQL Server 2000 Standard (works fine)
Database: SQL Server 2005 Standard (fails)
Hey, that’s exactly the change in my production environment! Reading more, I find that they’re talking about legacy VB6 code, and client-side RecordSets — two things I make extensive use of. Please, don’t make me rewrite all this code, not today…
Reading more (on page two of the thread), I find this:
Now, at last, I have isolated the problem and wasn’t the SHAPE command that
raised the error, but a UNION SELECT in a query of one of my subrecordsets.
Hey, that’s exactly the type of query that is running early on in my code, where I call a stored procedure that uses a UNION SELECT statement. Could this be it?
A hint at a possible fix comes from a DevX forums post, which details the same problem and ends with this:
The workaround I can think of is to bounce data over a temp table.
I quickly opened up the stored procedure with the UNION SELECT statements, rewrote it to remove the UNION statements (using a temp table for temporary data), and recompiled. Rerunning my code yielded…
SUCCESS!!!
It is situations like this where I am reminded how much I can absolutely hate Microsoft. It also reminds me that no two production environments are the same, and a database that is identical in both SQL 2000 and SQL 2005 may not be identical in implementation.
harborpirate says:
I’ve yet to encounter a major version change of any significant enterprise level product, language, or framework that did not involve pain. From SQL Server, to Oracle, to Java, even .Net – there are always outlier cases where if you’re doing just exactly the wrong thing in just exactly the wrong place, you’re screwed. Sometimes that means something truly obscure, sometimes it means something slightly more common, but the pitfalls always seem to be there in any product of sufficient complexity.
Having just recently started upgrading a product I develop with many thousands of users; I can say from experience that as much as you might try, its pretty much impossible to think of every possible scenario when the user base gets large enough. Even with testing until you’re blue in the face, something, however small, will slip through. It just sucks when you happen to be the person to find that small crack to fall through.
brian says:
Couldn’t agree more. Unfortunately, the deliberate attention to testing, testing, and more testing is something I do more for client projects than I do for my own — it’s not a good thing, for sure, and it bites me in the arse more times than I’d like to admit.
Jonas says:
Thank you very much. I have exactly the same problem. Apparently if you use a Case When statement in the select the same thing happens if you try to modify that column in the recordset
Felix says:
Even i had the issue. got resolved by using tempoary table in Stored Procedure.
Discovery says:
总算找到了，thanks.
modify result set output from union sql statements cause error: ‘Multiple-step operation generated errors’