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

Since the error obviously is in the calling code - you need to show us the code **calling** this stored procedure .... — marc_s, May 13 '13 at 18:53
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*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, May 13 '13 at 18:53
ok I posted it. The error comes from when I try to do a record count. — omega, May 13 '13 at 18:55
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](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins). — Pondlife, May 13 '13 at 18:58

score 77 · Answer 1 · edited Sep 21 '18 at 10:27

77

Try this in your stored procedure:

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Right below the AS.

edited Sep 21 '18 at 10:27

kenorb

155,785
88
678
743

answered May 17 '13 at 20:47

Ramon

1,016
7
4

1

set nocount on did it for us – Slider345 Dec 17 '13 at 23:55
This fixed a problem I had getting a result set from a procedure using BarTender software for printing labels. (I had the same error) – Chris Feb 25 '14 at 21:40
Great! Worked smoothly! – MarceloBarbosa Dec 09 '14 at 19:32
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. – Tmdean Dec 22 '14 at 17:54
Worked for me. Is there any chance anyone could provide an explanation? – jaysoncopes Mar 02 '15 at 15:05
6

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.) – Tmdean Aug 27 '15 at 03:29
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. – Jeffrey Roughgarden Apr 25 '16 at 18:36

score 3 · Answer 2 · answered Dec 08 '15 at 16:56

3

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

answered Dec 08 '15 at 16:56

user3158212

555
6
16

The results of testing on my end disagrees with this suggestion. – Maritim Aug 26 '16 at 10:25

score 1 · Answer 3 · answered May 16 '13 at 03:03

1

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)

answered May 16 '13 at 03:03

johna

10,540
14
47
72

I thought ADO's Recordset Open method would implicitly open a connection, if only a connection string was passed. – G. Stoynev May 16 '13 at 04:26
@G. Stoynev, just checked the documentation and it seems you are right. – johna May 16 '13 at 07:38

score 1 · Answer 4 · edited Sep 21 '18 at 10:27

1

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

edited Sep 21 '18 at 10:27

kenorb

155,785
88
678
743

answered Oct 23 '15 at 13:16

Sergio Abreu

2,686
25
20

score 0 · Answer 5 · answered Nov 23 '18 at 22:51

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.

score 0 · Answer 6 · answered Sep 04 '19 at 14:41

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.

score 0 · Answer 7 · answered Feb 18 '20 at 19:06

0

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.

answered Feb 18 '20 at 19:06

Greg Sipes

683
6
16

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

7 Answers7

Linked