ADO Object was Open error aka DB_E_OBJECTOPEN on Large Dataset in MS SQL 2012

Hi I am running a query with a big result set in my Delphi application using tADOQuery.

When I request 12 months data I get an error "Object was Open".
But if I run the same query on 1 month of data it works fine.

The mechanism I use to open all queries in my application is the same and has always worked for many years.  This is the first time I have hit this error.

In my research I found that this seems to correspond to an ADO Error DB_E_OBJECTOPEN.

The 12 month query runs OK in SQL Mgmt Studio and takes about 1.5 minutes to start showing results.  But it is 4 minutes before it works out there are 3,810,979 rows.

I am using a client cursor
myADOQuery.CursorLocation:=clUseClient;
LVL 1
enigmasolutionsAsked:
Who is Participating?
 
Geert GConnect With a Mentor Oracle dbaCommented:
hmm, i must be getting old
ssis is http://technet.microsoft.com/en-us/library/ms141026.aspx

it lets you define jobs for data processing
each job can be given a name and started with a command call
something of an advanced scheduler
it can also address multiple types of database and also file systems

i'm not a mssql expert, so coming up with an example would be the same as googling for one

from delphi you would call the stored proc sp_start_job
(got it from this list: http://technet.microsoft.com/en-us/library/ff878099.aspx)
might be best to look at this together with an mssql dba ...
0
 
enigmasolutionsAuthor Commented:
Further to this...

I have tried the following connection strings all to no avail...
(all of these work for all queries except the big one)


Provider=SQLOLEDB.1;Persist Security Info=True;User ID=myUser;Password=myPassword;Initial Catalog=myDB;Data Source=mySERVER


Provider=SQLNCLI11.1;Persist Security Info=True;User ID=myUser;Password=myPassword;Initial Catalog=myDB;Data Source=mySERVER


Provider=SQLNCLI11.1;Persist Security Info=True;User ID=myUser;Password=myPassword;Initial Catalog=myDB;Data Source=mySERVER;MARS Connection=True
0
 
Sinisa VukCommented:
Think, nothing wrong with connection string, but with database design. You shoud put some indexes to tables to speed up query - even 1.5 minutes is a way to much.
Sql query should/can be optimized too. You can put part of it here...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
JimFiveCommented:
I'm guessing that your connection is timing out.
You probably need to add something like
Cmd.CommandTimeout = 300
and possibly
Connection.CommandTimeout = 300
0
 
Geert GOracle dbaCommented:
on a side note
are you actually trying to display nearly 4 million rows ?
i'm not sure if you'll find a user willing to read that many rows ... :)

if you need to process them, consider using the database
a stored procedure ?

i know delphi is great for processing, but it will never beat a stored procedure

limiting the rows returned is also an option
0
 
enigmasolutionsAuthor Commented:
Sinisa, I have added indexes where appropriate.  4 minutes is acceptable given the amount of data being returned.

JimFive, I did set the command timeout to 600 (ten minutes).  It bugged out well before then.

Geert, Yes I know 4 million rows is absolutely ridiculous. But I have a customer with an accountant that wants to export all GL postings in our accounting system to a CSV or PDF or something and send this off to the Australian Tax Office (I am not sure why he needs this).  But he is having fun making us look like our software can't do what the ATO "needs".

Geert, so there is no real processing being done in Delphi, just a whopping dump of dumb data.
0
 
enigmasolutionsAuthor Commented:
Here is the query.  It is a fairly basic query by my standards.
Anyway the point is - it runs OK in SQL Mgmt Studio, but fails in ADO.


--GLDetailReport
DECLARE @GLNodeType varchar(1)     SET @GLNodeType='D'
DECLARE @GLPeriodToParamBnd INT    SET @GLPeriodToParamBnd=201001
DECLARE @GLPeriodFromParamRng INT  SET @GLPeriodFromParamRng=201001
DECLARE @GLPeriodToParamRng INT    SET @GLPeriodToParamRng=201012

SELECT
  GL.GLCode+'   '+GL.GLDescription as Grp1Description,
  GL.GLCode, GL.GLDescription, GL.GLFinanceType, GL.GLNo,
  T.TransRefCode, T.TransYourRef,T.TransComment,
  T_Acc.AccountRef, T_Acc.AccountName,
  TGL.GLCurrency, TGL.GLPaymentType, TGL.GLChequeNo,
  TGL.GLDate, TGL.GLJobCode, TGL.GLCampaignCode,
  TGL.GLCostCentreCode,
  TGL.GLSegmentCode,
  TGL.GLBaseAmt as GLAmt,
  case when TGL.GLBaseAmt>=0 then TGL.GLBaseAmt else null end as DebitAmt,
  case when TGL.GLBaseAmt<0 then -TGL.GLBaseAmt else null end as CreditAmt,
  OB.OpenBalance
FROM GL
JOIN T_TrnGl TGL WITH (INDEX (T_TrnGL_GLNoPeriod_Idx)) --this table is huge
  ON TGL.GLNo=GL.GLNo
  AND TGL.GLPeriod>=@GLPeriodFromParamRng
  AND TGL.GLPeriod<=@GLPeriodToParamRng
LEFT OUTER JOIN --Get GLBalance
    (
       Select GLPRD.GLNo,
         sum(GLBaseActivity) as OpenBalance
         FROM GLPrd                         --this table is really small
           WHERE GLPeriod<@GLPeriodFromParamRng  
       GROUP BY GLPRD.GLNo
    ) OB
  ON OB.GLNo=TGL.GLNo

LEFT OUTER JOIN T_Trans T
  ON T.CompanyNo=TGL.CompanyNo and T.TransNo=TGL.TransNo

LEFT OUTER JOIN T_Acc
  ON T.AccountCompanyNo=T_Acc.CompanyNo and T.AccountNo=T_Acc.AccountNo

WHERE GL.GLNodeType=@GLNodeType
0
 
Sinisa VukCommented:
I know that using sql procedure is almost a "must" in a big projects, but try to use a raw select statement in tadoquery component. Fixed values concatenate as string values. After that set cursor as CursorLocation:=clUseServer.
0
 
Anthony PerkinsCommented:
Anyway the point is - it runs OK in SQL Mgmt Studio, but fails in ADO.
That is only because SSMS has an infinite timeout, so you will need to change the ADO command timeout to something more appropriate than the default 30 seconds.
0
 
Geert GOracle dbaCommented:
djees ... accountants
don't you just love 'em ?

why not use the best of both worlds ?

add a task to ssis with delphi and ado to start the dump ...
wait for it to finish (check periodically ... with a thread ?)
and then pick up the dump (or csv) file wherever the sql server put the huge file
and then ftp that to the whatyacallit_accountant ?
0
 
enigmasolutionsAuthor Commented:
Geert, I think you may be on to a work around.  But what is ssis?  Can I do this programatically?

Also, only problem with this is it is a fair bit of work to change my program to manage this.

I would still like to know what causes this error?



Sinsa, I am sure the query is not the problem.  The syntax of the query is fine.  It runs OK in SQL Mgmt Studio.  So I can certain the problem lies with returning a large dataset through tADOQuery.

Sinsa, OK I can create a standalone application using clUseServer.  But from memory this caused me grief in the past (I can't remember).

Anthony, the query definitely runs in 4 minutes.  I set command timeout to 10 minutes in tADOQuery.  I get the error well before 10 minutes.
0
 
enigmasolutionsAuthor Commented:
Thanks Geert,  I will do some testing...
0
 
enigmasolutionsAuthor Commented:
I have not had time to work on this.  But will resume soon.
In particular I will test Server Cursor in stand alone application.

I was kinda hoping there would be some upgrade to MDAC or something to fix this.
I might test this theory too.
0
 
enigmasolutionsAuthor Commented:
You may be right.

But, I never got time to test it.

Thanks anyway.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.