We can Enable this feature by two ways:
1. SQL Server Surface Area Configuration.
2. by sp_configure option.
Lets check with first way, by SQL Server Surface Area Configuration.
Open surface Area configuration, you will get this screen:
Click on second option, Surface Areas Configuration for Features. you will get this screen, where you need to check to Enable OPENROWSET and OPENDATASOURCE support.
Lets see second option to enable this feature with sp_configure option:
If you run this command, you will lists of SQL configuration settings. There are 14 items in the list in which 'Ad Hoc Distributed Queries' is not exist. To see this, we need to enable the 'show advanced options' configuration parameter.
You can enable advance options by:
sp_configure 'show advanced options',1
When we run this command we will get this message:
“Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.”
so we need to execute reconfigure command as:
so now if we run sp_configure again, we will get result set as follows:
Here, we can find that config_value for “Ad Hoc Distributed Queries” is “0”. We need to set it to 1 to enable this feature. so to do that we need to use following:
sp_configure 'Ad Hoc Distributed Queries',1
so, now if we run sp_configure, we will get result as follows:
Here, we can find that now config_value for “Ad Hoc Distributed Queries” is “1”.
That’s it, now you can use OPENROWSET and OPENDATASOURCE to connect with remote database without Linked server.
Let me know if it helps you in any way.
Posted by Tejas Shah on November 11, 2017
Posted by Tejas Shah on August 12, 2015
Posted by Tejas Shah on November 22, 2017
Posted by Tejas Shah on February 26, 2009
Posted by Tejas Shah on February 09, 2009