?
Solved

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

Posted on 2014-04-14
14
Medium Priority
?
1,290 Views
Last Modified: 2014-07-01
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;
0
Comment
Question by:enigmasolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 1

Author Comment

by:enigmasolutions
ID: 39999159
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
 
LVL 27

Expert Comment

by:Sinisa Vuk
ID: 40000940
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
 
LVL 15

Expert Comment

by:JimFive
ID: 40001794
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 40004678
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
 
LVL 1

Author Comment

by:enigmasolutions
ID: 40015188
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
 
LVL 1

Author Comment

by:enigmasolutions
ID: 40015249
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
 
LVL 27

Expert Comment

by:Sinisa Vuk
ID: 40015848
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40015973
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
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 40017265
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
 
LVL 1

Author Comment

by:enigmasolutions
ID: 40026624
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
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 2000 total points
ID: 40026900
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
 
LVL 1

Author Comment

by:enigmasolutions
ID: 40033852
Thanks Geert,  I will do some testing...
0
 
LVL 1

Author Comment

by:enigmasolutions
ID: 40098170
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
 
LVL 1

Author Closing Comment

by:enigmasolutions
ID: 40170111
You may be right.

But, I never got time to test it.

Thanks anyway.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question