OPENROWSET function can be used to query on the database exists in different server or query on the stored procedure. Consider that the system procedure sp_who2 gives informations about the current users, sessions, etc.
But note that the resultset has two columns with the same name SPID. So the openquery function will result to an error if you run this query
select * from OPENROWSET('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','exec master.dbo.sp_who2')
The error is
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec master.dbo.sp_who2". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
So you need to aware of this that when you run a OPENROWSET function against the procedure whose resultset has same column names will throw an error
Tags: tsql, BRH, SQL Server, #TSQL, #SQLServer,