Tag Archives: ‘Ad Hoc Distributed Queries’

Solve the problem that SQL Server blocks the state’openrowset/opendatasourc of component ‘ad hoc distributed queries’

1.1 connection

select * from openrowset( 'SQLOLEDB ', 'sql server name '; 'username '; 'password ',database name.dbo.table name)

1.2 error prompt

Message 15281, level 16, status 1, line 3
sql server has blocked access to the state “openrowset/opendatasource” of the component “ad hoc distributed queries” because the component has been shut down as part of the security configuration of this server. System administrators can use sp_ Configure enables “ad hoc distributed queries”. For more information about enabling ad hoc distributed queries, search for ad hoc distributed queries in SQL Server Books Online

2. Solutions

2.1. Open the ad hoc distributed queries component and execute the following statements in the SQL query editor:

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

2.2. Close the ad hoc distributed queries component and execute the following statements in the SQL query editor:

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure