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

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)

Try this in your stored procedure:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Right below the AS.

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)

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

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

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.

Comments
  • I mean the microsoft sql server.
  • Since the error obviously is in the calling code - you need to show us the code calling this stored procedure ....
  • 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!
  • ok I posted it. The error comes from when I try to do a record count.
  • 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.
  • set nocount on did it for us
  • This fixed a problem I had getting a result set from a procedure using BarTender software for printing labels. (I had the same error)
  • Great! Worked smoothly!
  • 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.
  • 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.)
  • I thought ADO's Recordset Open method would implicitly open a connection, if only a connection string was passed.
  • @G. Stoynev, just checked the documentation and it seems you are right.
  • The results of testing on my end disagrees with this suggestion.