SQL Server でデータのエクスポート / インポートを行う場合、bcp ユーティリティ を使用しますが、ネイティブ型 (-n オプション) を使用したファイルをインポートしようとした際に、特定の条件下でエクスポートされたデータをインポートすることができず、理由を理解するのに時間がかかりました…。
その時の調査内容を残しておきたいと思います。
テスト用のデータの作成とエクスポート / インポートの動作確認
今回の事象を再現するには、次のようなデータを使用します。
DROP TABLE IF EXISTS ExportTest GO CREATE TABLE ExportTest ( c1 int, c2 varchar(100) NOT NULL, c3 int NOT NULL, c4 int ) GO INSERT INTO ExportTest VALUES(100, N'ABCD', 380, 380) GO SELECT * FROM ExportTest
作成されるデータはシンプルなもので、次のような単純な文字と数値のデータとなります。
このデータを次のような bcp コマンドでエクスポート / インポートします。
bcp "ExportTest" out F:\ExportTest.dat -T -d "TESTDB" -k -T -n -t "|" bcp "ExportTest" in F:\ExportTest.dat -T -d "TESTDB" -k -T -n -t "|"
区切り文字をパイプ記号にし、ネイティブ型でデータのエクスポートを行い、インポートを行うというものです。
そうすると、次のようなエラーが発生します。
コピーを開始しています... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 17 for SQL Server]BCP データファイル中で予期しない EOF が検出されました。 SQLState = 23000, NativeError = 515 Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]テーブル 'TESTDB.dbo.ExportTest' の列 'c3' に値 NULL を挿 入できません。この列では NULL 値が許可されていません。INSERT は失敗します。 SQLState = 01000, NativeError = 3621 Warning = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]ステートメントは終了されました。
- 区切り文字 (-t "|") を、パイプ記号から変更してエクスポート / インポートをする
- -c オプションで文字列としてデータを出力する
ことで、エラーが解消できるのですが、変更前のコマンドで失敗する理由がわからず、原因を特定するのに時間がかかってしまったというのが、本投稿の内容となります。
エラーが発生した原因
エラーが発生した原因ですが、今回のケースでは、二つの要因が重なった時に発生するようです。
- NOT NULL の int 型の項目 (c3) がある (bigint でも同じです)
- パイプ記号 (| : 7c) と、380 (0x017c) の 7c が連続して出現したバイトのパターンとなっている
今回は、これらの事象が重なっており、それによりインポートエラーとなっていたようです。
-n オプションを使用した場合、全データ型がネイティブ型 (データベースの方に合わせたデータ) で出力が行われるため、エクスポートしたファイルをテキストエディタでそのまま確認しても、すべての列を確認することはできません。
一部の文字については、そのままでも確認できていますが、数値のようなデータはテキストエディタでは判別ができていないですね。
バイナリエディタで開けば内容を確認することができます。
今回、c1 (int) は 100 が入っています。
c1 を表しているのが画像の次の部分となります。
SQL Server の int は 4 バイトとなりますので、先頭が 04 になっているのかと思います。100 は 0x00 00 00 64 ですので、その値が次に格納されています。
| 記号は 0x7c ですので、上記の値の区切り文字として、7c が入っており、このような形式で各列のデータが格納されています。
今回、問題が発生した項目は次の箇所です。
NOT NULL の int 型 (c3) : 赤枠 と NOT NULL でない int 型 (c4) : 青枠では、同一のデータ (380 : 0x017C) データの格納方法が異なっています。
- NOT NULL の int 型 : データ
- NOT NULLでない int 型 : バイト数 + データ型
上記のデータであれば、1. についてはその前の項目との区切り文字を入れると 0x7C7C01 となっていますが、2. については、0x7C047C01 となっています。
NOT NULL の場合は、int 型のデータバイト数が出力されずに、区切り文字の後にすぐにデータが出力されているようです。
この時区切り文字の 0x7c の後に 100 を表す 0x7C01 が出力されており、0x7c7c となってしまい、データの認識として「|100」ではなく「||1」というような認識となってしまっている (パイプ記号が 2 個連続となっていると認識している) ことが今回の問題のようです。
結構レアなケースかと思うのですが、NOT NULL の数値型のデータを使用していて、たまたま、区切り文字と同じバイトデータが連続して出現すると、今回のようなケースになってしまうのではないでしょうか。
今回のケースであれば、次のように、区切文字を | ではなく || にすると回避ができます。
bcp "ExportTest" out F:\ExportTest.dat -T -d "TESTDB" -k -T -n -t "||" bcp "ExportTest" in F:\ExportTest.dat -T -d "TESTDB" -k -T -n -t "||"
「||」にした場合は 「0x7c7c7c7c」というよなバイトデータが生成されると、エラーが再発するので、
INSERT INTO ExportTest VALUES(100, N'ABCD', 16874620, 380)
というような 16874620 (0x01017C7C) というようなパターンのデータが生成されるとエラーになるので、区切文字をどのようなものにすればベストなのかは少し悩みどころではありますが…。 (||||| 辺りで回避できそうな気もしますが)
今回のケースであれば、-n ではなく、-c を使用することでも回避ができるのですが、古い bcp ユーティリティでは Unicode 系のコードページが指定できず、-c でエクスポートした場合に、Shift-JIS (CP932) に含まれない文字が nchar / nvarchar に含まれているとエクスポートしたデータで ? に置換されてしまうため、テキストデータについては -n を使用したいため、オプションの指定も悩ましいものです。
今回のケースは、なぜ、エラーになっているのかが、最初わからず、ネイティブ型のエクスポートデータをバイナリで見た際に、ピンと来たのですが、データエクスポートと区切り文字は毎回悩ましいですね。