sqlcmd でのスクリプト変数の使用

更新 : 2006 年 7 月 17 日

スクリプトで使用される変数は、スクリプト変数と呼ばれます。スクリプト変数を使用すると、1 つのスクリプトを複数のシナリオで使用できます。たとえば、1 つのスクリプトを複数のサーバーに対して実行する場合、各サーバー用にスクリプトを変更するのではなく、サーバー名にスクリプト変数を使用することができます。サーバー名をスクリプト変数で指定することで、同じスクリプトを複数のサーバーで実行することができるようになります。

スクリプト変数は、setvar コマンドを使用して明示的に定義するか、または sqlcmd-v オプションを使用して暗黙的に定義できます。

このトピックでは、SET を使用して Cmd.exe コマンド プロンプトで環境変数を定義する例も紹介しています。

setvar コマンドを使用したスクリプト変数の設定

setvar コマンドは、スクリプト変数を定義するのに使用します。setvar コマンドを使用して定義されている変数は、内部的に格納されます。スクリプト変数は、SET を使用してコマンド プロンプトで定義されている環境変数と混同しないようにする必要があります。環境変数でもなく setvar コマンドを使用して定義したものでもない変数をスクリプト内で参照していると、エラー メッセージが表示され、スクリプトの実行は停止されます。詳細については、「sqlcmd ユーティリティ」の -b オプションの説明を参照してください。

変数の優先順位 (低から高)

複数の種類の変数に同じ名前が付いている場合、優先順位の最も高い変数が使用されます。

1. システム レベル環境変数
2. ユーザー レベル環境変数
3. sqlcmd の起動前にコマンドプロンプトで設定されたコマンド シェル (SET X=Y)
4. sqlcmd-v X=Y
5. :Setvar X Y

メモ :
環境変数を表示するには、[コントロール パネル] の[システム] アイコンを開き、[詳細設定] タブをクリックします。

スクリプト変数の暗黙的な設定

関連する sqlcmd 変数を含むオプションを指定して sqlcmd を起動すると、sqlcmd 変数には、そのオプションを使用して指定されている値が暗黙的に設定されます。次の例では、sqlcmd が -l オプションを指定して起動されています。このコマンドを実行すると、SQLLOGINTIMEOUT 変数が暗黙的に設定されます。

c:\> sqlcmd -l 60

-v オプションを使用して、スクリプト内に存在するスクリプト変数を設定することもできます。次のスクリプト (ファイル名は testscript.sql) では、ColumnName がスクリプト変数です。

USE AdventureWorks;

SELECT c.$(ColumnName)

FROM Person.Contact x

WHERE c.ContactID < 5;

その後、-v オプションを使用して、取得する列の名前を指定できます。

sqlcmd -v ColumnName ="FirstName" -i c:\testscript.sql

同じスクリプトを使用して別の列を取得するには、ColumnName スクリプト変数の値を変更します。

sqlcmd -v ColumnName ="LastName" -i c:\testscript.sql

スクリプト変数の名前と値に関するガイドライン

スクリプト変数に名前を指定する場合は、次のガイドラインを考慮してください。

• 変数名には空白文字または引用符を使用できません。
• 変数名には、$(var) のような変数式と同じ形式を使用することはできません。
• スクリプト変数では、大文字と小文字が区別されません。

  メモ :
  sqlcmd 環境変数に値が割り当てられていない場合、この環境変数は削除されます。値を指定せずに :setvar VarName を使用すると、変数は削除されます。

スクリプト変数に値を指定する場合は、次のガイドラインを考慮してください。

• setvar または -v オプションを使用して定義する変数値は、空白を含む文字列値の場合に引用符で囲む必要があります。
• 変数値に引用符が使用されている場合は、その引用符をエスケープする必要があります。たとえば、setvar MyVar "spac""e" のように指定します。

Cmd.exe の SET による変数の値と名前に関するガイドライン

SET を使用して定義される変数は、Cmd.exe 環境で使用されるため、sqlcmd で参照できます。次のガイドラインを考慮してください。

• 変数名には空白文字または引用符を使用できません。
• 変数値には空白文字または引用符を使用できます。

sqlcmd スクリプト変数

sqlcmd で定義される変数はスクリプト変数と呼ばれます。次の表は、sqlcmd スクリプト変数の一覧です。

変数	関連するオプション	R/W	既定値
SQLCMDUSER*	-U	R	""
SQLCMDPASSWORD*	-P	--	""
SQLCMDSERVER*	-S	R	"DefaultLocalInstance"
SQLCMDWORKSTATION	-H	R	"ComputerName"
SQLCMDDBNAME	-d	R	""
SQLCMDLOGINTIMEOUT	-l	R/W	"8" (秒)
SQLCMDSTATTIMEOUT	-t	R/W	"0" = 無制限に待機
SQLCMDHEADERS	-h	R/W	"0"
SQLCMDCOLSEP	-s	R/W	" "
SQLCMDCOLWIDTH	-w	R/W	"0"
SQLCMDPACKETSIZE	-a	R	"4096"
SQLCMDERRORLEVEL	-m	R/W	"0"
SQLCMDMAXVARTYPEWIDTH	-y	R/W	"256"
SQLCMDMAXFIXEDTYPEWIDTH	-Y	R/W	"0" = 無制限
SQLCMDEDITOR		R/W	"edit.com"
SQLCMDINI		R	""

* SQLCMDUSER、SQLCMDPASSWORD および SQLCMDSERVER は、:Connect が使用されている場合に設定されます。

R は、その値がプログラムの初期化時に一度だけ設定できることを示します。

R/W は、setvar コマンドを使用して値を再設定できること、および後続のコマンドで新しい値が使用されることを示します。

例

A. スクリプトでの setvar コマンドの使用

多くの sqlcmd オプションは、スクリプトで setvar コマンドを使用して制御できます。次の例では、スクリプト test.sql が作成されます。このスクリプトでは、変数 SQLCMDLOGINTIMEOUT が 60 秒に設定され、別のスクリプト変数 server が testserver に設定されています。test.sql のコードを次に示します。

:setvar SQLCMDLOGINTIMEOUT 60

:setvar server "testserver"

:connect $(server) -l $(SQLCMDLOGINTIMEOUT)

USE AdventureWorks;

SELECT FirstName, LastName

FROM Person.Contact;

The script is then called by using sqlcmd:

sqlcmd -i c:\test.sql

B. setvar コマンドの対話的な使用

次の例では、setvar コマンドを使用してスクリプト変数を対話的に設定する方法を示します。

sqlcmd

:setvar MYDATABASE AdventureWorks

USE $(MYDATABASE);

GO

以下に結果セットを示します。

Changed database context to 'AdventureWorks'

1>

C. sqlcmd 内でのコマンド プロンプト環境変数の使用

次の例では、4 つの環境変数を設定した後、sqlcmd から呼び出します。

C:\>SET tablename=Person.Contact

C:\>SET col1=FirstName

C:\>SET col2=LastName

C:\>SET title=Ms.

C:\>sqlcmd -d AdventureWorks

1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name

2> FROM $(tablename)

3> WHERE Title ='$(title)'

4> GO

D. sqlcmd 内でのユーザーレベル環境変数の使用

次の例では、ユーザーレベル環境変数 %Temp% をコマンド プロンプトで設定し、sqlcmd 入力ファイルに渡します。ユーザーレベル環境変数を取得するには、[コントロール パネル] の [システム] をダブルクリックします。[詳細設定] タブをクリックし、[環境変数] をクリックします。

入力ファイル c:\testscript.txt のコードを次に示します。

:OUT $(MyTempDirectory)

USE AdventureWorks;

SELECT FirstName

FROM AdventureWorks.Person.Contact

WHERE ContactID < 5;

コマンド プロンプトで、次のコードを入力します。

C:\ >SET MyTempDirectory=%Temp%\output.txt

C:\ >sqlcmd -i C:\testscript.txt

出力ファイル C:\Documents and Settings\<user>\Local Settings\Temp\output.txt には、次の結果が送信されます。

Changed database context to 'AdventureWorks'.

FirstName

--------------------------------------------------

Gustavo

Catherine

Kim

Humberto

(4 rows affected)

E. スタートアップ スクリプトの使用

sqlcmd スタートアップ スクリプトは、sqlcmd の起動時に実行されます。次の例では、環境変数 SQLCMDINI が設定されます。init.sql の内容を次に示します。

SET NOCOUNT ON

GO

DECLARE @nt_user_name nvarchar(128)

SET @nt_user_name = (SELECT rtrim(convert(nvarchar(128), nt_user_name))

FROM sys.dm_exec_sessions WHERE session_id = @@SPID);

SELECT @nt_user_name + ' is connected to ' +

rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('servername'))) + ' (' +

rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('productversion'))) + ')';

:setvar SQLCMDMAXFIXEDTYPEWIDTH 100

SET NOCOUNT OFF

GO

:setvar SQLCMDMAXFIXEDTYPEWIDTH

次のコードにより、sqlcmd の起動時に init.sql ファイルが呼び出されます。

C:\> SET sqlcmdini=c:\init.sql

>1 Sqlcmd

出力結果は次のとおりです。

>1 < user > is connected to < server > (9.00.2047.00)

注   -X オプションを使用すると、スタートアップ スクリプト機能が無効になります。

F. 変数の拡張の使用

次の例では、sqlcmd 変数の形式でデータを使用する方法を示します。

USE AdventureWorks;

CREATE TABLE AdventureWorks.dbo.VariableTest

(

Col1 nvarchar(50)

);

GO

dbo.VariableTest の Col1 に、値 $(tablename) を含む 1 行を挿入します。

INSERT INTO AdventureWorks.dbo.VariableTest(Col1)

VALUES('$(tablename)');

GO

sqlcmd プロンプトで、$(tablename) に変数を設定していない場合、次のステートメントでは行が返されます。

C:\> sqlcmd

>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';

>2 GO

>3 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';

>4 GO

以下に結果セットを示します。

>1 Col1

>2 ------------------

>3 $(tablename)

>4

>5 (1 rows affected)

変数 MyVar が $(tablename) に設定されている場合は次のようになります。

>6 :setvar MyVar $(tablename)

次のステートメントでは、行だけでなく、"'tablename' スクリプト変数が定義されていません。" というメッセージも返されます。

>6 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';

>7 GO

>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';

>2 GO

次のステートメントでは行が返されます。

>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(MyVar)';

>2 GO

>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(MyVar)';

>2 GO

変更履歴

リリース	履歴
2006 年 7 月 17 日	新しい内容 : 例を追加しました。

参照

概念

sqlcmd ユーティリティの使用

その他の技術情報

sqlcmd ユーティリティ
コマンド プロンプト ユーティリティ

ヘルプおよび情報

SQL Server 2005 の参考資料の入手