Configure component 'Ad Hoc Distributed Queries'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Configure component 'Ad Hoc Distributed Queries'

Author

Topic

SoftFox
Starting Member

42 Posts

Posted - 2008-01-25 : 04:07:03

When i try to use the OPENROWSET function on a particular server i get the 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.."

Does anyone know how exacly you do configure this?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-26 : 00:27:30

Turn it on with sp_configure as said.

SoftFox
Starting Member

42 Posts

Posted - 2008-01-29 : 09:48:25

If there was a prize for stating the obvious..

HOW exactly do you execute this sp to turn on the relevent component. The list genetrated by this sp does not include anything similar to 'Ad Hoc Distributed Queries' to supply as the @configname parameter.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-29 : 10:42:01

http://msdn2.microsoft.com/en-us/library/ms189631.aspx

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

SoftFox
Starting Member

42 Posts

Posted - 2008-01-30 : 09:29:55

Thank you that does it.

naveenoronha
Starting Member

1 Post

Posted - 2008-03-21 : 04:56:54

Hi after I use this statment to turn on 'Ad Hoc Distributed Queries' do I add the same at the end of the code to turn it off?
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 0;
GO
RECONFIGURE;
GO
I will frequently have to access a remote database.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-21 : 14:14:26

you can enable it directly from

SQL surface area Configuration.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-21 : 18:02:21

>> I will frequently have to access a remote database

Then just leave it on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources