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)

http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure?rq=1 — Daniel E., Apr 27 '14 at 17:10
`DECLARE @tblOutputTable` [**STOP** doing this](http://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/), use temp tables. — Kermit, Apr 27 '14 at 17:15
@Kermit: ok, but temp table or table variable, in both cases (in this example) require me to predefine the table. Also, for very small resultsets, or scratchpad queries, [I don't really see the difference](http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server) (and the first example uses a temp table anyway ;) — Abel, Apr 27 '14 at 17:18
@Abel Just got done with SQL Saturday. Brent Ozar demonstrated that statistics on table variables are inaccurate, and table variables should be avoided. — Kermit, Apr 27 '14 at 17:20
@Kermit: I updated the question to use your suggested best practices. (@daniel, that's the same link you gave in the 1st comment) — Abel, Apr 27 '14 at 17:27
@DanielE.: the solution in the linked question does not work with this stored procedure. It throws `The OLE DB provider "SQLNCLI10" for linked server "(null)" supplied inconsistent metadata for a column. The name was changed at execution time.`. — Abel, Apr 27 '14 at 17:56
@DanielE.: yes, I can use any other SELECT-query, just not this one. — Abel, Apr 27 '14 at 18:05
@DanielE.: solution finally found, see http://stackoverflow.com/a/23327256/111575. Thanks for helping! — Abel, Apr 27 '14 at 18:41

score 4 · Accepted Answer · edited May 23 '17 at 12:02

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

All mine needed was the SET FMTONLY OFF; added to my statement. I think my connection was retaining that setting from previous OPENROWSET commands. Thanks for pointing this out! It was non-obvious at the time. — Jeff Fischer, Dec 29 '16 at 15:54

score 2 · Answer 2 · edited Dec 19 '16 at 21:41

We cannot SELECT from a stored procedure thats why SELECT * INTO ..Exec sp_ will not work.

To get the result set returned from a store procedure we can INSERT INTO a table.

SELECT INTO statement creates a table on fly and inserts data from the source table/View/Function. The only condition is source table should exist and you should be able to Select from it.

Sql Server doesn't allow you to use SELECT from sp_ therefore you can only use the INSERT INTO statement when executing a stored procedure this means at run time you can add the returned result set into a table and Select from that table at later stage.

INSERT INTO statement requires the destination table name, An existing table. Therefore whether you use a Temp Table, Table variable or Sql server persistent table you will need to create the table first and only they you can use the syntax

INSERT INTO #TempTable
EXECUTE sp_Proc

You say "you will need to create the table first". Indeed, but that's precisely the point of my question. I don't want to have to do that, with system sp's this can be very tedious to do, esp,. for ad-hoc queries. — Abel, Apr 27 '14 at 18:28

score -1 · Answer 3 · answered Aug 03 '17 at 19:10

-1

Using [YOUR DATABASE NAME]

CREATE TABLE [YOURTABLENAME]
(Database_Name Varchar(128),
DataBase_Size VarChar(128),
unallocated_Space Varchar(128),
reserved Varchar(128),
data Varchar(128),
index_size Varchar(128),
unused Varchar(128)
);

INSERT INTO dbo.[YOUR TABLE NAME]
(
    Database_Name,
    DataBase_Size,
    unallocated_Space,
    reserved,
    data,
    index_size,
    unused

)

EXEC sp_spaceused @oneresultset = 1  
--To get it to return it all as one data set add the nonresultset=1 at the end and viola good to go for writing to a table. :)

answered Aug 03 '17 at 19:10

Jacob Fortuna

29
3

Welcome to SO. It is recommended to give context to an answer, instead of just copying in a snippet of code. Thanks for the effort, but it does not answer the question, your code will create a new actual table in a database, which is not what my original question was asking about (instead, it tried to prevent that). Specifically, I wrote: _"does a more compact expression exist than having to predefine the full table each time"_... Maybe I am missing the point, but than, please edit your answer to clarify. – Abel Aug 17 '17 at 22:01

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

3 Answers3