Re: Operation is not allowed when the object is closed

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 12/30/04 

Date: Wed, 29 Dec 2004 22:51:23 -0600

Try adding SET NOCOUNT ON to the beginning of your PROC. This will suppress
DONE_IN_PROC messages that are returned as empty closed recordsets to ADO. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Conax" <ConaxLiu@hotmail.com> wrote in message 
news:eDXdiIi7EHA.1404@TK2MSFTNGP11.phx.gbl...
> Hello!
>
> Error Message: "Operation is not allowed when the object is closed."
>
> What I have is a stored procedure on SQL server that goes and checks data 
> on
> a table and populates a temporary table (declared within the stored
> procedure) with record ID and description of invalid records. At the end 
> of
> the stored procedure, I use a select statement to return the records from
> this temporary table to the caller.
>
> I then use VB6 to execute this stored procedure and expect the return
> recordset. While the recordset object is returned, it is closed.
>
> I tested by changing the select statement to select records from a real
> table, and the recordset returned is also closed. So there would be
> something that I do before calling the select statement that's causing the
> problem...I think.
>
> Any help or suggestion is very much appreciated.
>
>
> Sample Code:
>
> CREATE PROCEDURE CHECK_Table
> AS
>
> DECLARE @ErrorLimit int
> DECLARE @ErrorCount int
> DECLARE @TotalCount int
> DECLARE @TempTable TABLE(RowNo int, Reason varchar (1000))
>
> SET  @ErrorLimit = 100
> SET  @ErrorCount = 0
> SET  @TotalCount = 0
> SET  DATEFORMAT dmy
>
> /***************************************************************************
> ***********************
> Validate Record Type : Must be ADT
> ****************************************************************************
> **********************/
> IF @ErrorCount < @ErrorLimit
> BEGIN
>
> INSERT INTO  @TempTable (RowNo, Reason)
> SELECT TOP 100 RowNo + 1, 'Invalid record type'
> FROM   dbo.MyTable
> WHERE  RecordType Is Null
> OR   RecordType <> 'INV'
> ORDER BY  RowNo
>
> SET   @ErrorCount = @ErrorCount + @@ROWCOUNT
>
> END
>
> DELETE FROM  dbo.MyTable
> WHERE  RecordType Is Null
> OR   RecordType <> 'INV'
>
> SET   @TotalCount = @TotalCount + @@ROWCOUNT
>
> /***************************************************************************
> ***********************
> Check for duplicate records
> Key : Code 1
> ****************************************************************************
> **********************/
> IF @ErrorCount < @ErrorLimit
> BEGIN
>
> INSERT INTO  @TempTable (RowNo, Reason)
> SELECT TOP 100 RowNo + 1, 'Duplicated record'
> FROM   dbo.MyTable INV,
>   (
>    SELECT Code1
>    FROM  dbo.MyTable
>    GROUP BY Code1
>    HAVING (Count(RowNo) > 1)
>   ) Dups
> WHERE  INV.Code1 = Dups.Code1
> ORDER BY  RowNo
>
> SET   @ErrorCount = @ErrorCount + @@ROWCOUNT
>
> END
>
> DELETE FROM  dbo.MyTable
> WHERE  RowNo In
>   (
>    SELECT RowNo
>    FROM  dbo.MyTable INV,
>      (
>       SELECT Code1
>       FROM  dbo.MyTable
>       GROUP BY Code1
>       HAVING (Count(RowNo) > 1)
>      ) Dups
>    WHERE INV.Code1 = Dups.Code1
>   )
>
> SET @TotalCount = @TotalCount + @@ROWCOUNT
>
> /***************************************************************************
> ***********************
> Insert total error count
> ****************************************************************************
> **********************/
> IF @ErrorCount > 0
> BEGIN
> INSERT INTO @TempTable (RowNo, Reason)
> VALUES (0, Cast(@TotalCount As varchar(1000)))
> END
>
> -- Ensure that only first 100 reasons + 1 total record are returned to 
> avoid
> overflowing
>
> SELECT TOP 101 *
> FROM   @TempTable
> ORDER BY  RowNo ASC
>
> GO
>
>
>
>

Relevant Pages

  • Operation is not allowed when the object is closed
    ... What I have is a stored procedure on SQL server that goes and checks data on ... DECLARE @ErrorLimit int ... DECLARE @ErrorCount int ... INSERT INTO @TempTable (RowNo, Reason) ...
    (microsoft.public.data.ado)
  • Operation is not allowed when the object is closed
    ... What I have is a stored procedure on SQL server that goes and checks data on ... DECLARE @ErrorLimit int ... DECLARE @ErrorCount int ... INSERT INTO @TempTable (RowNo, Reason) ...
    (microsoft.public.sqlserver.programming)
  • Re: Operation is not allowed when the object is closed
    ... > What I have is a stored procedure on SQL server that goes and checks data ... > DECLARE @ErrorLimit int ... > INSERT INTO @TempTable (RowNo, ... > GROUP BY Code1 ...
    (microsoft.public.sqlserver.programming)