"object Was Open" Error Using Delphi, Sql Server And Ado

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(20

Solution 1:

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.

Solution 2:

Just change the query of YourADOQuery (if applied) before setting it's Active property to true as below:

YourADOQuery.SQL.Text := 'select top 100 * from ' + YourADOQuery.SQL.Text + ')a';

Note that 100 is NOT '100 PERCENT'! but returns 100 percent of records :)