--Вариант 1. Если известно количество столбцов, то самое простое
insert MyTable exec MyStoreProcedure

-- Вариант 2 (если неизвестно количество столбцов) через openquery
-- если надо выполнить хранимку с этого же сервера, то сначала надо выполнить
exec sp_serveroption 'MyServer', 'data access', 'true'

-- Потом делаем select
select t.* from openquery(MyServer,'sp_who') as t

-- Если возникают ошибки, то можно по очереди, выполнять что-нибудь из этого
exec sp_serveroption 'MyServer', 'data access', 'true'
exec sp_serveroption 'MyServer', 'rpc','true'
exec master.dbo.sp_serveroption @server=N'MyServer', @optname=N'remote proc transaction promotion', @optvalue=N'false'

-- Также может помочь "set fmtonly off" (помогло, когда хранимая процедура имеет output-параметр)
select t.* from openquery(MyServer, 'set fmtonly off exec MyServer.test.sysdba.slxnewID [address], null') as t


-- Вариант 3, через openrowset
select * into #t from openrowset('SQLNCLI', 'Server=MyServer;Trusted_Connection=yes;', 'exec msdb.dbo.sp_help_job')