如何在PowerShell中执行sp_configure启用Ad Hoc Distributed Queries？

There are a couple of straightforward ways to execute those SQL configuration commands in PowerShell. Here are the most common, practical methods:

Method 1: Using Invoke-SqlCmd (Simplest Approach) #

This cmdlet is part of the SqlServer module, the modern, recommended tool for interacting with SQL Server in PowerShell.

1. First, install the module if you haven't already (you might need to adjust your execution policy first with Set-ExecutionPolicy RemoteSigned):

   Install-Module -Name SqlServer -Scope CurrentUser -Force
   

2. Import the module into your session:

   Import-Module SqlServer
   

3. Run the configuration script. Replace YourSqlInstance with your actual instance name (use localhost for the default local instance):

   $configScript = @"
   sp_configure 'show advanced options', 1;
   RECONFIGURE;
   sp_configure 'Ad Hoc Distributed Queries', 1;
   RECONFIGURE;
   "@
   
   Invoke-SqlCmd -ServerInstance "YourSqlInstance" -Database "master" -Query $configScript
   

   Note: You’ll need server-level permissions (like ALTER SETTINGS) to make these changes successfully.

Method 2: Using SQL Server Management Objects (SMO) #

If you prefer an object-oriented approach (no raw SQL needed), use SMO directly:

# Load the SMO assembly (works if SQL Server tools are installed locally)
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

# Connect to your SQL instance
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("YourSqlInstance")

# Enable advanced options first
$server.Configuration.ShowAdvancedOptions.ConfigValue = 1
$server.Configuration.Alter()

# Enable Ad Hoc Distributed Queries
$server.Configuration.AdHocDistributedQueries.ConfigValue = 1
$server.Configuration.Alter()

This method leverages SMO's built-in properties to adjust server settings cleanly.

Quick Verification #

After running either method, confirm the setting is active with:

Invoke-SqlCmd -ServerInstance "YourSqlInstance" -Query "sp_configure 'Ad Hoc Distributed Queries'"

Check the config_value column— it should show 1 if the change worked.

内容的提问来源于stack exchange，提问作者PatrickNolan