Solved

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

Posted on 2014-04-14
14
1,260 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 37

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 37

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 37

Accepted Solution

by:
Geert Gruwez earned 500 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

729 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