sql - 运行更高级查询时关闭对象时不允许操作

Question

当我尝试在 ASP 页面上运行更高级的 SQL 查询时，出现此错误:

operation not allowed when the object is closed

当我运行这段代码时，它工作正常:

...
sql = "SELECT distinct team FROM tbl_teams"
rs.open sql, conndbs, 1, 1
...

但是当我运行这段代码时(如果我在 Microsoft SQL Server Management Studio 中运行这段代码是有效的)，我得到错误...

...
sql = "DECLARE     @cols AS NVARCHAR(MAX),     @query  AS NVARCHAR(MAX),     @orderby nvarchar(max),     @currentYear varchar(4)  select @currentYear = cast(year(getdate()) as varchar(4))  select @cols   = STUFF((SELECT  ',' + QUOTENAME(year([datefrom]))            from tbl_teams            group by year([datefrom])            order by year([datefrom]) desc             FOR XML PATH(''), TYPE             ).value('.', 'NVARCHAR(MAX)')         ,1,1,'')  select @orderby = 'ORDER BY ['+cast(year(getdate()) as varchar(4)) + '] desc'  set @query = 'SELECT team, Won = [1],                 Lost=[2], Draw = [3]' + @cols + ', Total             from             (               select                 team,                 new_col,                 total                from               (                 select team,                   dt = year([datefrom]),                   result,                   total = count(*) over(partition by team)                 from tbl_teams               ) d               cross apply               (                 select ''dt'', dt union all                 select ''result'', case when dt = '+@currentYear+' then result end               ) c (old_col_name, new_col)             ) x             pivot             (                 count(new_col)                 for new_col in ([1], [2], [3],' + @cols + ')             ) p '+ @orderby  exec sp_executesql @query"
rs.open sql, conndbs, 1, 1
...

这是对查询的更好概述:

DECLARE 
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @orderby nvarchar(max),
    @currentYear varchar(4)

select @currentYear = cast(year(getdate()) as varchar(4))

select @cols 
  = STUFF((SELECT  ',' + QUOTENAME(year([datefrom])) 
           from tbl_teams
           group by year([datefrom])
           order by year([datefrom]) desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @orderby = 'ORDER BY ['+cast(year(getdate()) as varchar(4)) + '] desc'

set @query = 'SELECT team, Won = [1], 
                Lost=[2], Draw = [3]' + @cols + ', Total
            from 
            (
              select 
                team,
                new_col,
                total  
              from
              (
                select team, 
                  dt = year([datefrom]),
                  result,
                  total = count(*) over(partition by team)
                from tbl_teams
              ) d
              cross apply
              (
                select ''dt'', dt union all
                select ''result'', case when dt = '+@currentYear+' then result end
              ) c (old_col_name, new_col)
            ) x
            pivot 
            (
                count(new_col)
                for new_col in ([1], [2], [3],' + @cols + ')
            ) p '+ @orderby

exec sp_executesql @query

我是否需要以另一种方式运行查询，或者这段代码有什么问题？

Answer 1

这是使用 ADODB 时行计数被解释为存储过程输出的常见问题。使用 SQL Server。

为了避免这种情况记得设置

SET NOCOUNT ON;

在您的存储过程中，这将阻止 ADODB 返回一个关闭的记录集，或者如果出于某种原因您不想这样做(不知道为什么，因为您总是可以使用 @@ROWCOUNT 将行计数传回)，您可以用

'Return the next recordset, which will be the result of the Stored Procedure, not 
'the row count generated when SET NOCOUNT OFF (default).
Set rs = rs.NextRecordset()

返回下一个 ADODB.Recordset如果 ADODB 检测到存储过程返回一个(在处理多个 ADODB.Recordset 对象时最好检查 rs.State <> adStateClosed)。