If
you want to use OpenDataSource or OpenRowSet query, then you must have to
enable “Ad Hoc Distributed queries”. It is disabled by default as a part of
security configuration as per Microsoft.
You
can set this option by two ways.
1.) sp_configure
command
2.) Surface
area configuration.
Since
I am a script bee, I will explain steps for sp_configure.
You can run sp_configure to see current status of “run_value” field.
If you don’t able to see “Ad Hoc Distributed queries” in the results set. You have to enable “Show advanced
option” by following command.
sp_configure 'show advanced options',
1
Reconfigure
GO
GO
You
may get message
Configuration option
‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to
install.
Once
you are done with that, you can see “Ad Hoc Distributed queries” in list of
sp_configure. You have to set “Run_value” to “1”. If it is “0” than run
following command.
sp_configure 'Ad Hoc Distributed Queries', 1
Reconfigure
GO
GO
Which
will show you following message.
Configuration
option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE
statement to install.
Once
you will go through the procedure described here, you will be able to run
OpenRowSet and OpenDataSource query.
Read More
No comments:
Post a Comment