Invoke-Sqlcmd

構文

Invoke-Sqlcmd
      [-ServerInstance <PSObject>]
      [-Database <String>]
      [-Encrypt <String>]
      [-EncryptConnection]
      [-Username <String>]
      [-AccessToken <String>]
      [-Password <String>]
      [-Credential <PSCredential>]
      [[-Query] <String>]
      [-QueryTimeout <Int32>]
      [-ConnectionTimeout <Int32>]
      [-ErrorLevel <Int32>]
      [-SeverityLevel <Int32>]
      [-MaxCharLength <Int32>]
      [-MaxBinaryLength <Int32>]
      [-AbortOnError]
      [-DedicatedAdministratorConnection]
      [-DisableVariables]
      [-DisableCommands]
      [-HostName <String>]
      [-ApplicationName <String>]
      [-ApplicationIntent <ApplicationIntent>]
      [-MultiSubnetFailover]
      [-FailoverPartner <String>]
      [-HostNameInCertificate <String>]
      [-TrustServerCertificate]
      [-NewPassword <String>]
      [-Variable <PSObject>]
      [-InputFile <String>]
      [-OutputSqlErrors <Boolean>]
      [-IncludeSqlUserErrors]
      [-SuppressProviderContextWarning]
      [-IgnoreProviderContext]
      [-OutputAs <OutputType>]
      [-StatisticsVariable <String>]
      [-ProgressAction <ActionPreference>]
      [<CommonParameters>]

Invoke-Sqlcmd
      [-AccessToken <String>]
      [[-Query] <String>]
      [-QueryTimeout <Int32>]
      [-ErrorLevel <Int32>]
      [-SeverityLevel <Int32>]
      [-MaxCharLength <Int32>]
      [-MaxBinaryLength <Int32>]
      [-AbortOnError]
      [-DisableVariables]
      [-DisableCommands]
      [-Variable <PSObject>]
      [-InputFile <String>]
      [-OutputSqlErrors <Boolean>]
      [-IncludeSqlUserErrors]
      [-OutputAs <OutputType>]
      -ConnectionString <String>
      [-KeyVaultAccessToken <String>]
      [-ManagedHsmAccessToken <String>]
      [-StatisticsVariable <String>]
      [-ProgressAction <ActionPreference>]
      [<CommonParameters>]

説明

Invoke-Sqlcmd コマンドレットは、SQL Server SQLCMD ユーティリティでサポートされている言語とコマンドを含むスクリプトを実行します。

サポートされるコマンドは、Transact-SQL ステートメントと、データベース エンジンでサポートされている XQuery 構文のサブセットです。

このコマンドレットは、GO や QUIT など、SQLCMD でネイティブにサポートされているコマンドの多くも受け入れます。

このコマンドレットは、SQLCMDUSER などの SQLCMD スクリプト変数も受け入れます。 既定では、このコマンドレットは SQLCMD スクリプト変数を設定しません。

このコマンドレットは、主に対話型スクリプト編集に関連するコマンドの使用をサポートしていません。

サポートされていないコマンドには、:!, :connect、:error、:out、:ed、:list、:listvar、:reset、:p erftrace、および :serverlist が含まれます。

このコマンドレットを実行すると、スクリプトから返される最初の結果セットが書式設定されたテーブルとして表示されます。

後続の結果セットに最初の列リストとは異なる列リストが含まれている場合、それらの結果セットは表示されません。

最初のセットの後の後続の結果セットに同じ列リストがある場合、その行は、最初の結果セットによって返された行を含む書式設定されたテーブルに追加されます。

Verbose パラメーターを指定することで、SQL PRINT ステートメントの結果などの SQL Server メッセージ出力を表示できます。

例

例 1: 名前付きインスタンスに接続し、スクリプトを実行する

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery" -ServerInstance "MyComputer\MainInstance"

TimeOfQuery
-----------
9/21/2017 2:48:24 PM

このコマンドは、コンピューター上の SQL データベース エンジンの名前付きインスタンスに接続し、基本的な Transact-SQL スクリプトを実行します。

例 2: スクリプト ファイルでコマンドを呼び出し、出力をテキスト ファイルに保存する

Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" | Out-File -FilePath "C:\ScriptFolder\TestSqlCmd.rpt"
Output sent to TestSqlCmd.rpt.

このコマンドは、Transact-SQL ステートメントと SQLCMD コマンドを含むファイルを読み取り、ファイルを実行して、出力を別のファイルに書き込みます。

出力ファイルには独自の情報が含まれている可能性があるため、適切な NTFS アクセス許可を使用して出力ファイルをセキュリティで保護する必要があります。

例 3: スクリプトを呼び出し、文字列から変数値を渡す

$StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2" -Variable $StringArray

Var1     Var2
----     ----
String1  String2

このコマンドは、Variable パラメーターへの入力として文字列の配列を使用します。

配列は、複数の SQLCMD 変数を定義します。

SQLCMD 変数を識別する SELECT ステートメントの $ 記号は、バックティック (') 文字を使用してエスケープされます。

例 4: スクリプトを呼び出し、SQL データベース エンジンから変数を渡す

Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance"
PS SQLSERVER:\SQL\MyComputer\MainInstance> Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('MachineName') AS ComputerName" -ServerInstance (Get-Item .)

ComputerName
------------
MyComputer

このコマンドでは、Set-Location を使用して、SQL Database エンジンのインスタンスの SQL ServerWindows PowerShell プロバイダー パスに移動します。

次に、Get-Item を呼び出して、Invoke-Sqlcmd の ServerInstance パラメーターとして使用する SQL 管理オブジェクト サーバー オブジェクトを取得します。

例 5: クエリを実行して詳細出力を表示する

Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance"
Invoke-SqlCmd -Query "PRINT N'abc'" -Verbose
VERBOSE: abc

このコマンドは、Windows PowerShellVerbose パラメーターを使用して、SQL PRINT コマンドのメッセージ出力を返します。

例 6: 位置指定文字列を入力として使用してコマンドを呼び出す

Set-Location "SQLSERVER:\SQL\MyComputer\MainInstance\Databases\MyDatabase"
PS SQLSERVER:\SQL\MyComputer\MainInstance> Invoke-Sqlcmd "SELECT DB_NAME() AS DatabaseName"
WARNING: Using provider context. Server = MyComputer\MainInstance, Database = MyDatabase.

DatabaseName
------------
MyDatabase

このコマンドでは、位置指定文字列を使用して、Query パラメーターに入力を指定します。

また、Invoke-Sqlcmd が現在のパスを使用してデータベース コンテキストを MyDatabase に設定する方法も示します。

例 7: DataSet オブジェクトにデータをキャプチャする

$DS = Invoke-Sqlcmd -ServerInstance "MyComputer" -Query "SELECT  ID, Item FROM MyDB.dbo.MyTable" -As DataSet
$DS.Tables[0].Rows | %{ echo "{ $($_['ID']), $($_['Item']) }" }

{ 10, AAA }
{ 20, BBB }
{ 30, CCC }

このコマンドでは、As DataSet パラメーターを使用してデータを .Net System.Data.DataSet オブジェクトにキャプチャし、結果を変数 '$DS' に格納します。 オブジェクトは、さらに処理するために使用できます。

例 8: 特定の列セットを取得する

$Tables = Invoke-Sqlcmd -ServerInstance "MyComputer" -Query "SELECT  Item, id FROM MyDatabase.dbo.MyTable; SELECT GETDATE() AS T" -As DataTables
$Tables[0].Rows | %{ echo $_.ID }
$Tables[1].Rows | %{ echo $_.T.DayOfWeek }

10
20
30

Monday

最初のコマンドでは、As DataTables パラメーターを使用して、.Net System.Data.DataTable オブジェクトのコレクションにデータをキャプチャします。 このコマンドは、異なる列セットを持つ 2 つのテーブルを取得します。

各テーブルは、独自のスキーマに基づいて個別に処理できます。

例 9: 接続を完全に制御する

Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM MyTable" -ConnectionString "Data Source=MYSERVER;Initial Catalog=MyDatabase;Integrated Security=True;ApplicationIntent=ReadOnly"
Count
-----
127432

このコマンドは、-ConnectionString パラメーターを使用して、コマンド ラインで渡されたパラメーターに基づいて接続文字列を構築する Invoke-Sqlcmd ではなく、このコマンドレットが確立する接続を完全に制御します。

これは、使用する可能性のあるあまり一般的でないプロパティに役立ちます。

例 10: ストアド プロシージャを実行し、SQL エラーをキャプチャする

$script_sp_with_errors = @'
CREATE PROCEDURE [dbo].[TestProcedure3]
AS 
BEGIN 
  CREATE TABLE [dbo].[TestTable] (col INT NOT NULL);
  INSERT INTO [dbo].[TestTable] VALUES (NULL); -- will cause an error
END
GO
'@

# Create a test database
Invoke-SqlCmd -ServerInstance MyServer -Query 'CREATE DATABASE TestDB'
# ... adds a stored procedure that has errors in it...
Invoke-SqlCmd -ServerInstance MyServer -Database 'TestDB' -Query $script_sp_with_errors
# ... executes the SP and collected the errors
Invoke-SqlCmd -ServerInstance MyServer -Database 'TestDB' -Query 'EXEC TestProcedure3' -OutputSqlErrors $true

Here's the output:
Invoke-SqlCmd : Cannot insert the value NULL into column 'col', table 'TestDB.dbo.TestTable'; column does not allow nulls. INSERT fails.
The statement has been terminated.
 Msg 515, Level 16, State 2, Procedure TestProcedure3, Line 5.
At line:1 char:1
...

このコマンドは、-OutputSqlErrors パラメーターを使用してエラーをユーザーに報告します。 この場合のエラー メッセージは、SP 名やエラーが発生した行番号などの追加情報を提供します。

例 11: アクセス トークンを使用して Azure SQL Database (またはマネージド インスタンス) に接続する

Import-Module SQLServer
Import-Module Az.Accounts -MinimumVersion 2.2.0

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that Service Principal and has granted it access to the database (in this example at least
#       the SELECT permission).

### Obtain the Access Token: this will bring up the login dialog
Connect-AzAccount
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token`
              -query 'select * from Table1'

例 12: サービス プリンシパルを使用して Azure SQL Database (またはマネージド インスタンス) に接続する

Import-Module SQLServer

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that Service Principal and has granted it access to the database (in this example at least
#       the SELECT permission).

$clientid = "enter application id that corresponds to the Service Principal" # Do not confuse with its display name
$tenantid = "enter the tenant ID of the Service Principal"
$secret = "enter the secret associated with the Service Principal"

$request = Invoke-RestMethod -Method POST `
           -Uri "https://login.microsoftonline.com/$tenantid/oauth2/token"`
           -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$clientid; client_secret=$secret }`
           -ContentType "application/x-www-form-urlencoded"
$access_token = $request.access_token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token`
              -query 'select * from Table1'

例 13: システム割り当てマネージド ID (SAMI) を使用して Azure SQL Database (またはマネージド インスタンス) に接続する

Import-Module SQLServer

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that VM Identity you are running on and has granted it access to the database (in this 
#       example at least the SELECT permission).

Connect-AzAccount -Identity
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token `
              -query 'select * from Table1'

例 14: ユーザー割り当てマネージド ID (UAMI) を使用して Azure SQL Database (またはマネージド インスタンス) に接続する

Import-Module SQLServer

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that VM Identity you are running on and has granted it access to the database (in this 
#       example at least the SELECT permission).

Connect-AzAccount -Identity -AccountId '<your-user-assigned-managed-identity-client-id>'
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token `
              -query 'select * from Table1'

例 15: -ApplicationIntent を使用して Read-Only ルーティング用に構成された可用性グループに接続する

# In the following example:
# - MT_2009250511 is a listener for an AG configured for Read-Only Routing (port 5555)
# - AGDB_2_1 is the DB in the AG
# - VLM00226138 is the primary replica configured to only allow ReadWrite connections 
# - VLM00226137 is the secondary replica
# 
Invoke-Sqlcmd -ServerInstance "MT_2009250511,5555" -Database AGDB_2_1 `
     -HostName "PowershellBox1" -ApplicationName "ReadWrite" -ApplicationIntent ReadWrite `
     -Query "select HOST_NAME() AS HostName, APP_NAME() AS ApplicationIntent, @@SERVERNAME AS ServerName"

Invoke-Sqlcmd -ServerInstance "MT_2009250511,5555" -Database AGDB_2_1 `
     -HostName "PowershellBox2" -ApplicationName "ReadOnly" -ApplicationIntent ReadOnly `
     -Query "select HOST_NAME() AS HostName, APP_NAME() AS ApplicationIntent, @@SERVERNAME AS ServerName"  

# When you run the 2 cmdlets above, the output is going to be something like this:  
#  
# HostName       ApplicationIntent ServerName  
# --------       ----------------- ----------  
# PowershellBox1 ReadWrite         VLM00226138  
#  
# HostName       ApplicationIntent ServerName  
# --------       ----------------- ----------  
# PowershellBox2 ReadOnly          VLM00226137

これは、-ApplicationIntent パラメーターの値に応じて、接続が AG 内の別のサーバーにルーティングされることを示しています。 なお、-ApplicationName パラメーターと -HostName パラメーターの使用を観察して、2 つの結果を視覚的に区別します。これは、次に示す -ApplicationIntent 例を超えて、接続とその意図をトレースするために使用できる一般的な手法です。

例 16: -StatisticsVariable パラメーターを使用して接続統計をキャプチャする

Import-Module SQLServer
Invoke-Sqlcmd -ServerInstance localhost -StatisticsVariable stats `
              -Query 'CREATE TABLE #Table (ID int); INSERT INTO #Table VALUES(1), (2); INSERT INTO #Table VALUES(3); SELECT * FROM #Table'

Write-Host "Number of rows affected......: $($stats.IduRows)"
Write-Host "Number of insert statements..: $($stats.IduCount)"
Write-Host "Number of select statements..: $($stats.SelectCount)"
Write-Host "Total execution time.........: $($stats.ExecutionTime)ms"

# When you run the code fragment above, is going to be something like this:  
#
# Number of rows affected......: 3
# Number of insert statements..: 2
# Number of select statements..: 1
# Total execution time.........: 5ms

この例では、-StatisticsVariable パラメーターを使用して、一時テーブルを作成し、いくつかの値を挿入し、最後に挿入されたすべての行を取得するための選択を発行する T-SQL の実行時に、接続、実行されたステートメント、および実行時間に関する情報をキャプチャする方法を示します。

注: 同じクエリが複数のサーバーに対して実行される場合 (たとえば、コマンドレットを使用してサーバー名をパイプする)、StatisticsVariable は接続ごとに 1 つずつ、統計の配列をキャプチャします。 結果は、たとえば ($stats.IduRows | Measure-Object -Sum).Sumを使用して集計できます。

使用可能な統計の詳細については、SQL Server の プロバイダー統計を参照してください。

例 17: Always Encrypted を使用して暗号化された列から取得されたデータを復号化するクエリを実行します。 列マスター キーが Azure Key Vault のキー コンテナーに格納されているとします。

# Connect to Azure account.
Import-Module Az.Accounts -MinimumVersion 2.2.0
Connect-AzAccount

# Obtain an access token for key vaults. 
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token

# Pass the token to the cmdlet, so that it can use it to authenticate to Azure when decrypting data protected with Always Encrypted.
$connString = 'Data Source=MYSERVER;Initial Catalog=MyDatabase;Integrated Security=True;ApplicationIntent=ReadOnly;Column Encryption Setting=Enabled'
Invoke-Sqlcmd -Query 'SELECT COUNT(*) AS Count FROM MyTable' -ConnectionString $connString -KeyVaultAccessToken $keyVaultAccessToken

パラメーター

-AbortOnError

-AccessToken

-ApplicationIntent

-ApplicationName

-ConnectionString

-ConnectionTimeout

-Credential

-Database

-DedicatedAdministratorConnection

-DisableCommands

-DisableVariables

-Encrypt

-EncryptConnection

-ErrorLevel

-FailoverPartner

-HostName

-HostNameInCertificate

-IgnoreProviderContext

-IncludeSqlUserErrors

-InputFile

-KeyVaultAccessToken

-ManagedHsmAccessToken

-MaxBinaryLength

-MaxCharLength

-MultiSubnetFailover

-NewPassword

-OutputAs

-OutputSqlErrors

-Password

-ProgressAction

-Query

-QueryTimeout

-ServerInstance

-SeverityLevel

-StatisticsVariable

-SuppressProviderContextWarning

-TrustServerCertificate

-Username

-Variable

入力

System.Management.Automation.PSObject

出力

System.Object

関連リンク

• SQLServer_Cmdlets
• サービス プリンシパル
• マネージド ID
• 高可用性
• SQL Server のプロバイダー統計
• 厳密な接続の暗号化