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

  • Question

  • 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.  My application has run thousands of different queries across many customer sites without ever hitting this error.

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

    The 12 month query runs perfectly 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;

    And I am setting a large CommandTimeout = 600;

    So why does the query fail in the ADO client (but it works in SQL Mgmt Studio)?

    And what does this "Object was Open" error mean?


    Tuesday, April 22, 2014 3:47 PM

All replies

  • Hi Justin,

    Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    If you have any feedback on our support, please click here.

    Regards,


    Elvis Long
    TechNet Community Support

    Thursday, April 24, 2014 6:53 AM
    Moderator
  • Hi Elvis,

    It would be great of someone can solve this.  Thank you.


    justin

    Monday, April 28, 2014 6:15 AM
  • Hi Justin,

    The below picture is the relationships between the Data Access component. ADO is based on OLEDB, the underlying provide is OLEDB.

    As you can see, ADO provider is based on OLE DB provider or ODBC Driver.

    And DB_E_OBJECTOPEN is the error you experence. It is returned by OLE DB provider (be it SQLOLEDB, SQLNCLI or any 3rd party provider) when it makes a call to ICommand::Execute without closing the result of the previous execution (see http://msdn.microsoft.com/en-us/library/ms718095(VS.85).aspx).

    Did anything change recently in your environment, a new version of the client application was deployed or client driver was upgraded?

    Usually this error occurs because of the problem in the client application, not the client provider. Application should do either of the following:

    • Fully consume the result of the previous command execution prior to issuing a new command. If it doesn't, it might be leaking an object.
    • If the result must be pending, it should turn set DBPROP_MULTIPLECONNECTIONS property to VARIANT_TRUE which will allow underlying OLE DB provider to establish additional connections under the hood. This must only be done if developer is absolutely sure that the application needs the previous result.
    • Enable Multiple Active Result Sets on the connection which would allow running multiple queries on the same connection. This is also must be done consciously, as it may mask object leak.

    Besides, maybe you have known that the CommandTimeout is 30s by default. However, the reason that you can succeed when you use SSMS(SQL Server Management Studio) to run the query is because when using SSMS to create a new connetion, the commandTimeout is 0 by default. It means it has no limitation for the query command time.  But when you use ADO client to create a new connection to run the query, the time which is taken by the query may beyond the commandTimeout setting .So it failed to return the results in your ADO client.

    From a support perspective to further analize this issue is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone."

    Keep us posted.

    Monday, April 28, 2014 10:07 AM
  • Hi Justin,

    If you want to verify if the problem is because of the client application, you can judge it through buliding a profiler trace on your SQL Server to see whether the query running is completed or not. If the query can be finished, then it shows that it is the problem of client application. if not finished, then it would show something error in profiler trace.

    Keep us posted.

    Best regards,

    Tuesday, April 29, 2014 2:11 AM
  • Hi Justin,

    We want to know whether our solution has resolved your problems?
    There are several days you don't contact with us. And we will close the case.
    If there're any questions, don't hesitate to contact with us.
    Thank you for your time.

    Best wishes & regards!

    Monday, May 5, 2014 1:54 AM