operation not allowed when the object is closed when running more advanced query

When I try to run a more advanced SQL query on an ASP page I get this error:

operation not allowed when the object is closed

When I run this code it’s working:

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

But when I run this code (and this code is working if I run it in Microsoft SQL Server Management Studio), I get the error…

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

This is a better overview of the query:

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

Do I need to run the query on another way or what is wrong with this code?

Answer

This is a common problem caused by row counts being interpreted as output from a Stored Procedure when using ADODB with SQL Server.

To avoid this remember to set

SET NOCOUNT ON;

in your Stored Procedure this will stop ADODB returning a closed recordset, or if for whatever reason you don’t want to do this (not sure why as you can always use @@ROWCOUNT to pass the row count back), you can use

'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()

which returns the next ADODB.Recordset if ADODB has detected one being returned by the Stored Procedure (might be best to check rs.State <> adStateClosed when dealing with multiple ADODB.Recordset objects).