sides of march (Brian DeMarzo)

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.