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

[gavin]

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
>
>
>
>
>
>
>
>
>
>
>
>
>

[Diane]

"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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>

[Diane]

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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>

[gavin]

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

[gavin]

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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>