Solved

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

Posted on 2014-04-14
14
1,172 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
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 1

Author Comment

by:enigmasolutions
Comment Utility
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 25

Expert Comment

by:Sinisa Vuk
Comment Utility
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
Comment Utility
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
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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
Comment Utility
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 25

Expert Comment

by:Sinisa Vuk
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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 36

Accepted Solution

by:
Geert Gruwez earned 500 total points
Comment Utility
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
Comment Utility
Thanks Geert,  I will do some testing...
0
 
LVL 1

Author Comment

by:enigmasolutions
Comment Utility
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
Comment Utility
You may be right.

But, I never got time to test it.

Thanks anyway.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now