- PR -

DTSからストアドプロシージャの結果を取得する方法について

1
投稿者投稿内容
crab
会議室デビュー日: 2006/12/25
投稿数: 11
投稿日時: 2007-01-09 17:41
[OSのVER]:Windows Server 2003
[SQLServerのVER]:SQLServer2000

DTSパッケージからSQL実行タスクを使ってストアドプロシージャを実行しています。
そのストアドプロシージャの処理結果（成功／失敗）を取得して、DTSのグローバル変数に格納し、後続のActive Xスクリプトタスクでそのグローバル変数を読んでエラー処理をしようと考えてます。

ところが、ストアドプロシージャ内でINSERT分を実行している場合に戻り値をうまくハンドリングができません。


[SQL実行タスクのSQL Statementに記述している内容]
declare @SPRET int, @SpResult int
exec @SPRET = SpTest @SpResult OUTPUT
select @SPRET as SPRET, @SpResult as SpResult

で、結果をそれぞれDTSのグローバル変数 SPRET、SPRESULTへマップするよう設定しています。


[ストアドプロシージャの中身]
(実際はいろんな処理をしているのですが、問題をわかりやすくするために簡単なものにしています)
drop proc sptest
go
create proc sptest
(
@SpResult int OUTPUT
)
as
INSERT INTO tableA .....
select @SpResult = 2
return 3


[後続のActiveX スクリプトの中身]
Function Main()

msgbox "SPRET is " & DTSGlobalVariables("SPRET").Value
msgbox "SPRESULT is " & DTSGlobalVariables("SPRESULT").Value

Main = DTSTaskExecResult_Success
End Function

このDTSを実行すると、sptestの結果はDTSのグローバル変数に反映されずに、初期値が表示されてしまいます。（初期値はともの0を設定）
ストアドプロシージャsptestのINSERT分をコメントアウトすると、期待する結果 3と2 が返ります。


この現象はどう解釈／解決すればいいのか、悩んでおります。
どなたか、この件でアドバイスがあれば、ご教授いただきたいのですが。
よろしくお願いします。


[ メッセージ編集済み 編集者: crab 編集日時 2007-01-10 12:45 ]
99ri
大ベテラン
会議室デビュー日: 2006/09/09
投稿数: 129
投稿日時: 2007-01-09 18:34
後続のreturn 3 が実行されていないのでは？
つまり
INSERT INTO tableA ..... でDBの例外が発生している

実行するプロシージャ事体をdrop & create しているのも気になります
crab
会議室デビュー日: 2006/12/25
投稿数: 11
投稿日時: 2007-01-09 20:59
99ri様、
コメントありがとうございます。

>後続のreturn 3 が実行されていないのでは？
>つまり
>INSERT INTO tableA ..... でDBの例外が発生している

DTSからではなく、直接

declare @SPRET int, @SpResult int
exec @SPRET = SpTest @SpResult OUTPUT
select @SPRET as SPRET, @SpResult as SpResult
go

とすると問題なく動作するので、プロシージャ自体には問題無いと考えてます。
実際そのテーブルに正しくデータがINSERTされていてます。

>実行するプロシージャ事体をdrop & create しているのも気になります
紛らわしくてすみません。
プロシージャの定義用のスクリプトをそのまま載せました。
実際は、DTSを実行する前にちゃんとプロジャーは存在します。




引用:

99riさんの書き込み (2007-01-09 18:34) より:
後続のreturn 3 が実行されていないのでは？
つまり
INSERT INTO tableA ..... でDBの例外が発生している

実行するプロシージャ事体をdrop & create しているのも気になります



[ メッセージ編集済み 編集者: crab 編集日時 2007-01-09 22:58 ]
99ri
大ベテラン
会議室デビュー日: 2006/09/09
投稿数: 129
投稿日時: 2007-01-10 10:04

１．テーブルをみるとINSERTは成功しているが
DTS後続のActiveXスクリプトに結果が引き継がれない
２．INSERTをコメントにするとDTS後続のActiveXスクリプトに結果が引き継がれる
３．ストアドプロシージャの違いはINSERTの有無のみ
４．プロシージャの結果は
DTSGlobalVariables("SPRET").Value
DTSGlobalVariables("SPRESULT").Value
で参照している

となるとトランザクション関係でしょうか？
crab
会議室デビュー日: 2006/12/25
投稿数: 11
投稿日時: 2007-01-10 12:27
99ri様、
コメントありがとうございます。

いろいろ検索してみたら、似たような現象を以下で見つけました。
http://www.experts-exchange.com/Databases/Q_21703169.html
Solutionを見るためにお金払わないといけなかったんですけど...

それで、ストアドプロシジャの先頭で SET NOCOUNT ON コマンドを実行したところうまく戻り値を取得できました。
この例でいくと、INSERT文を実行したときに、RETURN 3の前に(1 row(s) affected)と処理結果が返ってきて、それをDTSが拾おうとしてうまく戻り値が取得できなかったようです。
また、同じようにPRINT文を入れると、やっぱり戻り値を取得できませんでした。

create proc sptest
(
@SpResult int OUTPUT
)
as
SET NOCOUNT ON -- *追加*
INSERT INTO tableA .....
select @SpResult = 2
return 3

DTS側が最初に返す結果セットを読むことになるので、途中の処理で余計な結果を返さないようにしないといけないんですね。

ありがとうございました。

1

スキルアップ／キャリアアップ（JOB@IT）