PowerShellからSQL Serverストアドプロシージャを呼び出す方法

SQL Server のストアドプロシージャを PowerShell から呼び出す方法

SQL Server のストアドプロシージャは、データベース操作をまとめたプログラムです。PowerShell は、Windows システム管理向けのスクリプティング言語です。PowerShell からストアドプロシージャを呼び出すことで、データベース操作を自動化できます。

方法

1. 必要なモジュールのインストール

   Install-Module SqlServer
   

2. 接続情報の準備

   以下の変数に、SQL Server への接続情報とストアドプロシージャの名前を格納します。

   $serverName = 'localhost'
   $databaseName = 'TestDB'
   $storedProcedureName = 'MyStoredProcedure'
   

3. 接続の作成

   以下のコマンドを実行して、SQL Server への接続を作成します。

   $connection = New-Object Microsoft.SqlServer.Management.Smo.ServerConnection($serverName)
   

4. データベースの選択

   以下のコマンドを実行して、接続するデータベースを選択します。

   $connection.Database = $databaseName
   

5. ストアドプロシージャの作成

   以下のコマンドを実行して、ストアドプロシージャを実行します。

   $cmd = New-Object Microsoft.SqlServer.Management.Smo.SqlCommand($connection, $storedProcedureName)
   $cmd.ExecuteNonQuery()
   

6. 接続のクローズ

   $connection.Dispose()
   

パラメータの受け渡し

ストアドプロシージャにパラメータを渡す場合は、$cmd.Parameters プロパティにパラメータオブジェクトを追加します。

$param1 = New-Object Microsoft.SqlServer.Management.Smo.SqlParameter('@param1', 'nvarchar(50)')
$param1.Value = 'Hello, world!'
$cmd.Parameters.Add($param1)

$cmd.ExecuteNonQuery()

結果の取得

ストアドプロシージャが結果セットを返す場合は、$cmd.ExecuteReader() メソッドを使用して結果を取得できます。

$reader = $cmd.ExecuteReader()

while ($reader.Read()) {
    $id = $reader['Id']
    $name = $reader['Name']

    Write-Host "Id: $id, Name: $name"
}

$reader.Close()

詳細は、以下のドキュメントを参照してください。

# モジュールのインストール
Install-Module SqlServer

# 接続情報の準備
$serverName = 'localhost'
$databaseName = 'Northwind'
$storedProcedureName = 'GetCustomers'

# 接続の作成
$connection = New-Object Microsoft.SqlServer.Management.Smo.ServerConnection($serverName)

# データベースの選択
$connection.Database = $databaseName

# ストアドプロシージャの作成
$cmd = New-Object Microsoft.SqlServer.Management.Smo.SqlCommand($connection, $storedProcedureName)

# 結果の取得
$reader = $cmd.ExecuteReader()

# 結果の出力
while ($reader.Read()) {
    $customerId = $reader['CustomerID']
    $companyName = $reader['CompanyName']

    Write-Host "CustomerID: $customerId, CompanyName: $companyName"
}

# 接続のクローズ
$connection.Dispose()

# 結果のクリーンアップ
$reader.Close()

このコードを実行すると、Customers テーブルのすべてのデータが表示されます。

SQL Server ストアドプロシージャを PowerShell から呼び出すその他の方法

Invoke-Sqlcmd コマンドレットは、SQL Server に対するクエリやコマンドを実行するためのコマンドレットです。このコマンドレットを使用して、ストアドプロシージャを呼び出すことができます。

Invoke-Sqlcmd -ServerInstance localhost -Database Northwind -Query "EXEC GetCustomers"

SMO オブジェクト

SQL Server Management Objects (SMO) は、PowerShell から SQL Server を管理するためのオブジェクトモデルです。SMO オブジェクトを使用して、ストアドプロシージャを呼び出すことができます。

# モジュールのインストール
Install-Module SqlServer

# 接続情報の準備
$serverName = 'localhost'
$databaseName = 'Northwind'
$storedProcedureName = 'GetCustomers'

# 接続の作成
$connection = New-Object Microsoft.SqlServer.Management.Smo.ServerConnection($serverName)

# データベースの選択
$connection.Database = $databaseName

# ストアドプロシージャの作成
$cmd = New-Object Microsoft.SqlServer.Management.Smo.SqlCommand($connection, $storedProcedureName)

# 結果の取得
$reader = $cmd.ExecuteReader()

# 結果の出力
while ($reader.Read()) {
    $customerId = $reader['CustomerID']
    $companyName = $reader['CompanyName']

    Write-Host "CustomerID: $customerId, CompanyName: $companyName"
}

# 接続のクローズ
$connection.Dispose()

# 結果のクリーンアップ
$reader.Close()

SqlConnection クラスは、.NET Framework から SQL Server に接続するためのクラスです。SqlConnection クラスを使用して、ストアドプロシージャを呼び出すことができます。

# モジュールのインストール
Install-Module SqlServer

# 接続情報の準備
$serverName = 'localhost'
$databaseName = 'Northwind'
$storedProcedureName = 'GetCustomers'

# 接続文字列の作成
$connectionString = "Data Source=$serverName;Initial Catalog=$databaseName;Integrated Security=True;"

# 接続の作成
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

# コマンドの作成
$cmd = New-Object System.Data.SqlClient.SqlCommand($storedProcedureName, $connection)
$cmd.CommandType = System.Data.CommandType.StoredProcedure

# 結果の取得
$reader = $cmd.ExecuteReader()

# 結果の出力
while ($reader.Read()) {
    $customerId = $reader['CustomerID']
    $companyName = $reader['CompanyName']

    Write-Host "CustomerID: $customerId, CompanyName: $companyName"
}

# 接続のクローズ
$connection.Close()

# 結果のクリーンアップ
$reader.Close()

ODBC 接続を使用して、SQL Server に接続し、ストアドプロシージャを呼び出すことができます。

# モジュールのインストール
Install-Module SqlServer

# 接続情報の準備
$serverName = 'localhost'
$databaseName = 'Northwind'
$storedProcedureName = 'GetCustomers'

# データソース名の作成
$dataSourceName = "Driver={SQL Server};Server=$serverName;Database=$databaseName;Trusted_Connection=Yes;"

# 接続の作成
$connection = New-Object System.Data.Odbc.OdbcConnection($dataSourceName)

# コマンドの作成
$cmd = New-Object System.Data.Odbc.OdbcCommand($storedProcedureName, $connection)
$cmd.CommandType = System.Data.CommandType.StoredProcedure

# 結果の取得
$reader = $cmd.ExecuteReader()

# 結果の出力
while ($reader.Read()) {
    $customerId = $reader['CustomerID']
    $companyName = $reader['CompanyName']

    Write-Host "CustomerID: $customerId, CompanyName: $companyName"
}

# 接続のクローズ
$connection.Close()

# 結果のクリーンアップ
$reader.Close()

各方法の比較