【概要】
bcpコマンドによりCSVのエクスポートを行える。
OracleのSQLローダのようなものである。
コマンドプロンプトからSQLServerに対し認証を行い、
任意のテーブル/クエリでimport/exportを行う。
これを敢えてストアドから実行するサンプルを示す。
【コード例】
■テーブル情報を直接出力
DECLARE @CMD VARCHAR(1000);
SET @CMD = ' bcp [dbo].[TABLEA] out C:\WK\TABLEA.csv -Usa -Pxxxxx -S MSSQLSERVER'
EXECUTE master.dbo.xp_cmdshell @CMD
■クエリを実行してCSV出力
DECLARE @CMD VARCHAR(1000);
--Windows認証の場合の例
SET @CMD = ' bcp "select * from tablea where flg = '1'" queryout C:\WK\TABLEA.csv -c -t, -S MSSQLSERVER -T'
--SQLServer認証の場合の例
--SET @CMD = ' bcp "select * from tablea where flg = '1'" queryout C:\WK\TABLEA.csv -c -t, -S MSSQLSERVER -U user1 -P password1'
EXECUTE master.dbo.xp_cmdshell @CMD
※パラメータの意味
-c SJIS指定
-w(unicode:UTF-16)
-c -C {codepage} コードページ指定
-t,区切り文字「,」
-S:サーバ名 (ホスト名 またはホスト名\SQLServerのインスタンス名)
-T:Windows認証
【注意事項】
・既存のファイルは上書きされてしまうので注意(追記不可)
http://msdn.microsoft.com/ja-jp/library/aa337544.aspx
⇒既存のファイルを指定すると、ファイルは上書きされます。
・queryoutで記述できるクエリの文字列長は1023byteまで。
長いクエリになる場合はViewを作るなどの対応を行う。
・上記例ではbcp実行用のコマンドをストアドから実行できるようにするため、
xp_cmdshellでbcpのコマンドを実行している。
OSコマンドの実行権限がない場合は、以下のエラーメッセージが表示される。
SQL Server によって コンポーネント xp_cmdshell のプロシージャ sys.xp_cmdshell に対するアクセスがブロックされました。
⇒この場合、ファセット→ServerSecurity→XPCmdShellEnabledをTrueにすることで、
ストアドからOSコマンドの実行が可能となる。
http://msdn.microsoft.com/ja-jp/library/ms161956.aspx
・UTF-8での出力不可!
意外なことに、UTF-8に対応していない(と、以下ページにもしっかり書いてある)。
どうしてもUTF-8にしたければ、出力してから別手段で変換かけるしかない。
[参考]
bcp ユーティリティ
http://msdn.microsoft.com/ja-jp/library/ms162802.aspx