“Object Was Open” error using Delphi, SQL Server and ADO

Question

I am getting an "Object Was Open" error when opening a TADOQuery which returns a large dataset (around 700,000 rows and 75 columns).

8 of my fields are derived fields as varchar(200), and I have found that the error does not occur if I change them to varchar(95) or less, or varchar(256) or more, i.e. the error only occurs in the range 96-255. The error also does not occur if I remove these columns from my query, or if I select less rows.

Googling has suggested that this is a known error with SQLOLEDB with nvarchar fields greater than 127, but that is not the case for me. I am using SQLOLEDB, but I have tried changing to SQL Server Native Client instead and the error still occurs.

Can anyone shed any light on this, I'm stumped. I am using Delphi 5 and SQL Server 2008R2, and the query selects data into a temp table and then selects from the temp table, like this (n.b. this is a simplified version of the actual query, which uses 75 columns and 8 tables):

select memno, surname, forename, 
'EE Conts in Year'= CAST('' as varchar(200)),
'ER Conts in Year'= CAST('' as varchar(200)),
'AVC Conts in Year'= CAST('' AS VARCHAR(200)),
'ERAVC Conts in Year'= CAST('' AS VARCHAR(200)),
'Total EE Conts'= CAST('' AS VARCHAR(200)),
'Total ER Conts'= CAST('' AS VARCHAR(200)),
'Total AVC Conts'= CAST('' AS VARCHAR(200)),
'Total ERAVC Conts'= CAST('' AS VARCHAR(200)),
into #tmptab
from members

select * from #tmptab
order by surname

Thanks

That error corresponds to DB_E_OBJECTOPEN error, which (bugs apart) means your provider would need to open another connection to support the operation. Have you tried what is explained in this link? — Guillem Vicens
Thanks for the info, I have tried the suggestions from that post but to no avail. I tried explicitly setting "multiple connections" to True (although in honesty it alreay was True), I tried using a separate connection just for this query, and I tried using a separate TADOQuery for this query, but still got the error. — Steve Taylor
I seem to recall there were some problems with the Delphi-5 ADO library, and that a patch was released. Have you installed it? Can't tell if it will help but it is worth a try. — Guillem Vicens
Can you edit to add the actual table definition (DDL statements) to create a table that causes the problem? What you've posted so far is asking us to just speculate on what might be the issue based on very little information, and that type of question isn't really appropriate here. — Ken White
Guillem, yes you remember correctly, I am using the patched version of ADO. — Steve Taylor

Wes Wes · Accepted Answer · 2015-03-05T18:55:03

I was receiving the same error and, for me, some changes to my TAdoQuery properties fixed it. My situation is somewhat different than yours, so I'll describe it before getting to the changes that worked for me.

I have a fairly large table; 684,673 rows, 107 columns and a data size of 636240 KB. It has three sets of repeating columns that I'm going to normalize out to three new tables. The query?

SELECT * FROM MyTable

So this is just a straight run through the table, one direction only. The processing has no need for any particular order, so adding indexes, beyond the primary key, won't help. Since I'm making no changes to this table, it's a read-only proposition. Nothing needs to be displayed.

I was receiving the error in the Delphi IDE when I simply tried to set this table's TADOQuery.Active property to true. In other words, just trying to open it in the IDE threw the error. There's no point in examining any of my code before I can successfully open this in the IDE.

I made the following changes to this table's TADOQuery:

CommandTimeout: 600

CursorLocation: clUseServer

CursorType: ctOpenForwardOnly

EnableBCD: False

LockType: ltReadOnly

The error no longer happens, either in the IDE or in my processing code.

It may be that only one of these changes was necessary. If so, I don't know which because I didn't test them one-at-a-time. I simply made all the changes that looked like candidates to give the query the best chance of success.

“Object Was Open” error using Delphi, SQL Server and ADO

2 Answers