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

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

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...

I'm guessing that your connection is timing out.
You probably need to add something like
Cmd.CommandTimeout = 300
and possibly
Connection.CommandTimeout = 300

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

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.

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

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.

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.

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 ?

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.

Thanks Geert,  I will do some testing...

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.

You may be right.

But, I never got time to test it.

Thanks anyway.