Ad Hoc Distributed Queries Error

I am getting the following error when with SQL Express.

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.

While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on...

I tried sp_configure'Ad Hoc Distributed Queries', 1 but got the following error

The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have...

Could someone help me out with this?

Thanks - Mark

As 'Ad Hoc Distributed Queries' is an advanced option, you need to turn on advanced options. Try the statements below:

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

|||

FANTASTIC! Quick and easy!

That works - Thanks

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.

Hi all,

I am currently working on a stored procedure in SQL 2000 where I use OPENROWSET function to read data from an Excel file into a temporary table.

It works fine when I logged in with username 'sa' and psswrd 'sa' but when I log in with another user name and password I get the following error:

"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.40' has been denied.
You must access this provider through a linked server."

I am using VB 6.0 as front end. Is there anyway i can overcome this error?

Please help.

Dhiraj

I just started having this issue too. I had this working in SQLEXPRESS, but now I am moving to a new SQL Server (Version 3054) and started getting this error.