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.
OPENQUERY
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
Enabling data access
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
sys.dm_exec_describe_first_result_set_for_object
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