Re: Temp.Table results from Stored Procedure into rs- ANSWER


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Re: Temp.Table results from Stored Procedure into rs- ANSWER

  1. #1
    gavin Guest

    Re: Temp.Table results from Stored Procedure into rs- ANSWER


    FYI
    ..You have to put
    SET NOCOUNT ON
    ..statement on top of stored procedure line....(oh and its MS-SQL 7)



    "Gavin" <gavin.fairlamb@defence.gov.au> wrote:
    >
    >Hi,
    >I was wondering if any of you out there can help me out please...
    >I have a stored procedure which comprises of a UNION of two SELECT statements
    >storing the results into a temporary table (#Table)...
    >
    >Using ADO I call the stored procedure passing the results into into a recordset.
    >
    >
    >Until now using normal SELECT statements in my Stored procedures I've had
    >no problems. Using the more involved SELECT/Temp table use, Ive struck a
    >problem. I now get the message
    >"Operation is not allowed when object is closed"
    >
    >Now I've tested the exact code switching the Stored Procedure name with


    >a stored procedure used previously and it works fine. I went back to the
    >
    >problem stored procedure and removed the line :
    >DROP Table #Table ..thinking its dropped the results before sending it

    to
    >the recordset...to no avail..
    >
    >My VB referring to the stored procedure call is as follows :
    > adoCommand.CommandText = "StoredProcedure"
    > adoCommand.Commandtype = adCmdStoredProc
    > adoCommand.Name = "MultipleResults"
    >
    > Set adoCommand.ActiveConnection = adoGlobalConn
    > adoGlobalConn.MultipleResults rstGlobal
    >
    >do while not rstglobal.eof <----- error Above given here
    >
    >(My record set is Static, LockBatchOptimistic....)
    >
    >Any assistance would be much appreciated.
    >Many Thanks in Advance,
    >Gavin
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >



  2. #2
    Diane Guest

    Re: Temp.Table results from Stored Procedure into rs- ANSWER


    "gavin" <gavin.fairlamb@defence.gov.au> wrote:
    >
    >FYI
    >..You have to put
    >SET NOCOUNT ON
    >..statement on top of stored procedure line....(oh and its MS-SQL 7)
    >
    >
    >
    >"Gavin" <gavin.fairlamb@defence.gov.au> wrote:
    >>
    >>Hi,
    >>I was wondering if any of you out there can help me out please...
    >>I have a stored procedure which comprises of a UNION of two SELECT statements
    >>storing the results into a temporary table (#Table)...
    >>
    >>Using ADO I call the stored procedure passing the results into into a recordset.
    >>
    >>
    >>Until now using normal SELECT statements in my Stored procedures I've had
    >>no problems. Using the more involved SELECT/Temp table use, Ive struck

    a
    >>problem. I now get the message
    >>"Operation is not allowed when object is closed"
    >>
    >>Now I've tested the exact code switching the Stored Procedure name with

    >
    >>a stored procedure used previously and it works fine. I went back to the
    >>
    >>problem stored procedure and removed the line :
    >>DROP Table #Table ..thinking its dropped the results before sending it

    >to
    >>the recordset...to no avail..
    >>
    >>My VB referring to the stored procedure call is as follows :
    >> adoCommand.CommandText = "StoredProcedure"
    >> adoCommand.Commandtype = adCmdStoredProc
    >> adoCommand.Name = "MultipleResults"
    >>
    >> Set adoCommand.ActiveConnection = adoGlobalConn
    >> adoGlobalConn.MultipleResults rstGlobal
    >>
    >>do while not rstglobal.eof <----- error Above given here
    >>
    >>(My record set is Static, LockBatchOptimistic....)
    >>
    >>Any assistance would be much appreciated.
    >>Many Thanks in Advance,
    >>Gavin
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >



  3. #3
    Diane Guest

    Re: Temp.Table results from Stored Procedure into rs- ANSWER


    I am also having the "operation not allowed if object is closed" problem.
    I have "SET NOCOUNT ON" in the stored procedure. Any other suggestions?


    "gavin" <gavin.fairlamb@defence.gov.au> wrote:
    >
    >FYI
    >..You have to put
    >SET NOCOUNT ON
    >..statement on top of stored procedure line....(oh and its MS-SQL 7)
    >
    >
    >
    >"Gavin" <gavin.fairlamb@defence.gov.au> wrote:
    >>
    >>Hi,
    >>I was wondering if any of you out there can help me out please...
    >>I have a stored procedure which comprises of a UNION of two SELECT statements
    >>storing the results into a temporary table (#Table)...
    >>
    >>Using ADO I call the stored procedure passing the results into into a recordset.
    >>
    >>
    >>Until now using normal SELECT statements in my Stored procedures I've had
    >>no problems. Using the more involved SELECT/Temp table use, Ive struck

    a
    >>problem. I now get the message
    >>"Operation is not allowed when object is closed"
    >>
    >>Now I've tested the exact code switching the Stored Procedure name with

    >
    >>a stored procedure used previously and it works fine. I went back to the
    >>
    >>problem stored procedure and removed the line :
    >>DROP Table #Table ..thinking its dropped the results before sending it

    >to
    >>the recordset...to no avail..
    >>
    >>My VB referring to the stored procedure call is as follows :
    >> adoCommand.CommandText = "StoredProcedure"
    >> adoCommand.Commandtype = adCmdStoredProc
    >> adoCommand.Name = "MultipleResults"
    >>
    >> Set adoCommand.ActiveConnection = adoGlobalConn
    >> adoGlobalConn.MultipleResults rstGlobal
    >>
    >>do while not rstglobal.eof <----- error Above given here
    >>
    >>(My record set is Static, LockBatchOptimistic....)
    >>
    >>Any assistance would be much appreciated.
    >>Many Thanks in Advance,
    >>Gavin
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>

    >



  4. #4
    gavin Guest

    Re: Temp.Table results from Stored Procedure into rs- ANSWER


    Hi,
    I've also got it if I'd reused the recordset and/or ado multiple times and
    closed it without reinitialising the next time..I just double checked my
    loops
    eg.
    dim adoCommand as New ADODB.Command ' open
    Set rstGlobal = new ADODB.recordset

  5. #5
    gavin Guest

    Re: Temp.Table results from Stored Procedure into rs- ANSWER


    Hi,
    I've also got it if I'd reused the recordset and/or ado multiple times and
    closed it without reinitialising the next time..I just double checked
    my loops.
    eg. dim adoCommand as New ADODB.Command ' open
    Set rstGlobal = new ADODB.recordset
    etc.
    set adoCommand = Nothing ' close
    set rstGlobal = Nothing
    regards
    gavin

    >"Diane" <dmcdermo@im.wustl.edu> wrote:
    >
    >I am also having the "operation not allowed if object is closed" problem.
    > I have "SET NOCOUNT ON" in the stored procedure. Any other suggestions?
    >
    >
    >"gavin" <gavin.fairlamb@defence.gov.au> wrote:
    >>
    >>FYI
    >>..You have to put
    >>SET NOCOUNT ON
    >>..statement on top of stored procedure line....(oh and its MS-SQL 7)
    >>
    >>
    >>
    >>"Gavin" <gavin.fairlamb@defence.gov.au> wrote:
    >>>
    >>>Hi,
    >>>I was wondering if any of you out there can help me out please...
    >>>I have a stored procedure which comprises of a UNION of two SELECT statements
    >>>storing the results into a temporary table (#Table)...
    >>>
    >>>Using ADO I call the stored procedure passing the results into into a

    recordset.
    >>>
    >>>
    >>>Until now using normal SELECT statements in my Stored procedures I've

    had
    >>>no problems. Using the more involved SELECT/Temp table use, Ive struck

    >a
    >>>problem. I now get the message
    >>>"Operation is not allowed when object is closed"
    >>>
    >>>Now I've tested the exact code switching the Stored Procedure name with

    >>
    >>>a stored procedure used previously and it works fine. I went back to the
    >>>
    >>>problem stored procedure and removed the line :
    >>>DROP Table #Table ..thinking its dropped the results before sending it

    >>to
    >>>the recordset...to no avail..
    >>>
    >>>My VB referring to the stored procedure call is as follows :
    >>> adoCommand.CommandText = "StoredProcedure"
    >>> adoCommand.Commandtype = adCmdStoredProc
    >>> adoCommand.Name = "MultipleResults"
    >>>
    >>> Set adoCommand.ActiveConnection = adoGlobalConn
    >>> adoGlobalConn.MultipleResults rstGlobal
    >>>
    >>>do while not rstglobal.eof <----- error Above given here
    >>>
    >>>(My record set is Static, LockBatchOptimistic....)
    >>>
    >>>Any assistance would be much appreciated.
    >>>Many Thanks in Advance,
    >>>Gavin
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center