前回からの続き…

もしくはOPENROWSET(BULK...)関数を使う方法ならばselect句が使えるので、柔軟に対応できるだろうけど、サーバーロールにsysadmin、またはbulkadminを付与する必要があり、更にSQL Serverを実行するWindows上でのユーザーに、フォーマットファイル、インポートファイルへのアクセス権限を与える必要があり、七面倒臭い。

サーバーロールに一括読み込みステートメントを使用する権限を付与するのは、SSMS起動 -> オブジェクトエクスプローラー -> セキュリティ -> ログイン -> 対象ユーザー -> プロパティ -> サーバーロールから設定出来る。やらないと以下のようになる。

メッセージ 4834、レベル 16、状態 1、行 6
一括読み込みステートメントを使用する権限がありません。

これだけでは不十分で、このままだとフォーマットファイル、インポートファイルに対するアクセスが拒否される。

メッセージ 4861、レベル 16、状態 1、行 1
ファイル "C:\Users\arimasou16\myTestSkipCol2.Dat" を開けなかったので、一括読み込みできません。オペレーティング システム エラー コード 5(アクセスが拒否されました。)。

Windowsのどのユーザーにフォルダ権限を与えるのか？は、 SQL Server 認証を使用しBulk Insert ステートメントを実行する方法 - 元「なんでもエンジニ屋」のダメ日記が、参考になる。

私の場合、NETWORK SERVICEでもSQLServerMSSQLUser$UserName$SQLExpressでもなかった。記述されているとおりSQL Server 構成マネージャーから調べるのが確実。

[SQL Server 構成 マネージャ]→[SQL Serverのサービス]の一覧にあるSQL Serverのログオン ユーザ

SQL Server 構成マネージャーは、 SQL Server 構成マネージャー を起動する（SQL Server Configuration Manager）にあるとおりC:\Windows\System32の直下にあった。SQLServerManager13.mscの13とかはバージョンらしいので自分の場合、 14.0.17289.0 だったのでSQLServerManager14.mscだった。

ユーザー名を確認したら、エクスプローラー起動 -> フォーマットファイル、インポートファイルがあるフォルダで右クリック -> プロパティとフォルダのプロパティを開く。

そしてセキュリティタブ -> 編集 -> 追加 -> オブジェクト名入力欄にSQL Serverのログオン ユーザを貼り付け -> 名前の確認をして、該当ユーザーが下線付きとなったのを確認して、開いたダイアログをOKで閉じていく。

私の場合、MSSQLSERVERが追加するユーザー名だったんだけど、詳細設定の検索からだと見つからなかったんだよなぁ。何でだろう。 選択するオブジェクト名を入力してください の下の入力欄にNT Service\MSSQLSERVERと打ち込んで名前確認で MSSQLSERVER と下線付きとなって初めて追加出来た。

OPENROWSET(BULK…)関数を使ったXML形式フォーマットファイルを使用したテーブル列のスキップ成功

上記のような手順で以下2つの権限付与してから、OPENROWSET(BULK...)関数が使用可能となる。

• SQL Sever上でのログインユーザーのサーバーロールに一括読み込みステートメントを使用する権限を付与
• Windows上でのSQL Serverログインユーザーにフォーマットファイル、インポートファイルのアクセス権限を付与

myTestSkipCol2.sql

USE db;
GO
INSERT INTO myTestSkipCol
  (Col1,Col3)
    SELECT Col1,Col3
      FROM  OPENROWSET(BULK  'C:\Users\arimasou16\myTestSkipCol2.Dat',
      FORMATFILE='C:\Users\arimasou16\myTestSkipCol2.Xml'
       ) as t1 ;
GO

C:\Users\arimasou16>sqlcmd -S server -U user -d db -I -i "myTestSkipCol2.sql"
データベース コンテキストが 'db' に変更されました。

(3 行処理されました)

XML形式のフォーマットファイルでも成功。

XML形式フォーマットファイルを使用した後続のテーブル列のスキップ成功

何でこんなに面倒なの？って感じ。自分の場合、過去にCol4、Col5みたいのがあって、後続の列(カラム)をスキップしてbcpする事はやったことあった。だから、当たり前に中間の列(カラム)もスキップできると思ってた。

ALTER TABLE myTestSkipCol ADD Col4 nvarchar(50) NULL;
ALTER TABLE myTestSkipCol ADD Col5 nvarchar(50) NULL;

myTestSkipCol4-5.dat

1,DataForColumn2,DataForColumn3
1,DataForColumn2,DataForColumn3
1,DataForColumn2,DataForColumn3

myTestSkipCol4-5.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

C:\Users\arimasou16>bcp myTestSkipCol in myTestSkipCol4-5.dat -E -f myTestSkipCol4-5.xml -S server -U user -d db -P password

コピーを開始しています...

3 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 1      平均 : (3000.00 行/秒)

これは、当たり前に上手く行く。あと、よく知られているようにダブルコーテーションで括られたインポートファイルなんかをbcpする場合、以下のように先頭一文字にも<RECORD>を割り当てて、ある意味、先頭列(カラム)をスキップするので、勘違いして、何で出来ないんた？！と戸惑った。

ダブルコーテーション括りのカンマ区切りファイルに対するXML形式フォーマットファイル

myTestSkipColQuot.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="0" xsi:type="CharTerm" TERMINATOR="&quot;" MAX_LENGTH="1"/>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;" MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

XML形式フォーマットファイルを使用したテーブル列のスキップ失敗2

似たようにダミー<FIELD>を<RECORD>に作っても、TERMINATORが空""ってのは駄目なんですよね。\0は指定できるのだけと、今度はMAX_LENGTH の値が無効って言われちゃう。MAX_LENGTHが 0 は無効なんですね。

myTestSkipCol2Err.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="" MAX_LENGTH="0"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

C:\Users\arimasou16>bcp myTestSkipCol in myTestSkipCol2.dat -E -f myTestSkipCol2Err.xml -S server -U user -d db -P password
SQLState = HY000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]xml フォーマット ファイルの行 5、列 66 のフィールドの属性 TERMINATOR の値が無効です。

長々書いたけど、XML形式でない古い形式のフォーマットファイルを使うのが一番いい。