Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

When I run a query with OPENROWSET in SQL Server 2000 it works.

But the same query in SQL Server 2008 generates 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

i try to run

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

But any attempt to run RECONFIGURE gives the error:

Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.

How do i enable Ad Hoc Distributed Queries in SQL Server 2008 R2?

Note: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

From here: http://sqlserverpedia.com/blog/database-design/error-message-ad-hoc-update-to-system-catalogs-is-not-supported/

Either run this first:

EXEC sp_configure ‘allow updates’, 0
RECONFIGURE

or change your RECONFIGURE statements to RECONFIGURE WITH OVERRIDE:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE --really reconfigure
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE --really reconfigure
GO
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.