SSRS ad hoc distributed queries issue

Question

I have a big query that I'm trying to execute however SQL SERVER is giving me an error telling me I can't do it because I do not have ad hoc distributed queries enabled. Here's what the error looks like:

After doing some research it looks like I have to enable the option for AD HOC DQ in FACETS after right clicking on the desired database. However, when VIEW FACETS opens up it shows me the different facets with Facet Properties.

The facets are Database, Database Maintenance, Database Options; however, nowhere can I find Ad Hoc Distributed Queries. Anyone have any idea where I can find it?

Answer 1

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.