sp_execute_remote only outputs one result set?

I created a stored procedure in an Azure SQL database that does something along the lines of:

CREATE PROCEDURE [dbo].[DoSomething](@Script NVARCHAR(MAX))
AS
BEGIN
    EXEC (@Script)
END

In another Azure SQL database I created an external data source pointing at this and I run the procedure

EXEC sp_execute_remote @datasource, 
        N'EXEC [dbo].[DoSomething] @Script', 
        N'@Script NVARCHAR(MAX)', 
        @Script = ....

The script can contain various different scripts I want to execute remotely. One example would be

SELECT <some columns> FROM <somewhere>
SELECT <different columns> FROM <somewhereelse>

When I run the script through SSMS directly on the target database, "of course" I get the two result sets.

However when I run the script through the stored procedure using sp_execute_remote, I only see the first result set in the output grid in SSMS.

The script doesn't appear to be the problem here. The same thing happens when I simplify the script to its bare essentials:

SELECT 'hello world 1'
SELECT 'hello world 2'

The only thing I see in the output grid in SSMS is hello world 1.

I was also able to confirm that both SELECT statements are actually executed. I did this using a table variable

DECLARE @outputtable TABLE ([output] NVARCHAR(100))
INSERT INTO @outputtable
EXEC ('SELECT ''hello world 1''')

INSERT INTO @outputtable
EXEC ('SELECT ''hello world 2''')

SELECT * FROM @outputtable

Now it shows both outputs.

But of course, in real life it isn't that simple. In real life, the script may output a number of result sets with very different columns in each.

Question: is there anything I can do to make sure all result sets are returned to the SSMS output grid?

Solution

The behaviour you're seeing, with sp_execute_remote, is documented behaviour. From the the Results set section:

Result set

Returns the result set from the first T-SQL statement.

As a result if your procedure returns many result sets you will only see the first, regardless of the client tool you are using; the problem isn't related to SSMS.

If the procedure returns datasets with the same definition, you could CREATE a a (temporary) table, INSERT the data into it and then SELECT from it. Something like:

DECLARE @stmt nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @stmt = N'CREATE TABLE #ProcResults (Col1 varchar(10),' + @CRLF +
            N'                           ...' + @CRLF +
            N'                           Col10 int);' + @CRLF +
            N'INSERT INTO #Procresults (Col1, ..., Col10)' + @CRLF +
            N'EXEC [dbo].[DoSomething] @Script;' + @CRLF +
            N'SELECT * FROM #ProcResults;';


EXEC sp_execute_remote @datasource, 
                       @stmt, 
                       N'@Script NVARCHAR(MAX)', 
                       @Script = ....

If the result sets differ, however, then you will need to separate the separate SELECTs into their own procedures, and execute each separately.