CODEWITHSUNDEEP
delphiado
Warren P
Warren P

Reputation: 68902

Complex T-SQL script executed via TADOQuery is firing "Multiple-step OLE DB operation generated errors."

I have a very large block of SQL that I am trying to execute inside of Delphi, against a Microsoft SQL Database. I am getting this:

  Multiple-step OLE DB operation generated errors. 
  Check each OLE DB status value, if available. No work was done.

The script has multiple sql IF statements followed by BEGIN and END blocks with invocations of stored procedures, declaration of variables, and EXEC inside that. Finally it returns some of the variable values by SELECT @Variable1 AsName1,@Variable2 AsName2....

The above multi-step error is coming in as an OLEException from ADO, not from the Delphi code, and happens after all the SQL exec-stored-procedure have occurred, and therefore I suspect it's firing this OLE exception when it reaches the final stage which SELECT @Variable1 AsName1,... to get back a few variable values for my program to see them.

I know about this retired/deprecated MS KB article, and this is unfortunately not my actual issue:

http://support.microsoft.com/kb/269495

In short that KB article says to fix a registry key and remove "Persist Security Info" from the connection string. That's not my problem. I'm asking this question because I found the answer already and I think that someone else who gets stuck here might not want to waste several hours finding potential issues when there are several that I have found after searching for solutions for several hours. Anyone who wants to add another answer with different options, is fine, and I'll select yours if it's reproducible, and if necessary I'll turn this one into a Community Wiki because there could be a dozen obscure causes for this "ADO recordset is in a bad mood and is unhappy with your T-SQL" exception.

Upvotes: 1

Views: 1195

Answers (3)

Ali Dehban
Ali Dehban

Reputation: 148

If you have a query with parameter ,check the number of parameters in the query is matched with script...!

Upvotes: 0

Vidra74
Vidra74

Reputation: 86

Potential source of this error is updating char field with large value.

Example: Form has edit box with max length property set to 20 characters and Oracle database table has field defined as char(10). Updating with 10 characters (or less) will work fine while updating with more then 10 characters will cause 'Multiple step...' error on ADOQuerry.UpdateBatch(). You also have to know that CHAR will allways have 20 characters. Consider Trimming value in edit box. CHAR behaves different than VARCHAR2 type.

Upvotes: 1

Warren P
Warren P

Reputation: 68902

I have found several potential causes that are listed in various sources of documentation. The original KB article in the question suggests removing the 'Persist Security Info' from my ADO connection string, however in a standalone test in an application with just a TADOConnection and a single TADOQuery, the presence or absence of Persist Security Info had no effect, nor did explicitly setting it True or False.

What DID fix it was removing this CursorType declaration:

CursorType=ctKeyset

What I have learned is that bidirectional ADO datasets are fine for SELECT * FROM TABLE in ADO but are not so fine for complex SQL scripts.

Upvotes: 2

Related Questions