＆QUOT;当对象被关闭＆QUOT不允许操作;在执行存储过程时 [英] "Operation is not allowed when the object is closed" when executing stored procedure

问题描述

这是我的存储过程，当我从我传统的ASP code调用它，我得到错误：

  

当对象被关闭操作是不允许的。

当我尝试做一个记录计数。

有谁知道什么是错在这里？

我试图返回表 @t 。

感谢。

 使用[Hires_new]
走
/ ******对象：StoredProcedure的[DBO] [sp_selectNewHireWorkPeriodsSQL]脚本日期：2013年5月13日14时04分12秒****** /
SET ANSI_NULLS ON
走
SET QUOTED_IDENTIFIER ON
走
 -  =============================================
 - 作者：
 - 创建日期：
 - 说明：
 -  =============================================
ALTER PROCEDURE [DBO]。[sp_selectNewHireWorkPeriodsSQL]
     - 添加参数的存储过程在这里如    声明@t表（HireID INT，起始日期日期时间，结束日期日期时间，日期时间date_initiated，date_closed日期时间，firmName为nvarchar（100），InquiryID INT）
    DECLARE @acc INT
    SET @acc = 1
    DECLARE @Max INT
    选择@Max从NewHire = MAX（HireID）
    WHILE（@acc＆LT; = @Max）
        开始
            IF（@acc中（选择NewHire HireID））
                开始
                    插入@t
                        选择HireID，起始日期，结束日期，date_initiated，date_closed，firmName，Inquiries.InquiryID
                        从WorkPeriod，企业，咨询
                        其中，HireID = @acc和WorkPeriod.FirmID = Firms.FirmID和WorkPeriod.InquiryID = Inquiries.InquiryID
                        为了通过HireID，起始日期DESC
                结束
            集@acc = @acc + 1
        结束
    选择@t *
 

经典的ASP code

  selectNewHireWorkPeriodsSQL =EXEC sp_selectNewHireWorkPeriodsSQL
设置rsNewHireWorkPeriods =的Server.CreateObject（ADODB.Recordset）
rsNewHireWorkPeriods.Open selectNewHireWorkPeriodsSQL，ConnectionString，则ADOPENSTATIC
NumOfNewHireWorkPeriods = rsNewHireWorkPeriods.RecordCount的Response.Write（NumOfNewHireWorkPeriods）
 

解决方案

在您的存储过程试试这个：

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

右键下方的AS。

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.

这篇关于＆QUOT;当对象被关闭＆QUOT不允许操作;在执行存储过程时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！