【　xp_cmdshell でストアードプロシージャを知る　】

1. プロシージャ情報の取得
2. OS コマンドを実行するプロシージャ
3. ストアードプロシージャを作成する準備
4. ストアードプロシージャのコンパイルと実行
5. 戻り値を戻すストアードプロシージャ

一覧 
  • select * from sysobjects where xtype = 'P'
    •  



    


  • 上記SQL で登録されている ストアードプロシージャの一覧を得る事ができます

    

  • SQLの窓1.5 の コマンドコンボボックスに登録するには、input.htm の以下の部分を変更して下さい
    • 

    Fld( "SPNAME" ).options.length = 8
Fld( "SPNAME" ).options(0).text = ""
Fld( "SPNAME" ).options(1).text = "データベース一覧"
Fld( "SPNAME" ).options(1).value = "sp_helpdb"
Fld( "SPNAME" ).options(2).text = "テーブル一覧"
Fld( "SPNAME" ).options(2).value = "sp_tables"
Fld( "SPNAME" ).options(3).text = "テーブル定義"
Fld( "SPNAME" ).options(3).value = "sp_columns"
Fld( "SPNAME" ).options(4).text = "キー定義"
Fld( "SPNAME" ).options(4).value = "sp_pkeys"
Fld( "SPNAME" ).options(5).text = "バージョン情報"
Fld( "SPNAME" ).options(5).value = "sp_server_info @attribute_id = 2"
Fld( "SPNAME" ).options(6).text = "プロセス情報"
Fld( "SPNAME" ).options(6).value = "sp_who"
Fld( "SPNAME" ).options(7).text = "プロシージャ情報"
Fld( "SPNAME" ).options(7).value = "select * from sysobjects where xtype = 'P'"
Fld( "SPNAME" ).disabled = false



    


  • Fld( "SPNAME" ).options.length を変更して Fld( "SPNAME" ).options(7) を追加します


    • 

    


    
テキストの取得

    

  • sp_helptext @objname = 'プロシージャ名'
    •  



    


  • 上記SQL で登録されている ストアードプロシージャのソーステキストを得る事ができます


    • 

    

    

    


    
一覧

    

  • xp_cmdshell は、OS のコマンドを実行して出力をテキストの行として返します
    •  



    

    if exists(select * from sysobjects where name = 'CMD' and type = 'P')
	drop procedure cmd
go
-- *********************************************************
-- サンプル ( 1 )
-- *********************************************************
CREATE	PROCEDURE	CMD
	@COMMAND varchar(255)
AS
 
exec	master.dbo.xp_cmdshell @COMMAND
go

    


  • go は、osql 用のコマンドで、最後に入力した go の後に入力したすべてのステートメントを実行します

    

  • exists は、論理演算子で、サブクエリが行を含む場合、TRUE を返します

    

  • CREATE PROCEDURE プロシージャ名 〜 でストアードプロシージャを作成します
    ( PROCEDURE は PROC と省略できます )

    

  • xp_cmdshell は、汎用拡張プロシージャです

    

  • osql は SQL を実行する為のコマンドプロンプトユーティリティです


    • 

    

    


  • 以下のようなショートカットを作成を作成します

    

  • リンク先
    


  • %SystemRoot%\system32\cmd.exe /c osql -S localhost -d lightbox -U sa -P
    ( -d lightbox はデータベースの指定です )

    • 

  • 作業フォルダ
    


  • %HOMEDRIVE%%HOMEPATH%

    • 

  • オプションタブの編集オプショングループの簡易編集モードにチェックします


    • 

    

    


  • 先ほどのショートカットを実行して、サンプルコードをコピーしてコマントプロンプトのタイトルを
    クリックしてフォーカスを移し、ウインドウ内で右クリックして貼り付けます

    

  • 即実行されて、エラーメッセージが出なければ作成されているはずです

    

  • 実行は SQLの窓で可能なので以下のように入力して検索ボタンをクリックします

    • 

    


    

    exec cmd 'dir *.*'
 



    


  • osql でももちろん実行できますが、行が多くてなかなか止まらない場合は CTRL + C で終わります

    

  • exec は省略可能で、exec @COMMAND = 'dir *.*' でも同じ結果が得られます


    • 

    

    

    if exists(select * from sysobjects where name = 'CMD' and type = 'P')
	drop procedure cmd
go
-- *********************************************************
-- サンプル ( 2 )
-- *********************************************************
CREATE	PROCEDURE	CMD
	@COMMAND varchar(255),
	@RET varchar(255) output
AS
	DECLARE @STS int
 
exec	@STS = master.dbo.xp_cmdshell @COMMAND
set	@RET = '処理終了'
return 	@STS
go

    


  • 実行は以下のように記述する必要があります ( SQLの窓1.5で実行。つまり、ADO での実行 )

    • 

    DECLARE @RET1 varchar(255),@RET2 int
exec @RET2 = cmd 'dir *.bat',@RET1 output

    


  • たしかに実行されるのですが、このままでは戻り値を知る事ができませんので SQLの窓1.5 を改造します

    

  • まず、「ALL」というボタンを一つ追加します

    • 

    <INPUT type=button name=DoSql class=Btn value="検索処理" onClick="Call fncSql('')">
<INPUT type=button name=DoAllSql class=Btn value="ALL" onClick="Call fncAllSql()" style='width:30'><br>

    


  • fncAllSql という関数を追加します

    • 

    ' ******************************************************************************
' 検索処理
' ******************************************************************************
function fncAllSql()
 
	Dim ErrCnt
 
	' --------------------------------------------------------------------
	' 接続文字列の取得
	strConnection = GetConnectionString()
	if strConnection = "" then
		exit function
	end if
 
	' --------------------------------------------------------------------
	' 出力エリアの初期化
	Out.open()
 
	' --------------------------------------------------------------------
	' 接続オブジェクト作成
	if not IsObject( cn ) then
		Set cn = CreateObject("ADODB.Connection")
	end if
 
	' --------------------------------------------------------------------
	' エラートラップ開始
	on error resume next
 
	' --------------------------------------------------------------------
	' 接続の確立
	cn.Open strConnection
	if Err.Number <> 0 then
		on error goto 0
		exit function
	end if
 
	' --------------------------------------------------------------------
	' 読み出し用のレコードセットオブジェクトを作成
	if not IsObject( rs ) then
		Set rs = CreateObject( "ADODB.Recordset" )
	end if
	if rs is nothing then
		Set rs = CreateObject( "ADODB.Recordset" )
	end if
 
	' --------------------------------------------------------------------
	' 入力した文字列を変数にセット
	SqlQuery = Fld( "SQL" ).value
 
	' --------------------------------------------------------------------
	' SQLの実行
	rs.Open SqlQuery, cn 
	if Err.Number <> 0 then
		Out.write( "◆ " & Err.Description & "<br><br>" )
		rs.Close
		cn.Close
		on error goto 0
		exit function
	end if
 
	if rs is nothing then
		Out.write( "◆ レコードセットオブジェクトが Nothing に設定されました<br><br>" )
		Set rs = CreateObject( "ADODB.Recordset" )
		cn.Close
		on error goto 0
		exit function
	end if
 
	if rs.State = 0 then
		ErrCnt = 0
		Do While rs.State = 0
			set rs = rs.NextRecordset
			if rs is nothing then
				Set rs = CreateObject( "ADODB.Recordset" )
				cn.Close
				on error goto 0
				Out.write( "◆ レコードセットオブジェクトが Nothing に設定されました<br><br>" )
				exit function
			end if
			ErrCnt = ErrCnt + 1
			if ErrCnt > 50 then
				rs.Close
				cn.Close
				on error goto 0
				Out.write( "◆ データを取り出す事ができませんでした<br><br>" )
				exit function
			end if
		Loop
	end if
 
	' --------------------------------------------------------------------
	' 表示処理
	with ( Out )
 
		Call OutTitlePrint( )
 
		' --------------------------------------------------------------------
		' テーブル開始
		.write( "<TABLE border=1 cellpadding=5 bordercolordark=black bordercolorlight=silver id=SelectData>" )
 
		' --------------------------------------------------------------------
		' タイトルのセット
		.write( "<th bgcolor=gray nowrap>No.</th>" )
 
		Dim MaxColumn
 
		MaxColumn = rs.fields.count
		for i = 1 to MaxColumn
			.write( "<th bgcolor=skyblue nowrap>" )
			.write( Replace(rs.Fields( i-1 ).Name,"'", "&#39;" ) )
			.write( "</th>" )
		Next
 
		' --------------------------------------------------------------------
		' 行を読み込む
		Cnt = 0
		Do while not rs.EOF
 
			' --------------------------------------------------------------------
			' 行開始
			.write( "<TR>" & vbCrLf )
			.write( "<TD nowrap bgcolor=gray>" & Cnt + 1 & "</TD>" )
 
			' --------------------------------------------------------------------
			' データのセット
			for i = 1 to MaxColumn
				select case rs.Fields( i-1 ).Type
					case 128,205
						.write( "<TD nowrap>&nbsp;</TD>" )
					case else
						.write( "<TD nowrap>" & rs.Fields( i-1 ).Value & "&nbsp;</TD>" )
				end select
			Next
 
			' --------------------------------------------------------------------
			' 行終了
			.write( "</TR>" & vbCrLf )
 
			Cnt = Cnt + 1
			if  Cnt >= eval( Fld("MaxRec").value ) then
				Exit Do
			end if
 
			rs.MoveNext
		Loop
 
		' --------------------------------------------------------------------
		' テーブル終了
		.write( "</TABLE>" & vbCrLf )
 
		' --------------------------------------------------------------------
		' SQLの実行
		set rs = rs.NextRecordset
		if Err.Number <> 0 then
			Out.write( "◆ " & Err.Description & "<br><br>" )
			rs.Close
			cn.Close
			on error goto 0
			.write( "</BODY><HTML>" )
			if rs is nothing then
				Set rs = CreateObject( "ADODB.Recordset" )
			end if
			exit function
		end if
		if rs is nothing then
			Set rs = CreateObject( "ADODB.Recordset" )
			cn.Close
			on error goto 0
			.write( "</BODY><HTML>" )
			exit function
		end if
		if rs.State = 0 then
			ErrCnt = 0
			Do While rs.State = 0
				set rs = rs.NextRecordset
				if rs is nothing then
					Set rs = CreateObject( "ADODB.Recordset" )
					cn.Close
					on error goto 0
					.write( "</BODY><HTML>" )
					exit function
				end if
				ErrCnt = ErrCnt + 1
				if ErrCnt > 50 then
					rs.Close
					cn.Close
					on error goto 0
					Out.write( "◆ データを取り出す事ができませんでした<br><br>" )
					exit function
				end if
			Loop
		end if
 
 
		Do while not rs.EOF and not rs.BOF
 
			' --------------------------------------------------------------------
			' テーブル開始
			.write( "<TABLE border=1 cellpadding=5 bordercolordark=black bordercolorlight=silver id=SelectData>" )
 
			' --------------------------------------------------------------------
			' タイトルのセット
			.write( "<th bgcolor=gray nowrap>No.</th>" )
 
			MaxColumn = rs.fields.count
			for i = 1 to MaxColumn
				.write( "<th bgcolor=skyblue nowrap>" )
				.write( Replace(rs.Fields( i-1 ).Name,"'", "&#39;" ) )
				.write( "</th>" )
			Next
 
			' --------------------------------------------------------------------
			' 行を読み込む
			Cnt = 0
			Do while not rs.EOF
 
				' --------------------------------------------------------------------
				' 行開始
				.write( "<TR>" & vbCrLf )
				.write( "<TD nowrap bgcolor=gray>" & Cnt + 1 & "</TD>" )
 
				' --------------------------------------------------------------------
				' データのセット
				for i = 1 to MaxColumn
					select case rs.Fields( i-1 ).Type
						case 128,205
							.write( "<TD nowrap>&nbsp;</TD>" )
						case else
							.write( "<TD nowrap>" & rs.Fields( i-1 ).Value & "&nbsp;</TD>" )
					end select
				Next
 
				' --------------------------------------------------------------------
				' 行終了
				.write( "</TR>" & vbCrLf )
 
				Cnt = Cnt + 1
				if  Cnt >= eval( Fld("MaxRec").value ) then
					Exit Do
				end if
 
				rs.MoveNext
			Loop
 
			' --------------------------------------------------------------------
			' テーブル終了
			.write( "</TABLE>" & vbCrLf )
 
			set rs = rs.NextRecordset
			if Err.Number <> 0 then
				Out.write( "◆ " & Err.Description & "<br><br>" )
				rs.Close
				cn.Close
				on error goto 0
				.write( "</BODY><HTML>" )
				if rs is nothing then
					Set rs = CreateObject( "ADODB.Recordset" )
				end if
				exit function
			end if
			if rs is nothing then
				Set rs = CreateObject( "ADODB.Recordset" )
				cn.Close
				on error goto 0
				.write( "</BODY><HTML>" )
				exit function
			end if
			if rs.State = 0 then
				Do While rs.State = 0
					set rs = rs.NextRecordset
					if rs is nothing then
						Set rs = CreateObject( "ADODB.Recordset" )
						cn.Close
						on error goto 0
						.write( "</BODY><HTML>" )
						exit function
					end if
				Loop
			end if
 
		Loop
 
		' --------------------------------------------------------------------
		' 接続を閉じる
		rs.Close
		cn.Close
 
		' --------------------------------------------------------------------
		' エラートラップ解除
		on error goto 0
 
		' --------------------------------------------------------------------
		' 全体終了
		.write( "</BODY><HTML>" )
 
	end with
 
end function

    


  • この処理は、複数のクエリーに対して複数の結果を表示する為のものです。本来SQLServerでは、
    複数のクエリーを実行できるのですが、通常では最初の結果しか取得できませんそこで、NextRecordset
    というメソッドを使用して複数の結果を全て取得します

    

  • 実行は以下のように記述します

    • 

    DECLARE @RET1 varchar(255),@RET2 int
exec @RET2 = cmd 'dir *.bat',@RET1 output
select @RET1 as 引数の戻り値
select @RET2 as プロシージャの戻り値

    


  • SQLの窓1.5(改)の実行結果です

    • 

    


    

    No.output
    1ドライブ C のボリューム ラベルがありません。 
    2ボリューム シリアル番号は B0EC-87B2 です 
    3 
    4C:\WINDOWS\system32 のディレクトリ 
    5 
    62001/08/28 21:00 70 jp.bat 
    72001/08/28 21:00 78 us.bat 
    82 個のファイル 148 バイト 
    90 個のディレクトリ 4,684,771,328 バイトの空き領域 
    

    No.引数の戻り値
    1処理終了 
    

    No.プロシージャの戻り値
    10