Query Result Set of a Microsoft SQL Stored Procedure

SELECT tmp.*FROM OPENROWSET('SQLOLEDB', 'my_sqlserver_name';'my_sqluser_login';'my_sqlpass word','EXEC mydatabase.dbo.mystoredprodedure') AS tmp

it can also be done without the OPENROWSET (but still OLEDB is used)--configure server for data accessEXEC sp_serveroption @@servername, 'data access', true--get results of a sp like selecting from a tableselect * from openquery(my_servername, 'exec master.dbo.mytesting') a--where my_servername is the name of the sql server, returned by @@servername (not a configured linked server)

If we want to execute a stored procedure using the below statement,
"SELECT Users.* FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC sp_who') AS Users where Users.spid = 10"SQL Server will throw an exception indicating the user to configure "Ad Hoc Distributed Queries" option from 0 to 1. It can be achieved by two options, either using query in Management Studion or else by Surface Area Configuration tool.
1. In Surface Area Configuration tool, MSSQLServer -> Database Engine -> Ad Hoc Remote Queries, Enable OPENROWSET and OPENDATASOURCE support.
2. Using Management Studio execute the below code to enable the Ad Hoc Distributed queries.
EXEC sp_configure 'show advanced options',1reconfigureEXEC sp_configure 'Ad Hoc Distributed Queries',1reconfigure
For more information about configuring server options, see sp_configure (Transact-SQL) in SQL Server Books Online.