Excel ADO Recordset Open Fails no error
<p>The following SQL works from the SQL Server 2008 R2 query tools but in Excel ADO RS is closed after the call RS.OPEN... On Error is set but an error does not happened on RS.Open but on the check RS.EOF a "recordset closed" message is thrown. Con is open and works with other RS.Open calls.</p>

<p>The SQL is a bit complex and it may be ADO is not handling it. The SQL builds a CSV list of Facility_IDs. This SQL works in ADO.Net.</p>

<p>Is there something I am missing/ Or is ADO just incapable of dealing with this multi-statement SQL?</p>

<pre><code>    SQL = "declare @tmp varchar(250);" &amp; vbCrLf _
     &amp; " SET @tmp = '';" &amp; vbCrLf _
     &amp; " " _
     &amp; " SELECT @tmp = @tmp + LTRIM(Facility_ID) + ', '" _
     &amp; " FROM  Facility_EI" _
     &amp; " WHERE Cover_Letter = '" &amp; sCategory &amp; "'" _
     &amp; " ORDER BY Facility_ID;" &amp; vbCrLf _
     &amp; " " _
     &amp; " select SUBSTRING(@tmp, 1, LEN(@tmp)-1);"
    RS.Open SQL, con
    If RS.EOF Then
        MsgBox "Category lookup failed: " &amp; sCategory
        GoTo local_exit
    End If
    sFacIDList = RS(0) ' list of facilities in the category
    RS.Close
</code></pre>

<hr>

<p>Here is the cleaned up SQL - modify the First SELECT to use your table for testing:</p>

<pre><code>declare @tmp varchar(250);
SET @tmp = ''

SELECT @tmp = @tmp + LTRIM(Facility_ID) + ', '
FROM  Facility_EI
WHERE Cover_Letter = 'AT&amp;T BUGs'
ORDER BY Facility_ID

SELECT SUBSTRING(@tmp, 1, LEN(@tmp)-1) CSV_List
</code></pre>