How to Enable Ad Hoc Distributed Queries

How to enable Ad Hoc Distributed Queries

The following command may help you..

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

How to run sp_configure 'Ad Hoc Distributed Queries' from PowerShell?

Try this, replacing <SQL server> and <database name> with the correct parameters for your environment:

$sql = '@
sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
'@

Invoke-SqlCmd -Query $sql -ServerInstance <SQL server> -Database <database name>

The syntax highlighting here doesn't help illustrate that @' '@ denotes a 'here string', which allows all characters until '@ as the first character on a newline denotes the end of the string.

SSRS ad hoc distributed queries issue

sp_configure is the proc you are looking for. Note that you need to set 'show advanced options' to 1 first to see the Ad Hoc Distributed Queries option at all. Then you need to run the second statement to set it.

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 

sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO  

Simply exec sp_configure to look at your configuration settings.

what's the Risk of Ad Hoc Distributed Queries

Assuming you are going from SQL to SQL, IMO you should try and use sp_addlinkedserver instead - this way your DBA's have better visibility and control into cross server data access.

The only reason I could think why you would want to use OPENROWSET was if you wanted to directly access a non-SQL data store - but you might consider other technologies at application level which might be preferable, e.g. DTS / SSIS, or a custom application with 2 connection strings.

That said, it seems there is a bug with the Ad Hoc Queries option.

---

Related Topics