- - PR -
IDENTITYのオーバーフローについて
1
| 投稿者 | 投稿内容 | ||||
|---|---|---|---|---|---|
|
投稿日時: 2005-08-11 20:41
こんばんは。初めまして。
現在、SQLServerのストアドプロシージャにて IDENTITYが設定されているTABLEにデータをINSERTしています。 INSERT対象TABLEがストアドプロシージャの引数により変わるので、 sp_executesql を使用しています。 ストアドプロシージャより抜粋 ******************************** SET @Sql = 'INSERT INTO SET @Sql = @Sql + @TargetTable SET @Sql = @Sql + ' default values ' EXEC sp_executesql @Sql SELECT @ErrCd = @@ERROR, @Seq = @@IDENTITY, @RowCnt = @@ROWCOUNT ******************************** そこでIDENTITYがオーバフローした場合に、 クエリアナライザに サーバー : メッセージ 8115、レベル 16、状態 1、行 1 IDENTITY から型 tinyint への変換で、算術オーバーフロー エラー。 算術オーバーフローが発生しました。 というエラーメッセージが出ます。 しかし理想としては、 直後に@@ERRORを取得しているので、ストアドプロシージャ内で エラーのハンドリングをしたいのです。 実際のストアド内ではIF文で@@ERRORを代入した変数@ErrCdが0ではないときは、、 と記述しているのですが、デバッグコードを入れて確認してみましたが そのロジックに到達していない状態です。 IDENTITYのオーバーフローは@@ERRORでは取得できないのでしょうか？ あるいはどのようにすれば取得できるでしょうか？ ご存知の方がいらっしゃいましたら、ご教授ください。 よろしくお願いいたします。 | ||||
|
投稿日時: 2005-08-12 12:54
おつかれさまです。 結果的に昨晩私が試した範囲では ■@@ERROR 評価でのトラップは不可能 のようです。 試していないですが， ■ON INSERT トリガで事前に防止する などできないでしょうか？ 要件によっては事前防止で満たせるような気がします。 ARITHABORT OFF で回避できるでしょうと思って実験してみたのですが， ARITHABORT OFF の状態でも今回のように IDENTITY をオーバーフローさせた 8115 はクエリの実行が停止してしまいます。 クエリ実行が停止しない「算術オーバーフロー」は 220 のコードになっています。 CREATE PROCEDURE [TrapTest] AS DECLARE @ERR int DECLARE @tint tinyint SET @tint = 500 SELECT @ERR = @@ERROR IF @ERR <> 0 BEGIN print 'overflow' END ELSE BEGIN print 'done' END ↑このコードはエラーをトラップします。 CREATE PROCEDURE [TrapTest2] AS DECLARE @ERR int DECLARE @counter int SET @counter = 0 WHILE (@counter < 1000) BEGIN SET @counter = @counter + 1 INSERT INTO [test]([value]) VALUES ('test') SELECT @ERR = @@ERROR IF @ERR <> 0 BEGIN print 'overflow' END ELSE BEGIN print @counter END END このコードはオーバーフローした時点で停止してしまいます。 海外のフォーラムで 8115 の記事はたくさんあったのですが， エラートラップの記事がちょっと見つかりませんでした。 ---------- TimberLandChapel http://blogs.timberlandchapel.com/blogs/timberlandchapel/ | ||||
|
投稿日時: 2005-08-12 23:18
TLCさま、こんばんは。
返信が遅れてすみません。 お忙しい中試していただきありがとうございます。 やはり無理なのでしょうかね。 引き続き模索しながら、 再度要件を見直してみたいと思います。 | ||||
|
投稿日時: 2005-08-13 18:18
こんな感じに記述することでexecute命令よりエラーを受け取ることができますよ。
EXEC @ErrCd = sp_executesql @Sql [ メッセージ編集済み 編集者: kizakura 編集日時 2005-08-13 19:02 ] | ||||
|
投稿日時: 2005-08-13 23:06
お疲れ様です。 残念ながら，8115 算術オーバーフローを起こしてしまうと， クエリの実行で停止してしまいます。 よって，処理コードを受け取れないんです。 やはり，挿入し終わった @@IDENTITY を管理しておいて， 挿入前にトラップかけるぐらいしか思いつきません。 綺良紗良さんの実際の要件が見えないのでなんともいえませんが， とりあえず「挿入してみてから，エラー処理する」というのは難しいと思います。 sp_execute でも処理コードがとれない再現 [code]---------- DECLARE @ERR int SET @counter = 0 WHILE (@counter < 500) BEGIN SET @counter = @counter + 1 SET @CMD = 'INSERT INTO [tinytest]([value]) VALUES (''aaa'') ' print @CMD EXEC @ERR = sp_executesql @CMD IF @ERR <> 0 BEGIN print 'fail' END ELSE BEGIN print @counter END END [code]---------- ---------- TimberLandChapel http://blogs.timberlandchapel.com/blogs/timberlandchapel/ | ||||
|
投稿日時: 2005-08-16 00:33
kizakuraさま
TLCさま 書込みありがとうございます。 お返事が遅れてすみません。 要件としては キー項目の採番のために使用しようということでした。 今までは１つのテーブルで管理していたのですが、 頻繁にロックが発生していたので それを改善するために、それぞれをIDENTITY項目のテーブルとして 個々に作成することになりました。 TLCさまの書込みの結果を報告しまして 運用面を含めて検討することになりました。 ありがとうございました。 | ||||
|
投稿日時: 2005-08-18 00:34
エラーには、エラーコードとエラーレベルがあって、
そのエラーレベルによって処理の継続・中断が決定されますね。 BooksOnlineのキーワードで[RAISERROR, セキュリティ レベル]を入力して見てみてください。 IDENTITYのオーバーフローですが、オーバーフローしないように 変数の型をテーブルの型に合わせてあげればいいだけだと思うのですが、 それができない理由などあるのでしょうか？ | ||||
|
投稿日時: 2005-08-18 11:32
かつのりさま
書き込みありがとうございます。
エラーレベル？重大度レベルが16までのものはストアド内で@@ERRORで拾えて 処理できると思っていました。 レベル16まではユーザが生成し、訂正できると書いてあったもので。 今回は、採番用に20近くのテーブルを用意し、 それぞれが、INTEGERやBIGINT、SMALLINTであったりします。 なので@@IDENTITYで取得する値を格納する変数はBIGINTで宣言してあります。 （おっしゃっていることはこのことでしょうか？） 運用として、月次あるいは年次処理の中で 採番テーブルはTRUNCATEしてオーバーフローを起こさないようにしますが、 処理するデータ量が多いため万が一のことを考えてハンドリングしたいなということで 色々調べている間に今回の問題に行き当たった状態です。 検討した結果、採番テーブルにチェック制約をつけることにより、 「サーバー : メッセージ 547、レベル 16、状態 1、行 1 COLUMN CHECK で、制約 と矛盾しています。」のエラーを取得して、TRUNCATE後に再度INSERTをかけることで回避しようという流れになりました。 みなさまありがとうございました。 | ||||
1
スキルアップ／キャリアアップ（JOB@IT）
Copyright © ITmedia, Inc. All Rights Reserved.
著作権はアイティメディア株式会社またはその記事の筆者に属します。
当サイトに掲載されている記事や画像などの無断転載を禁止します。
著作権はアイティメディア株式会社またはその記事の筆者に属します。
当サイトに掲載されている記事や画像などの無断転載を禁止します。