Search code examples
sql-serverpowershelloctopus-deploy

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

To enable 'Ad Hoc Distributed Queries' I can run the following statement in SQL. Does anyone know how to enable from PowerShell?

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

  • 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.