Operation is not allowed when Object is Closed

  • Vishal Prajapati-253528

    Ten Centuries

    Points: 1142

    Hi,

    I have a SP which gives the me the report of 12 months. I have a query in WHILE LOOP which execute 12 times my taking month number from 1 to 12 and accordingly gives the result as per each month. The result of gets store into HASH table for all 12 months.

    I run the SP through Query Analyser locally and it takes around 30 - 40 sec. When I run the same SP on Client Server Query Analyser it takes around 1 min and 30 sec approx. 

    But same SP when I call thru VB code which has ODBC connection to get the result in the front-end it gives me following error " ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed.  "

    When I run the Profiler I found that Query batch at 4th month have StartBatch but it does not have CompleteBatch and after which the Profiler returns nothing.

    Also query time out is set to 0. And connection time of in VB code is also set to 0.

    Please help.

     

     

     

     


    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • Jo Pattyn

    SSC-Dedicated

    Points: 31258

    Can we see the code of the stored procedure.

    Is the hash table a temporary table (last for one connection) or a more permanent one?

  • Vishal Prajapati-253528

    Ten Centuries

    Points: 1142

    Hi,

    Here is the SP ........

    ALTER PROC getaverage

    AS

    SET ANSI_WARNINGS OFF

    SET NOCOUNT ON

     CREATE TABLE #tblAverage (

      [ID] int,

      [Month]  varchar(100),

      [Value]  varchar(20))  

     DECLARE @NoofMonth int

     SET  @NoofMonth = 0

    WHILE @NoofMonth < 12

    BEGIN 

     

    INSERT INTO #tblAverage SELECT DISTINCT TABLE1.ID, 'Month ' + cast(@NoofMonth + 1 as varchar), count(TABLE2.User) FROM TABLE1, TABLE2

    WHERE TABLE1.ID = TABLE2.ID AND DATEPART(mm,TABLE1.DT) = @NoofMonth

    AND  DATEPART(yy,TABLE1.DT) = '2004'

    GROUP BY TABLE1.ID

     

     

     SET @NoofMonth = @NoofMonth + 1

    END

    SELECT * FROM #tblAverage 

     

    DROP TABLE #tblAverage

    SET NOCOUNT OFF

    SET ANSI_WARNINGS ON

    GO

     

    ------------------------------------------------

    AGAIN SP WORKS FINE Locally in Query Analyser and also AT Client's Server Query Analyser.

     


    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • Alex Otten

    Old Hand

    Points: 396

    How did you create your query in VB?

    If you are using the Recordset.Open to execute a SELECT statement you will have to close the recordset yourself, but with the INTO statement the Recordset is closed after is has been executed.

    Hope this helps,

    Alex Otten

  • Stewart Joslyn

    SSCertifiable

    Points: 6131

    This looks like a problem with your VB code. Are you using a server-side cursor? If so, try making it client side. An example (omitting any parameters for the sp) :

    Set l_cmdCommand = New ADODB.Command

    With l_cmdCommand

    .ActiveConnection = objDatabase.Connection

    .CommandType = adCmdStoredProc

    .CommandText = ProcedureName

    .ActiveConnection.CursorLocation = adUseClient

    Set l_rsRecordset = .Execute

    End With

  • Vishal Prajapati-253528

    Ten Centuries

    Points: 1142

    Hi,

    Thanks for you reply.....

    But this does not help out as we are making use of client side cursor only......

    Please help


    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • Lowell

    SSC Guru

    Points: 323356

    your error is on the vb handling side....

    ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed.  "

    this error occurs if you refer to the recordset in vb code , but the recordset did not find any records, ie:

    SQL="SELECT * FROM SOMETABLE WHERE X=" & somVBVariable

    Set rs= Conn.Execute(SQL)

    sometextbox.text=rs!SomeField

    The above statement will raise the error above if the rs.EOF was true:

    it should be: SQL="SELECT * FROM SOMETABLE WHERE X=" & somVBVariable

    Set rs= Conn.Execute(SQL)

    if not rs.eof then

    sometextbox.text=rs!SomeField  ' rs("SomeField").Value is the eqivilient syntax

    end if

     

    other similar error s will happen if the rs!SomeField was null, and you try to stick it directly into a variable or object property. (Invalid use of null for example);

    you could also get this same error if you are using the following code:

    SQL="SELECT * FROM SOMETABLE WHERE X=" & somVBVariable

    Set rs.open SQL,Conn

     

    if you did not first do a Set rs=New ADODB.Recordset prior ti the rs.Open command; using the Conn.Execute makes that step unnecessary.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Vishal Prajapati-253528

    Ten Centuries

    Points: 1142

    Hi,

    Thanks for reply.....

    I told in my original post that it works locally very fine. Also when the SP is run through the Query Analyser of client server, there also it run and gives the prefect result without any NULL values.

    Also when I tried to figured out problem from Profiler I found that in loop after the Month 4 NO statement is executed. (Remember again SP runs fine in Query Analyser, of all 12 months it returns the result)

    Please help......


    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • Lowell

    SSC Guru

    Points: 323356

    with sample data, in QA, it works fine....i agree. it has nothing to do with the quality of your SQL/procs.

    but in the real world application, your data is returning no records in certain conditions.(after month4?)

    when no data is returned, your VB application  are not handling the lack of data correctly.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Stewart Joslyn

    SSCertifiable

    Points: 6131

    Are you passing parameters into your stroed procedure? If so, are you sure that they are being passed correctly? Possibilities could be variable type mismatches, padding or ascii vs unicode.

    Have you checked the ADO error collection?

  • Vishal Prajapati-253528

    Ten Centuries

    Points: 1142

    HI,

    Sorry to bother you....

    But it does not take any parameter. I just call the SP and it gives me the result for 12 months. The same SP when I run through Query Analyser on Client server through "EXEC SPname" it works fine.

    Same data Same Calling statement just the medium of calling is different.

    Again, when I call it thorough my VB code the SP run till 4 months and at 5months it stops. If at 5months it is hanging then it should happen same when I run through Query Analyser at Client Server.

    Please Help


    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • Stewart Joslyn

    SSCertifiable

    Points: 6131

    Is it timing out, either on SQL Server or on the ADO connection object (they are separate settings)?

  • Vishal Prajapati-253528

    Ten Centuries

    Points: 1142

    Hi,

    I have set this at Infinite ( connection timeout = 0)

    And that too at both end. At Sql Server also and in ADO connection also. BUT 🙁

    Please don't be harsh.... but this a problem that I am facing. Also my senior programmer had hand on it but it VAIN.

    Please help dear....


    Kindest Regards,

    Vishal Prajapati

    DBA at Extentia Infromation Technology

  • Stewart Joslyn

    SSCertifiable

    Points: 6131

    You say it stops or hangs. Does SQL batch actually finish and return partial results or is it in a wait state or locking problem? Although the message that you quoted was ADO, perhaps there is something in the SP. Would you post the SP, please.

  • iv69

    Old Hand

    Points: 376

    use Set nocount on

    before Insert statement

    and set nocount off

    after Insert Statement

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply