"Operation is not allowed when the object is closed" when executing stored procedure

Question

This is my stored procedure, and when I am calling it from my classic ASP code, I am getting the error:

Operation is not allowed when the object is closed.

when I try to do a record count.

Does anyone know what is wrong here?

I am trying to return the table @t.

Thanks.

USE [Hires_new]
GO
/****** Object:  StoredProcedure [dbo].[sp_selectNewHireWorkPeriodsSQL]    Script Date: 05/13/2013 14:04:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      
-- Create date: 
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[sp_selectNewHireWorkPeriodsSQL] 
    -- Add the parameters for the stored procedure here

AS

    declare @t table (HireID int, StartDate datetime, EndDate datetime, date_initiated datetime, date_closed datetime, firmName nvarchar(100), InquiryID int)
    DECLARE @acc INT 
    SET @acc = 1
    DECLARE @max INT 
    select @max = max(HireID) from NewHire
    WHILE (@acc <= @max)
        BEGIN
            IF (@acc in (select HireID from NewHire))
                BEGIN
                    insert into @t  
                        select HireID, StartDate, EndDate, date_initiated, date_closed, firmName, Inquiries.InquiryID 
                        from WorkPeriod, Firms, Inquiries 
                        where HireID = @acc and WorkPeriod.FirmID = Firms.FirmID and WorkPeriod.InquiryID = Inquiries.InquiryID 
                        order by HireID,StartDate DESC
                END
            set @acc = @acc + 1
        END
    select * from @t

Asp classic code

selectNewHireWorkPeriodsSQL = "EXEC sp_selectNewHireWorkPeriodsSQL"
Set rsNewHireWorkPeriods = Server.CreateObject("ADODB.Recordset")
rsNewHireWorkPeriods.Open selectNewHireWorkPeriodsSQL,ConnectionString,adOpenStatic
NumOfNewHireWorkPeriods = rsNewHireWorkPeriods.RecordCount

response.write(NumOfNewHireWorkPeriods)

Sherwoodsherwynd

I mean the microsoft sql server. – Sherwoodsherwynd

Since the error obviously is in the calling code - you need to show us the code calling this stored procedure .... – Circus

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! – Circus

ok I posted it. The error comes from when I try to do a record count. – Sherwoodsherwynd

As another side note, there seems to be no need for a loop in this code: you could write the whole thing as a single SELECT statement by adding a join on NewHire. And it would be best to write your joins explicitly. – Scaramouch

How would you re write it? – Sherwoodsherwynd

Answer 1

Try this in your stored procedure:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Right below the AS.

Mannes

set nocount on did it for us – Bengt

This fixed a problem I had getting a result set from a procedure using BarTender software for printing labels. (I had the same error) – Impeccant

Great! Worked smoothly! – Peursem

It also works outside the stored procedure, if you're in a situation where you can't modify it. Just add them before the EXEC call in your query. – Jule

Worked for me. Is there any chance anyone could provide an explanation? – Include

SET NOCOUNT on prevents SQL server from returning the "x records affected" output when your queries contain updates or inserts. I can't remember the exact reason, but ADO gets confused when there's text output from a query. (I think it misinterprets it as an error message and aborts.) – Jule

FWIW, I did not need to 'set ansi warnings off'. Also, I found that the behavior was not consistent. Sometimes things worked without 'set nocount on' and sometimes they did not. However, using 'set nocount on' everywhere did fix things nicely. – Torosian

Answer 2

If, for whatever reason the stored procedure does not return a result set, empty or otherwise, the recordset object will not be open, so:

if rs.state = adStateOpen then x = rs.recordcount

Daugherty

The results of testing on my end disagrees with this suggestion. – Coplanar

Answer 3

You need to create an active connection first, and pass this to the recordset object, like this:

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open(ConnectionString)

selectNewHireWorkPeriodsSQL = "EXEC sp_selectNewHireWorkPeriodsSQL"
Set rsNewHireWorkPeriods = Server.CreateObject("ADODB.Recordset")
rsNewHireWorkPeriods.Open selectNewHireWorkPeriodsSQL,conn,adOpenStatic 'dont use connection string here
NumOfNewHireWorkPeriods = rsNewHireWorkPeriods.RecordCount

conn.Close
Set conn = Nothing

response.write(NumOfNewHireWorkPeriods)

Coincidental

I thought ADO's Recordset Open method would implicitly open a connection, if only a connection string was passed. – Scurrility

@G. Stoynev, just checked the documentation and it seems you are right. – Coincidental

Answer 4

Warnings may confuse the result. SET ANSI_WARNINGS OFF avoids losing the SELECT result or output parameter values.

Gatepost

Answer 5

I am sure that this will not affect many people, but I just stumbled upon this issue. This was working in production and not in the development environment. What I found was that our stored procedure had a print statement in the development environment. I guess the print statement was mucking up the works and ADODB thought that was the record set.

Tews

Answer 6

I know that this is very old. But in my case, it was the order of parameters. It worked after I set the parameters as they appear in the stored procedure. I know that there is no logical explanation to this as parameters are named and the order should not matter really.

Litt

Answer 7

This can be caused by a print statement in your stored procedure. I accidently left a few in after some performance debugging....hopefully this helps someone still working in legacy ADO.

Scots