Many times in the past I wondered why we have 'Select Into' while 'Exec into' is of the absent.
Off course you can use OPENROWSET for this, But you'll need to allow remote access to the server (which will have security ramifications) as well as enabling 'ad-hoc distributed queries'.
Also, insert the connection string right and specify the full path to the stored procedure including the database name and the sp's owner.
Another issue with OPENROWSET is that for order to insert into a TEMP table you'll need to SET FMTONLY OFF (in this case please notice that the SP will be ran twice).
To try and simplify this issue I created a SP that will return a table to read from.
I called this SP 'spGetTableFromProcedure'.
This SP can capture multiple result sets delivered by a single stored procedure execution, however all result sets must have the same Schema (column number, type and order).
This SP Get 5 params:
@Sp_Name: This param holds the name of the SP.
@DBName: The DB name you want to execute from. default is NULL which will give current DB.
@SchemaName: The SP's schema. Default is 'dbo'.
@Parameters: The parameters the SP gets. Default is NULL.
@NewTable: Will hold the name of the new table you want to EXEC INTO. Param default is '##TempFor'.
* Please notice that if you'll ask for a regular temp table (#Temp) it will be changed to '##Temp' (This is because the #Temp table created at a "lower level" than the block of executing code).
Please notice that SPs that use temp table can't be EXEC INTO.
Download the script.