問題描述
我在打開返回大型數據集(大約 700,000 行和 75 列)的 TADOQuery 時收到對象已打開"錯誤.
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 個字段是作為 varchar(200) 的派生字段，我發現如果我將它們更改為 varchar(95) 或更少，或 varchar(256) 或更多，則不會發生錯誤，即僅出現錯誤發生在 96-255 的范圍內.如果我從查詢中刪除這些列，或者選擇較少的行，也不會發生該錯誤.
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.
谷歌搜索表明這是 SQLOLEDB 的一個已知錯誤，nvarchar 字段大于 127，但對我來說并非如此.我正在使用 SQLOLEDB，但我已嘗試改為使用 SQL Server Native Client，但仍然出現錯誤.
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.
任何人都可以對此有所了解，我很難過.我正在使用 Delphi 5 和 SQL Server 2008R2，查詢將數據選擇到臨時表中，然后從臨時表中選擇，如下所示(注意這是實際查詢的簡化版本，它使用 75 列和 8 個表):
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
謝謝
推薦答案
我收到了同樣的錯誤，對我來說，對我的 TAdoQuery 屬性進行了一些更改修復了它.我的情況與您的有些不同，因此在討論對我有用的更改之前，我將對其進行描述.
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.
我有一張相當大的桌子；684,673 行，107 列，數據大小為 636240 KB.它有三組重復列，我將把它們標準化為三個新表.查詢?
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.
當我只是嘗試將這個表的 TADOQuery.Active 屬性設置為 true 時，我在 Delphi IDE 中收到了錯誤.換句話說，只是嘗試在 IDE 中打開它會引發錯誤.在我可以在 IDE 中成功打開它之前，檢查我的任何代碼都是沒有意義的.
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.
我對此表的 TADOQuery 進行了以下更改:
I made the following changes to this table's TADOQuery:
命令超時:600
CursorLocation:clUseServer
CursorLocation: clUseServer
光標類型:ctOpenForwardOnly
啟用BCD:假
鎖定類型:ltReadOnly
錯誤不再發生，無論是在 IDE 中還是在我的處理代碼中.
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.
這篇關于“對象已打開"使用 Delphi、SQL Server 和 ADO 時出錯的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！