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