ADO Connection.Execute keeps returning a closed recordset

Question

I'm trying to return the results of a sql command, passed to SQL server, into Excel using ADO objects, but my Recordset keeps coming back closed/empty. My guess is that my sql statment is not return anything - though I think it SHOULD be returning a single record.

The SQL I'm running is a 530 line beast, but it boils down to a MERGE statement that is supposed to dump results into a table var and then select from that. This select statement is what I want to have returned into an ADO record set.

Here is the generalized SQL statement I'm using:

     MERGE ldw_plan_working AS target 
 USING source 
 ON target.DT_Code = source.DT_Code 
    AND target.Country = source.Country 
    AND target.Channel = source.Channel 
    AND target.HierarchyID = source.HierarchyID 
    AND target.lifecycle = source.lifecycle 
     WHEN MATCHED 
     THEN UPDATE SET 
                     [Sales_TOT_Local] = source.[Sales_TOT_Local], 
                     [Sales_TOT_USD] = source.[Sales_TOT_USD], 
                     [Sales_TOT_CAD] = source.[Sales_TOT_CAD], 
                     [Sales_CLR_Local] = source.[Sales_CLR_Local], 
                     [Sales_CLR_USD] = source.[Sales_CLR_USD], 
                     [Sales_CLR_CAD] = source.[Sales_CLR_CAD], 
                     [PM_TOT_Local] = source.[PM_TOT_Local], 
                     [PM_TOT_USD] = source.[PM_TOT_USD], 
                     [PM_TOT_CAD] = source.[PM_TOT_CAD], 
                     [PM_CLR_Local] = source.[PM_CLR_Local], 
                     [PM_CLR_USD] = source.[PM_CLR_USD], 
                     [PM_CLR_CAD] = source.[PM_CLR_CAD], 
                     [Sales_TOT_U] = source.[Sales_TOT_U], 
                     [Sales_CLR_U] = source.[Sales_CLR_U], 
                     [Receipt_USD] = source.[Receipt_USD], 
                     [Receipt_U] = source.[Receipt_U] 
     WHEN NOT MATCHED 
     THEN 
       INSERT([DT_Code], 
              [Country], 
              [Channel], 
              [HierarchyID], 
              [Lifecycle], 
              [Sales_TOT_Local], 
              [Sales_TOT_USD], 
              [Sales_TOT_CAD], 
              [Sales_CLR_Local], 
              [Sales_CLR_USD], 
              [Sales_CLR_CAD], 
              [PM_TOT_Local], 
              [PM_TOT_USD], 
              [PM_TOT_CAD], 
              [PM_CLR_Local], 
              [PM_CLR_USD], 
              [PM_CLR_CAD], 
              [Sales_TOT_U], 
              [Sales_CLR_U], 
              [Receipt_USD], 
              [Receipt_U]) 
       VALUES 
 (source.[DT_Code], 
  source.[Country], 
  source.[Channel], 
  source.[HierarchyID], 
  source.[Lifecycle], 
  source.[Sales_TOT_Local], 
  source.[Sales_TOT_USD], 
  source.[Sales_TOT_CAD], 
  source.[Sales_CLR_Local], 
  source.[Sales_CLR_USD], 
  source.[Sales_CLR_CAD], 
  source.[PM_TOT_Local], 
  source.[PM_TOT_USD], 
  source.[PM_TOT_CAD], 
  source.[PM_CLR_Local], 
  source.[PM_CLR_USD], 
  source.[PM_CLR_CAD], 
  source.[Sales_TOT_U], 
  source.[Sales_CLR_U], 
  source.[Receipt_USD], 
  source.[Receipt_U] 
 ) 
 OUTPUT $action 
        INTO @tableVar;
INSERT INTO @tableVar(MergeAction) 
VALUES('nothing'); 
SELECT [UPDATE], 
       [INSERT], 
       [DELETE] 
FROM @tableVar PIVOT(COUNT(MergeAction) FOR MergeAction IN([UPDATE], 
                                                       [INSERT], 
                                                       [DELETE], 
                                                       [nothing])) AS piv;

And the vba function looks like this:

Public Function Run_SQL_Cmd(sql As String)
Dim cnn As Object
Dim cmd As Object
Dim rs As Object
Dim recordsAffected As Integer

Set cnn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")

cnn.Open strBEConnection
cnn.CommandTimeout = 0

With cmd
    .ActiveConnection = cnn
    .CommandText = sql
    .CommandType = 1
    Set rs = .Execute
End With

'... do stuff with the recordset

rs.Close
cnn.Close
Set cnn = Nothing
Set rs = Nothing

End Function

rs always comes back as a 0 field count, closed record set that I can't do anything with. But when I run the same sql directly in SQL server, obviously I do get the results of that final select statement. Anyone know what I'm doing wrong here?

Thanks in advance

Answer 1

Alright, I figured it out. When you have several SQL statements in the command text, you need to precede it all with a "SET NOCOUNT ON;" in the SQL statement. This will have the server return the results of the final select statement. Pretty annoyingly simple fix.