Caching stored procedure results

Result sets from SELECT statements can be cached in new database tables using SELECT..INTO. No equivalent exists to collect a stored procedure's first/single result set, so a destination table must be created/declared explicitly in advance, in turn requiring foreknowledge of the returned result set's column types.

A workaround for this is to embed those calls in an OPENQUERY statement:

SELECT *
INTO #myTempTable
FROM OPENQUERY (
  [your server name]
, 'EXEC myStoredProcedure'
)

[your server name] should be as it appears in sys.servers:

SELECT [name]
FROM sys.servers
WHERE server_id = 0

sys.servers contains one row per linked or registered remote server, and a row for the local server that has server_id = 0. The is_data_access_enabled field indicates whether or not a server is enabled for remote querying from the local server.

By default, the local server row is not enabled for remote querying because – under normal circumstances – you don't query the local server remotely! Using OPENQUERY locally as above however requires data access to be enabled:

EXEC sp_serveroption 'your server name', 'DATA ACCESS', TRUE

An alternative approach is to use the DMF sys.dm_exec_describe_first_result_set_for_object. This function returns the list of columns returned by the specified SP's first result set, which could then be used to build an output table:

SELECT
  ', [' + COALESCE([name], 'unnamed_column_' + CAST(column_ordinal AS VARCHAR)) 
      + '] ' + UPPER(system_type_name)
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.usp_Test'), 0)
ORDER BY column_ordinal