SE の雑記

PowerShell から SQL Server に SQL を実行する場合、データベース エンジン PowerShell を使用することがあるかと思います。

ほかにも System.Data.SqlClient を使用した方法 (Windows PowerShell: Windows PowerShell からデータベースにアクセスする) や、SQLSMO を使用した方法 (Using PowerShell with SQL Server Management Objects (SMO)) もあります。

今回は、データベースエンジン PowerShell の Invoke-Sqlcmd を sqlcmd.exe に置き換えた際のメモを。
以下の情報を参考にさせていただいています。

SQL2005のSQLCMDオプションについて質問
Passing varibles with special characters and or embedded spaces in powershell 2.0 with SQL Server
How to stop the “Changed database context to …” message

Invoke-Sqlcmd を使用する場合、

Import-Module SQLPS -DisableNameChecking

を実行してモジュールをインポートして利用するかと思います。

バッチ等から実行する場合、この Import-Module の実行時間と CPU の使用率がそれなりに高く、数秒で結果を返すのが難しそうでしたので、SQLCMD.exe で置き換えを行いました。

# 外部の SQL ファイルを変数置換を使用して実行していたので、SQLCMD.exe への置き換えが影響が少ないと思いまして。

今回は以下のような sql ファイルを実行してみたいと思います。

USE $(DBNAME)
GO
SELECT TOP 10 name,type_desc FROM sys.tables

実行すると以下のような形式で結果が帰ってきます。

sqlcmd.exe からの実行結果は文字列として取得されます。

また、ヘッダーや処理対象行が出力されていますので、この状態では取得した結果が使いづらいかと。

ということでまずは SQL  を以下のように修正します。

SET NOCOUNT ON
GO
USE $(DBNAME)
GO
SELECT TOP 10 name,type_desc FROM sys.tables

次に PowerShell を以下のように修正します。

$header = "name","type_desc"
$ret = sqlcmd.exe -S . -E -i C:tempSQLCMDTest.sql -v DBNAME='ADK' -h -1 -m 1 -s "," | ConvertFrom-Csv -Delimiter "," -Header $header
$ret

そうすると実行結果を PSCustomObject で取得することができますので、後続の処理がしやすくなるかと思います。

ただし、変数の内容にスペースが入った場合は [無効な引数です。] のエラーが発生し実行することができませんでし た。綺麗な回避方法がわからず、cmd.exe 経由で実行することでエラーを回避することにしました。

$header = "name","type_desc"
$ret = cmd.exe /C "sqlcmd.exe -S . -E -i C:tempSQLCMDTest.sql -v DBNAME=`"TEST DB`" -h -1 -m 1 -s `",`"" | ConvertFrom-Csv -Delimiter "," -Header $header
$ret

外部の SQL ファイルを実行したほうがクエリ修正が楽なので、Invoke-sqlcmd でインプットファイルを使用していたのですが、効率よくクエリを実行する方法を考えないとな～と思いました。

追記

@mutaguchi さんにパラメーター解析モードを cmd 互換にする方法 (?% の利用) を教えていただきました。

以下の形式にすることで cmd.exe を介さずに実行できました。

$header = "name","type_desc"
$ret = sqlcmd.exe --% -S . -E -i C:tempSQLCMDTest.sql -v DBNAME="TEST DB" -h -1 -m 1 -s "," | ConvertFrom-Csv -Delimiter "," -Header $header
$ret