Trouble creating a recordset from a SQL Server 2005 query with PIVOT

Question

Here's the output code from my stored proc:

SELECT  *
FROM 
(
    select q.ccypair, q.vega, t.label 
    from #parallel q 
    LEFT JOIN TPRR_vega_weights t ON q.Tenor = t.Tenor
) a
PIVOT 
(
    Sum(Vega)  
    for a.label in ([t1],[t2],[t3],[t4],[t5],[t6],[t7],[t8],[t9],[t10],[t11],[t12],[t13],[t14],[t15],[t16],[t17],[t18])
)p
order by ccypair

Works fine in SQL Server Management Studio, but I get a closed ADODB.Recordset when I try to open it in Excel VBA (I tested the same code with a vanilla select * from x query and it was fine).

Any ideas?

Answer 1

My thought is that ADO doesn't no how to handle the pivot statement correctly. If you where using DAO I would say use a passthrough query, not needed or an option in ADO.

Are you able to put your sql statement in a stored procedure and call the stored procedure? This way ado is only working with the returned data.

Answer 2

in the end, it wasn't the pivot causing the problem, it was just failure to SET NOCOUNT ON in the stored proc