【 xp_cmdshell でストアードプロシージャを知る 】
一覧 |
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の窓で可能なので以下のように入力して検索ボタンをクリックします
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,"'", "'" ) )
.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> </TD>" )
case else
.write( "<TD nowrap>" & rs.Fields( i-1 ).Value & " </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,"'", "'" ) )
.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> </TD>" )
case else
.write( "<TD nowrap>" & rs.Fields( i-1 ).Value & " </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 | | 4 | C:\WINDOWS\system32 のディレクトリ | 5 | | 6 | 2001/08/28 21:00 70 jp.bat | 7 | 2001/08/28 21:00 78 us.bat | 8 | 2 個のファイル 148 バイト | 9 | 0 個のディレクトリ 4,684,771,328 バイトの空き領域 |
|
|