Use the result of a system stored procedure as a queryable table

Question

Note: the highest linked question does not solve the problem for system stored procedures, but it's close. With help of the commenters, I came to a working answer.

Trying to use statements such as the following for sp_spaceused, throws an error

SELECT * INTO #tblOutput exec sp_spaceused 'Account'
SELECT * FROM #tblOutput 

The errors:

Must specify table to select from.

and:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

When I fully declare a table variable, it works as expected, so it seems to me that the stored procedure does return an actual table.

CREATE TABLE  #tblOutput (
    name NVARCHAR(128) NOT NULL, 
    rows CHAR(11) NOT NULL, 
    reserved VARCHAR(18) NOT NULL,
    data VARCHAR(18) NOT NULL,
    index_size VARCHAR(18) NOT NULL,
    unused VARCHAR(18) NOT NULL)

INSERT INTO #tblOutput exec sp_spaceused 'Response'
SELECT * FROM #tblOutput

Why is it not possible to use a temp table or table variable with the result set of EXECUTE sp_xxx? Or: does a more compact expression exist than having to predefine the full table each time?

(incidentally, and off-topic, Googling for the exact term SELECT * INTO #tmp exec sp_spaceused at the time of writing, returned exactly one result)

Answer 1

TL;DR: use SET FMTONLY OFF with OPENQUERY, details below.

It appears that the link provided by Daniel E. is only part of the solution. For instance, if you try:

-- no need to use sp_addlinkedserver
-- must fully specify sp_, because default db is master
SELECT * FROM OPENQUERY(
   [SERVERNAME\SQL2008], 
   'exec somedb.dbo.sp_spaceused ''Account''')

you will receive the following error:

The OLE DB provider "SQLNCLI10" for linked server "LOCALSERVER\SQL2008" supplied inconsistent metadata for a column. The name was changed at execution time.

I found the solution through this post, and then a blog-post on OPENQUERY, which in turn told me that until SQL2008, you need to use SET FMTONLY OFF. The final solution, which is essentially surprisingly simple (and easier to accomplish since there is no need to specify a loopback linked server), is this:

SELECT * FROM OPENQUERY(
   [SERVERNAME\SQL2008], 
   'SET FMTONLY OFF 
   EXEC somedb.dbo.sp_spaceused ''Account''')

In addition, if you haven't set DATA-ACCESS, you may get the following error:

Server 'SERVERNAME\SQL2008' is not configured for DATA ACCESS.

This can be remedied by running the following command:

EXEC sp_serveroption 'SERVERNAME\SQL2008', 'DATA ACCESS', TRUE