Here We go :
When you try to execute queries involving OPENROWSET on SQL Server you get the following error:
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource' of component ‘Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries', see “Surface Area Configuration” in SQL Server Books Online.
This occurs basically when Ad Hoc Distributed Queries is disabled.
To enable this:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
Now you can run the following query:
SELECT * FROM
OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=Yes;Database=Northwind','EXEC [CustOrdersDetail] 10248')
Output will be:
Queso Cabrales 14.00 12 0 168.00
Singaporean Hokkien Fried Mee 9.80 10 0 98.00
Mozzarella di Giovanni 34.80 5 0 174.00
Note:
The data which comes from the procedure can also be filtered in the where clause.I tried with small example which is shown below:
SELECT * FROM
OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=Yes;Database=Northwind','EXEC [CustOrdersDetail] 10248') where UnitPrice=14
After running the given above query the result would be like this:
Queso Cabrales 14.00 12 0 168.00
OPENROWSET To run with multi parameter:
SELECT * FROM
OPENROWSET('SQLNCLI','Server=(local);Trusted_Connection=Yes;Database=Northwind','EXEC Employee_Sales_by_Country ''1996-08-01'' ,''1996-10-01''')