SQLServer2008 R2 varchar(MAX)型の長さ制限について

Question

[環境]
--サーバー--
OS:WindowsServer2008 R2 Standard 64bit(パッチ適用なし)
DB:SQLServer2008 R2 Standard 64bit(6/1までの最新パッチ適用あり、全てなしの2環境)

--クライアント--
OS:WindowsXP SP3 32bit
VB:VB.NET2008

[質問]
上記の環境で開発していたところ、TableAdapterのUpdateメソッドで「Varchar(MAX)」型のカラムに一定の長さ(byte)のデータを入れると
「System.Data.SqlClient.SqlException: 着信の表形式のデータ ストリーム (TDS) リモート プロシージャ コール (RPC) プロトコル ストリームが不適切です。パラメーター 5 ("@カラム名"): データ型 0xA7 に、無効なデータ長またはメタデータ長が指定されています。」

というエラーが発生してしまいます。

「一定の長さ」というのは、一概に何バイト以上はエラーというわけでは無いようで、
調査した結果では8,000バイトくらい以下、9,777バイト以上はエラーは出ず、
この間の長さ(8,000くらい～9,776)ですとエラーとなるようです。
挿入しようとしたデータはShiftJISの全半角混在データです。
バイト数はEncode.GetByteCountで取っています。

また、ManagementStudioにてInsertクエリを作り「REPLICATE(CONVERT(varchar(MAX), ' '), 9500)」などと
データを挿入するときちんと9,500バイト入りました。
ちなみに、テーブルのlarge_value_types_out_of_rowは'1'(ON)となっています。

上記より、どちらかというとクライアント側の問題なのかな？と思いますが
エラーメッセージで検索してみても情報が乏しく、解決にはいたっておりません。
何か情報をお持ちの方いらっしゃいましたらご教示いただけませんでしょうか。

Answer 1

Stringの文字としてカウントすると8000文字以下ですが、Shift-JISのバイト数でいくと8000を超える時にエラーが出るようですね＾＾；

ちなみに列をNVARCHAR(MAX)としてみたら、エラーは発生しませんでした。
この時のクライアント側で追加されるコードですが、以下の通りになっていました。

試しにVARCHAR(MAX)として作成されたDBに対して、Global.System.Data.SqlDbType.VarCharをGlobal.System.Data.SqlDbType.NVarCharに変更したらエラーは発生しないことから、super-hakoneさんの ご想像の通り、クライアント側の変換でなにがしかの問題があると思われます。
※試しにプロファイラをかけたとき、エラーが出る場合はTSQLのテンプレートで取得したProfilerにはINSERTが表示されませんでした。

もし可能なのであれば、テーブルをNVARCHAR(MAX）にされてはいかがでしょうか。

VARCHAR(MAX) 

Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@misc", Global.System.Data.SqlDbType.VarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "misc", Global.System.Data.DataRowVersion.Current, False, Nothing, "", "", "")) 

NVARCHAR(MAX) 

Me._adapter.InsertCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@misc", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "misc", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", "")) 

 

Answer 2

情報ありがとうございます。
その後私自身でも調査を継続し解決出来ましたので記載します。

原因となっていたのはまさにChukiさんの仰る部分で、SqlDbTypeをVarCharからTextにしたところエラーが発生しなくなりました。
(NVARCHARはUnicodeでしたっけ？今回は文字コードをShiftJISに統一していますのでとりあえずText型にさせていただきました。)

変更方法はDataSetデザイナのTableAdapterのプロパティウィンドウのInsertCommandを展開し、
Parametersの該当カラムのProviderTypeをTextに変更‥という感じで行っています。
(SqlDbTypeという名前でないのがちょっと気になりますね)

ただ…今回の対応策は以下①のリンクの情報を参考に行ったのですが(環境に記載するのを忘れてしまいましたが開発環境は.NET 3.5です)
②の情報には「DBでvarchar(MAX)を使うときはVarCharを用いる」というような感じの記述があります(翻訳がちょっと微妙ですが)
.NET 4.0からはVarCharでいけるのかと思ってインストールしてみたらやっぱりエラー。そういう事じゃないんですかね

①SqlDbType列挙体(.NET Framework 3.5)
http://msdn.microsoft.com/ja-jp/library/system.data.sqldbtype%28v=VS.90%29.aspx

②SqlDbType列挙体(.NET Framework 4.0)
http://msdn.microsoft.com/ja-jp/library/system.data.sqldbtype%28v=VS.100%29.aspx


あと私の質問文でも書きましたが、ある一定長以上だとエラーがでなくなってしまうこともいまいちよくわかりません。
Data関係は難しいですねぇ～

とりあえず今回の件は解決ということにさせていただきます。
Chukiさん情報ありがとうございました。

Answer 3

情報共有ありがとうございます＾＾

以下はあくまで憶測ですが、いろいろと触っていて以下の感触を受けました。

＞②の情報には「DBでvarchar(MAX)を使うときはVarCharを用いる」というような感じの記述があります

おっしゃる通り、フレームワークが作った型を変えるのは不具合の元なのでしないほうがよいと思います。
あくまで検証結果の提示だとお考えくださいm(_ _)m

＞あと私の質問文でも書きましたが、ある一定長以上だとエラーがでなくなってしまうこともいまいちよくわかりません

どうも、MAXを使っている場合、文字数に対して8000文字を超えない場合、内部的にはMAXではない型に暗黙的に変換されるようなことがWebを検索していると出てきます。
であるならば、DBクライアントはStringクラス内の文字数（＝Unicode)で数えたとき、8000を超えていないので8000しか用意していないけれど、実際は8000文字以上が飛んでくるのでエラーが出るのもうなずけます。また、Unicodeとしての文字数が8000文字を超えていれば、MAXの容量で確保されるため、エラーが発生しなことも理由がつきます。
（ここでの文字数は、Unicodeに変換されたStringクラスが、全角・半角関係なく文字として数えた文字数です。Nがつかない型は、サイズの差異を考慮してくれないので、こういった問題がよくおきます。）
＃Web上に出てくるのはSQL Server内での話ですのであくまで私の想像でしかありません。（明示されている資料を見つけることができませんでした）

【ご参考】
大きな値のデータ型の使用
http://technet.microsoft.com/ja-jp/library/ms178158(SQL.90).aspx

SQL Server データ型のマッピング (ADO.NET)
http://msdn.microsoft.com/ja-jp/library/cc716729.aspx
※DB側がShift-JISだった場合、自動でUnicodeにしてStringへ代入してくれます

 

Answer 4

たびたびありがとうございます。

私の方でもいろいろいじってみましたが、Chukiさんの仰る通りのようです。

以下、ちょっと実験してみました。

len	byte	結果
7,999	8,000	OK
7,999	8,001	NG
7,999	12,001	NG
8,000	8,001	NG
8,001	12,003	OK

上記はVB.NET2008からTableAdapterを用いてInsertしたときのデータの「長さ」と「バイト」の関係をまとめたものです。

カラムのProviderType(SqlDbType?)はVarCharで、データの文字コードはShiftJISです。

SJISの全角文字と半角スペースを併用していろいろな長さのデータを突っ込んでみました。（わかりにくかったらすみません。。）

結果を見ると、たしかに文字数が8,000文字を超えない場合は(8,000byte超の領域を確保されないのか)8,000byte超のデータを入れるとエラーになります。

文字数が8,000文字を超える(8,001文字以上)と8,000byte超のデータも入るようになります。

やはり8,000文字あたりがしきいのようです。（もしかして結構常識的なこと？？）

 

文字コードってめんどくさいですね^^;

Answer 5

情報共有ありがとうございます。

＞文字コードってめんどくさいですね^^;

本当にその通りですね＞＜。
この上、.NET Frameworkや他のプラットフォーム、そしてOracleとか、DB2 UDBとかの連携も考えると、最近はUnicodeばかりで作るようになってきましたorz

 

Answer 6

SQL Server では、各テーブルの Text in Row プロパティ(デフォルト値 8000)を超えるバイト数のデータは、行データとは別の領域に保存するようになっています。

この計算処理に問題があって、「まだ 8000 バイトに到達していない」と誤解して、8000 バイト以上の文字列を 8000 バイトしか保存できない行データとして書き込もうとしてエラーになっているのでしょうね。考えられることは、

• ShiftJIS ではなく ASCII 等のクライアント エンコードで接続しているため、計算できない（たまたま Default Encoding を使用しているから、日本語Windowsでは偶然 ShiftJIS で処理された）
• プロバイダ (System.Data.SqlClient?) の不具合

のような問題が発生しているのでしょうね。

追記： Compact Framework のドキュメントだと、System.Data.SqlClient の char/varchar のサポートは、Generic-Laten1 のみであることが明記されており、デバイスのエンコーディングの都合で、上記のような挙動は「日本語文字などのサポートされない文字が渡された場合」の仕様であることが明記されています。フル版の Framework については、すぐには検索できませんでした。