SQL SERVER – Server Configurations Options – Adhoc distributed Queries Option

When Adhoc Distributed Queries Options is enable (value = 1), SQL Server allow use Ad hoc queries through OPENROWSET and OPENDATASOURCE functions using OLEDB provider.

OPENROWSET and OPENDATASOURCE use OLE DB providers, this functions must be used only for data that is infrequently accessed. For data that is frequently accessed, is better use linked servers.

Example:

I have two SQL Server instances:

The first one is the localhost (.), I am going to called Server 1, and the second one is a SQL Server named instance localhost\BKP (.\BKP), I am going to called Server 2.

Server 2.

create database db_test2

GO

use db_test2

GO

create table table2

(

field1 int,

field2 varchar(10)

)

go

insert into table2 values (1,'test')

GO

Server 1.

On the server 1, I check and the Ad Hoc Distributed Queries is disabled. ( run_value = 0 )

sp_configure 'Ad Hoc Distributed Queries'

/*

Result

name minimum maximum config_value run_value

----------------------------------- ----------- ----------- ------------ -----------

Ad Hoc Distributed Queries 0 1 0 0

*/

Then, I test my Ad hoc query using OPENROWSET.

select * from

openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')

I got the following error because the Ad Hoc Distributed Queries option is not enabled.

Msg 15281, Level 16, State 1, Line 1

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.

To execute the Ad hoc query is necessary enable the option and try again the query.

sp_configure 'Ad Hoc Distributed Queries',1

GO

reconfigure with override

GO

/*

Result.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

*/

select * from

openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')

/*

Result.

field1 field2

----------- ----------

1 test

(1 row(s) affected)

*/