Also...

    Do NOT use this in a loop or Scalar UDF. This method adds overhead, and should be used as a "last resort". Do not use this method just for the sake of using it, avoid it like you would cursors.

    You will also not get information about the "remote" script within your query plan, it will just show "Remote Scan", so make sure the SP is optimized in itself, and put results in a #Temp table, then use the #Temp table to JOIN with others.

Another "sub-trick"... I've seen some strange things with OLEDB sometimes, so here are two "Variations" on the same theme.

-- OLEDB Variation

select * from OpenRowset('SQLOLEDB',

    'Server=(local);Trusted_Connection=yes',

    'SET FMTONLY OFF SET NOCOUNT ON Exec Master.dbo.sp_who')

-- ODBC Variation

Select * from OpenRowset('MSDASQL',

    'DRIVER={SQL Server};SERVER=(local);Trusted_Connection=yes',

    'SET FMTONLY OFF SET NOCOUNT ON Exec Master.dbo.sp_who')