“对象已打开"使用 Delphi、SQL Server 和 ADO 时出错 [英] "Object Was Open" error using Delphi, SQL Server and ADO

问题描述

我在打开返回大型数据集(大约 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 时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！