あきこのSQL ServerなBlog

Datetime型で、あれれ~？

私の部で扱っているシステムと、他部署のシステムとの間で、データ連携をするため、主にSSIS(Integration Services)を利用しています。

また、主に相手側とのデータの受け渡しは、DBのコネクションではなく、Http/Httsが中心です。データのプロトコル、フォーマットXML-RPCの場合もあれば、CSV、Soap の場合もありますが、基本的にSSISのタスクで処理可能です。ちょっと具体例が少ないのが悲しいのですが、なんとかタスクを組み合わせてパッケージ作成、Agentジョブに登録して実行させています。

さて、そんなある日。

いつも問題なく稼働していたデータ受渡しのジョブが、ある日失敗してしまいました。相手は、Soap経由でデータをもらっているシステムです。

失敗したのは、Webサービスタスクの部分。

パッケージをVisual Studioで実行してみると、なるほど失敗してしまいます。Webサービスタスクから返って来るエラーは、こんな感じ。

[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: There was an error generating the XML document...... (実際は日本語です)

プロトコルにはHttpsを使っているのですが、同じWebサービスで提供している他のメソッドは、Webサービスタスクで処理しても問題はありませんでしたので、証明書切れなどの問題ではなさそうです。

問題のメソッドだけが、プロトコルにはHttpsを使っているのですが、同じWebサービスで提供している他のメソッドは、Webサービスタスクで処理しても問題はありませんでしたので、証明書の問題ではなさそうです。

問題のメソッドだけが、Webサービスタスクを通して呼び出すと、非常に長い時間処理待ちになり、ついには上記のエラーを出して止まってしまいます。実行されると返ってくるデータ量が多いメソッドなので、もしかしたらデータ量が多すぎてタイムアウトしちゃうんじゃないかしら.....とも思いました。

さて、相手のシステム担当者に状況を聞いてみると、先方の環境では、テストしても問題はないとの事。（Rails環境で作ったWebサービスなので、Railsのテストクライアントでチェックしてもらいました。）

でも、こちらでは失敗するのは変わらず。このデータ取り込み用のWebサービスがうまくいかないと、残りの関連するジョブを実行できません。

やっていることは、Webサービスを実行し、結果をXMLとして保存し、そのデータをXSLTを使って（XML変換タスクで）CSVに変換、CSVファイルを同僚の担当するジョブに渡すというもの。

要は、Webサービスの結果をCSVに変換できれば良いので、とりあえず代替として、相手のシステム担当者の環境にあわせ、RubyのコードでWebサービスを呼び出し、想定しているフォーマットでデータを取得し、CSVに書き出すことにしました。

＃RubyのコードでWebサービスの呼び出しを行うのは、結構簡単だったので、ちょっと驚きましたが(^^;

さて、なんとか代替案を使って同じようなCSVが生成できたので、次に同僚のジョブを実行してもらいました。

そうしたら、同僚のジョブの側でもエラーが発生。（これもSSISのパッケージで作ったもので、CSVからDBのテーブルに格納するというものです）

エラーのリダイレクトなどで問題になるデータが何かチェックしたところ、どうやら、とある「日付」のデータで変換エラーが発生していることが分りました。失敗前の取り込み結果との差分を見ると、このデータが登録された日以降、ジョブが失敗するようになっていました。

問題の日付データは、なんと、「20080-04-01」となっていました。この列をSQL ServerのDATETIME型の列に取り込む際に、変換できずに失敗してしまったようです。（YYYY-MM-DDでは無いので。また、SQL ServerのDATETIME型だと、9999年までしかサポートしてないようですし）

?結局、このデータが悪さをしているんだろうな、という予感がしました。もちろん、そういう（途方もなく先の）日付があるのも間違いでは無いでしょうけれど...。

Webサービスのメソッドも、WSDLに従うと、この列はxsd:dateになっています。W3Cの決まりに従うと、フォーマットは"YYYY-MM-DD"でないといけないので、このフォーマットにマッチしないため、Webサービスタスクが失敗したのかなと判断。

相手方のシステム担当者に「このデータって、間違いじゃないですか？」と問い合わせ、実際入力ミスだったようですので、データの修正をしてもらったところ、あっさりとWebサービスタスクのエラーが解消されてしまいました....。

ただ、相手側も、日付情報はCHAR(String)ではなく日付型などでDB上にデータを格納しているはず。Railsで生成されたWSDLも、おそらくDBのデータ型に沿ってDATETIME型にしているはずです。

ふと、先方のDBがなんだったか思い出すと、PostgreSQL。

ちょっと調べてみると、日付をサポートする型には2つあるようで、TIMESTAMP型とDATETIME型が。PostgreSQLでのTIMESTAMP型は2038年までのようですが、DATETIME型は、どうやら「無制限」のようです(^^;

おそらく相手のシステムでも、一般的なDATETIME型を利用しているのでしょう...。SQL ServerではDATETIME型に”20080-04-01”を登録しようとすると怒られちゃいますが、PostgreSQLの場合は、入力ミスであってもデータとしては受け入れ可能な範囲で、エラーにはならないと思われますし...。

なんだか脱力でしたが、とりあえずWebサービスタスクで処理を行い、そちらが失敗した場合は、Rubyのコードでデータを生成するエラーハンドリングを追加しました。

また、CSVを生成し、処理を完了する前に、日付やフォーマットのチェックを行う処理も組み込むことにしました。その際に、こちらで受け入れられないデータが存在する場合は、相手の担当者に、エラーを通知するようにもしてみるつもりです。

制約の無効化とデータの削除。

緒事情により、DBのファイルサイズが実データに比してとても大きくなってしまい、メンテナンスのためにデータ移行(Export/Import)をさせないといけなくなりました。

SSISのパッケージでもExport/Importは出来ますが、そのままではかゆいところに手が届かないので、その他のスクリプトを併用しつつ準備を進めています。

さて、データの登録/削除のテストをしている時に、テーブルに制約があるとデータの削除が（もちろん）うまく行えません。

制約の削除ではなく、無効化ってどうするんだっけ...と調べてみると、NOCHECKオプションで指定すれば良いということが分りました。

でも、根が横着者なので、全部のテーブルの制約を無効化するにはどうしたらいいかな....と考え、ストアドを作ってみました。（ストアドについては、Articleの方に載せてみました: 　[ DB内のオブジェクト（テーブル）の全部の制約を無効/有効にする ]　）

一応期待通り、まとめて外部キーの制約を無効化/有効化できるようにはなったのですが、制約が無効であっても、TRUNCATEはできないんですね....。

＃Oracleは出来たと思ったのですが、違ったかな？

DELETEは出来てもTRUNCATEさせたい場合は制約を削除しないといけないので、やっぱり、ちゃんとCreate TableとかDROP Constraint/ADD ConstraintのSQLを用意しておかないとダメなのかなあ、と思いました....。

Subversion + VisualStudio

サーバサイドのアプリケーション（Webサービスなど）や、SSISパッケージ、CLR作成のため、普段はVisualStudioを使っています。

その一方、クライアントアプリケーションの作成も行っていますが、こちらはFlex2.0/AIRを使ったものが中心で、IDEにはeclipseを使っています。（必要に迫られ、へなちょこなPerlとかPythonなどもたまに書きます....）

さて、チームの中では、VisualStudioを使う回数が多いのは私だけなので、ソースコードの管理には、どちらかというとSubversionが主になってきます。

eclipseにはSubverion用のプラグインがありますので、特に問題無し。

VisualStudioの場合はどうしようかな....と思い、最初は、TortoiseSVNだけを使っていたのですが、やっぱりIDEの中からコミットやUpdate、差分の確認をしたくなりました。そこでプラグインを探したところ、AnkhSVNというものを発見。

VisualSVNという、有償のツールもありますが、今のところ、AnkhSVNで十分という感じです。

逆に、Visual SourceSafe のほうは使ったことがありません......。もしかしたら、物凄く便利なんでしょうか.....(^^;

信頼可能オプション: アタッチだけではダメだった...。

＃2008で遊んでみたいな...と思いながらも、手が出せずにいるこの頃です(^^;

さて、またもや失敗の話題です。今回の失敗は、CLRに関する事でした。

現在、Transact-SQLでカバー仕切れない処理などを、CLRを使ってコーディングしています。（データを正規表現を使ってSELECTさせたり、ストアドの実行結果を受けて外部プログラムをキックする、といった処理です）

＃CLRについては、賛否両論あるかと思いますが、どうしても方法が思いつかなかったので、利用しています...。

実際のアプリ/DBは、まだ開発段階なので、DBの停止も可能。βテスト分（という大層なものではないのですが）を公開するため、開発環境からDBをデアタッチして、コピーし、公開用のサーバにアタッチしました。（このDBには、CLR用のアセンブリも登録されています）

もちろん、公開用のSQL Serverも、CLRを有効に設定しておきました。

DBのアタッチは問題なく終了。関連するDBも、テスト環境で使っていたのと同じものを使い、動作的には全く同じに動くはずでした。

早速チェックを始めたのですが、普通のストアドも問題なく動いたので、「これで良いかな～」と、ひと安心。

ところが、同僚から『ストアドを実行するとエラーになっちゃうんですけど～』の声が...。

「なんで？ソースもデータもおんなじDBのはずなのに？？」と不審に思いながら、問題のストアドを実行すると、私の担当した部分でエラーが発生していました...。問題は、CLRで関数を実行している箇所で発生していました。

エラーメッセージは、この通り。


「アセンブリ ID 65648 をロード中に Microsoft .NET Framework でエラーが発生しました。
サーバーのリソースが不足しているか、PERMISSION_SET が EXTERNAL_ACCESS または UNSAFE 
に設定されていて、アセンブリが信頼されていない可能性があります。」

Management Studioで問題のアセンブリを参照すると、特にエラーのような表示は出ていません。関数も登録されています。（デタッチ/アタッチしただけなで）

でも、単純にCLRを単体で実行しても、同じエラーが....。

仕方なく、Visual Studioで、テスト公開用のDBに配布しなおしてみようとしたところ、これまたエラー。（開発用ではエラーになりません）

あわてて調べると、アタッチしたDBのTRUSTWORTHYプロパティが、Falseだったことが発覚。

この値は、ManagementStudioからは変更できなかったので、 ALTER DATABASE xxxxDB SET TRUSTWORTHY ON でTrueに変更したところ、配布OKになりました。

単純に、DBをアタッチするだけでは、TRUSTWORTHYの値は引き継がれないんですね.....。（どこのものとも分からないアセンブリをそのまま信頼するのは、確かに問題なんでしょうけれど）

PerlからADO

最近、PerlからSQL Serverへの接続を行うコードを書いていたので、その覚え書きです。

実は、ストアドや.NET系のコーディングを始める前は、PerlやJavaがメインで、DB操作のためのSQLは、コードや設定ファイルの中に書くことが中心でした。(ただしOracleとかでした)

その後、SQL Serverを中心にお仕事をするようになってから、ストアドを多用するようになり、コードもC#などで書くようになっていきました。

さて今回、XML-RPCで外部APIと連携を行う必要が出たので、PerlでSQL Serverの操作を行うことになりました。（C#でもXML-RPCは実装可能だったのですが、私以外のメンテナーがいないので、同僚がメンテナンスしやすいPerlで行きましょう、ということになったのでした...）

ただ、私としては、できるだけ面倒なSQLはコードに書かず、サーバ側のストアドでカバーしたいというのが希望でした。同僚も、SQL(ストアド）の方は操作/修正には問題無いので、この方針で。

ストアドを使うメリットは、まずは、Perl中にSQLを書かなくて済むこと。（コード内で、@や\といった特殊文エスケープしたりするのが面倒なんですよね....）
同じストアドは（データ取得用ですけど）、ManagementStudioからも呼べるし、WebServiceでも呼び出せます。JavaのJDBCでも呼べますね。

さて、問題は、PerlのDBIで、SELECTステートメントを返すストアドをどう実行するか、ということと、複数SELECTの結果が返る場合、値をどうやって取得するかです。

どうやらDBI + ODBCだとうまくできません....。（というか、とんでもなく久しぶりなので、よく分らない）
かと言って、またもPerl中に長～いSQLは書きたくない。

一方、ADO .NETだと、SELECT結果は、複数STATEMENTも含め、DataSetというオブジェクトで返してくれるので、それぞれテーブル名や順番に取り出すことができます。（ここが、私が.NETのASPにしたいよ～と言った理由のひとつでした）

で、かなり時間がかかってしまったのですが、調べた結果、Win32::OLEを使うと、ADOを用できることが判明。

若干C#などで扱うのよりは面倒ではありますが、これでいくらか幸せになりました。

use strict;
use Win32::OLE;
use vars qw($dsn);

# Data Source Setting
$dsn = "driver=SQL Server;".
"Server=localhost;".
"database=TestDB;".
"Trusted_Connection=no;".
"AutoTranslate=No;";

# Connection Open
my $Conn = Win32::OLE->new("ADODB.Connection");
$Conn->{CursorLocation} = 3;? # the same as adUseClient
$Conn->{Open} = $dsn;

if (Win32::OLE->LastError() != 0) {
print "Failed creating ADODB.Connection object -> "
    . Win32::OLE->LastError();
exit 0;
}

my $Cmd = Win32::OLE->new("ADODB.Command");

if (Win32::OLE->LastError() != 0) {
   print "Failed creating ADODB.Command object -> "
       . Win32::OLE->LastError();
   exit 0;
}

my $Id;
my $ReferenceId;

####
# For Test
#
# 実際は、CGIのRequest Parameterやコマンドライン引数などから受け取ります。
#
###
$Id = 8;
$ReferenceId= 300;

# Command Execute
$Cmd->{CommandType} = 4; # Execute as Stored

# Prepared Statementを使わない場合はこんなかんじ。
$Cmd->{CommandText} =
    "dbo.getSampleData ($Id, $ReferenceId )";
$Cmd->{CursorLocation} = 3;
$Cmd->{ActiveConnection} = $Conn;

$Cmd->{Prepared} = 1;
$Cmd->{CommandTimeout} = 15;

my $rs = $Cmd->Execute();

if (Win32::OLE->LastError() != 0) {
    print "Failed opening ADODB.Recordset object for Command -> ". Win32::OLE->LastError();
    exit 0;
}

my $rs_count = $rs->{RecordCount};
print $rs_count . "\n";
if (!$rs_count) {
    print "Record not found to post or close.\n";
    exit 0;
}

print "\n------------1st SELECT Result-------------\n";
while ($rs->EOF != 1) {
    print "Id = ". $rs->Fields('Id')->{Value}."\n";
    print "Name = ". $rs->Fields('Name')->{Value}."\n";
      :
    $rs->MoveNext();
}

my $rs1 = $rs->NextRecordSet();

print "\n------------2nd SELECT Result-------------\n";
while ($rs1->EOF != 1) {
    print "ReferenceId = ". $rs->Fields('ReferenceId')->{Value}."\n";
    print "ProductName = ". $rs->Fields('ProductName')->{Value}."\n";

$rs1->MoveNext();
}
$rs->Close();
$Conn->Close();
exit 0;

Prepared Statementを使ってストアド実行時の引数を渡したい場合は、こんな感じ。

$Cmd->{Prepared} = 1;

#ストアドの戻り値を指定する場合　（Direction = 4）
$Cmd->Parameters->Append(
$Cmd->CreateParameter('@retval', 3, 4));

# ストアドへの引数はこちら
$Cmd->Parameters->Append($Cmd->CreateParameter('@Id', 3,
1, , $Id));
$Cmd->Parameters('@Id')->{Value} = $Id;
　　:

my $rs = $Cmd->Execute();

if (Win32::OLE->LastError() != 0) {
adoError(Win32::OLE->LastError());
}

# 戻り値を取得します
$retval = $Cmd->Parameters('@retval')->{Value};

ただ、通常のADOのように、PerlからでもResultSetにテーブル名を付けて、明示的にアクセスできるのかは、まだ分りませんでした....。

また、ストアドへのパラメータを指定する部分は、Win32::OLE::Variantを使えばもっといいのかもしれませんが、うまく動かなかったので、以下のサイトを参考に直接値を指定しました。

http://msdn.microsoft.com/library/ja/default.asp?url=/library/ja/jpdnvid/htm/VintrDev/VISQL/visql.asp

また、コーディングの際は、ActivePerlを使い、eclipse + Perl EPICというPerl用のプラグインを使ってみました。これもなかなか便利でした。(Visual Perlっていうのもあるんですね)

AD:レプリケートしたのはいいけれど。

またもトラブル系、失敗系のお話です...。

試行錯誤しながらWindowsのドメイン環境で生活し始めて、3年ほどが過ぎました。

ドメイン生活のきっかけは、異動先の部署に、買ったばかりのSmall Business Server? 2003 (以下：SBS)があったからです。

※ただ、上司によると、別にドメイン生活がしたかったわけではなく、SharePointとSQL ServerとExchange Serverがバンドルされていたから、SBSを購入したとのこと。結局、この機能を使うためには、ドメイン利用が必要になったのですが....。

その後、SQL Serverの利用を中心に、試行錯誤しつつもドメインでの生活を続けていきました。一度この環境に慣れてしまうと、とくに認証まわりが便利になるため、ドメインに参加させるサーバやクライアントPCが、次第に増えていきました。

ただ、ドメインコントローラ（以下DC）と、メンバサーバのネットワークセグメントが別なため、ネットワークトラブルの時は、DCとの通信ができず、困ることもありました。これについては、同じネットワーク内にDCの複製を作ることで対応しました。（このあたりも、管理ツールお任せで、あまり深く考えずに行っていました）

さて、ドメインの利用は、Virtual Serverを導入したことで、さらに増えていきました。

ここで私は、「DCの冗長性のために、仮想マシンでもDCを複製しておこう！」と考えました。これも、作成と複製自体は、あっさり終了しました。

※実は、DC同士は同じOSでないとダメという制限があるようで、複製のサーバは、Windwos 2003 R2にしようと思ったのですが、SBSに合わせたOS/ServicePackでしか稼働させることができませんでした....。また、Windows 2003 Serverとは言え、SBSはどうやら特別らしく、これも正直なところ扱いに難儀したのですが。

とりあえず仮想マシンも含め、DCは3台になり、私も少しは安心感がアップ。

ところが、その後、Virtual Serverのリソースを別の仮想マシンに多く割り当てないといけない状況になり、リソース節約のために、DCとして作成した仮想マシンを停止させることにしました。

そのまま、すっかり停止させていたことを忘れて、いつしか2ケ月ほどが経過。

やがて、最初のDCとなったSBSのサーバ状況のレポートメールから、「NTDS Replication / EventID: 1864」のエラーが通知されるようになりました。もちろん、原因はDCの仮想マシンを停止させてしまったことでした.....。

＃あとから知ったのですが、仮想マシンを使う場合の注意事項は、ちゃんと、こんなところに出ているんですね(^^;;;

仮想ホスト環境で Active Directory ドメインコントローラをホストする場合の考慮事項
- http://support.microsoft.com/kb/888794/ja

とりあえずサポートツールをDCになっている各サーバにインストールし、repadminを使って、あとからメンテナンスすれば良いや～、と単純に思って悠長に構えていました。実際、警告が出てから半月くらいは、特に問題は無かったからです。

ところが、3連休中に、この同期のための有効期限(tombstone)が切れてしまっていたのです...。

さて、大変なのが後始末。

単純に同期を取り直そうとしても、時すでに遅く、素直には実施できません。

じゃあ、DCから降格させれば良いかな、と思ったのですが、これまた正常に複製されていて、同期が取れた状態でないと、降格できなかったのです。

ドメインのメンバには、SBSのDCをプライマリのDNSとしていたので、SBSのDCが大丈夫なら問題ないかな、と考えていたのですが、これも甘かったようで、ドメインへのログオンが失敗する、といったトラブルに見舞われてしまいました。

この原因は、グローバルカタログになっているDCにエラーが発生すると生じる問題のようなのですが、私は、詳しい理由がまだ見つけられていません。

ひとまず、クライアントを再起動すれば、ドメインへのログオンが出来るようになったのですが....。

自分の出来心のおかげで、とんでもないことになった連休明けでした。（幸い、メインに利用されていた2台のDC間で同期は取れていたので、被害はまだ少ないのですが）

あれこれ試した結果、なんとか同期を取らせ、DCの降格まで行うことができました。この顛末は、また別途。

とか言いつつ、まだ落とし穴がありそうな気もします。

知識も乏しいのに、DCをいじった事には変わりないので、これでしばらく様子を見ないといけません...。

トランザクションのネスト対応について

先の投稿で書いた、「知らなかった：ストアドプロシージャのネスト。」の続き。

一緒にコーディングしていた同僚の働きママさんに、大変ご迷惑をおかけしてしまいました...。

その後、自分なりに認識した制限と、対策をこちらのArticleのほうに書いてみました。

トランザクションのネスト対応

おそらくまだ不備なところとか、認識間違いがありそうな気がしますので、後学のために、ご指摘いただければ幸いです。

今回も含め、色々システム連携の必要が出てきて、改めて難しさや自分の情けなさを痛感。

以前、ESB製品のセミナーを受けた時、トレーナーの方が、サービス連携のポイントを以下のように話してくださっていました。

各システムの提供するサービスは、『粒度を荒く』して作る。
小さなサービスをラップ（マッシュアップ）して、大きな『ビジネスプロセス』を作る
- オーケストレーション、という表現だったかと。

ストアドプロシージャや関数も、できるだけ小さな処理単位で作って、いろんなところで再利用できるようにしたいな...と思っていたんですが、なかなか私には難しいところです..。

FOR XML PATHモード。

覚え書きのようなものですが....。

SQL Server 2005では、FOR　XMLの機能が、かなり良くなっていました。（私個人的には、『とっても』でした）

SQL Server 2000の時と同じ、FOR XML Auto　とか RAWモードももちろん使えます。
ただし、上記のモードだと、お手軽ではあるんですが、Viewや一時テーブル、テーブル変数を使ったSELECTが、思いもよらないXMLで出力されることがありました。

でも、2005だと、PATHモードのおかげで、かなり自分の希望に近い形で、XMLを出力させることができるようになりました。

[今までのパターン]

SELECT Id AS Id, Name AS Name, ISNULL(NickName, Name) AS NickName
FROM [Test].[DocumentType] AS "DocumentType"
WHERE IsEnabled = 1
FOR XML Auto, Elements

-- 実行結果

<DocumentType>
<Id>1</Id>
<Name>見積り</Name>
<NickName>みつもり</NickName>
</DocumentType>
<DocumentType>
<Id>3</Id>
<Name>交通費精算</Name>
<NickName>こうつうひ</NickName>
</DocumentType>

上記は、FOR　XML Autoモードの場合ですが、SELECT結果が複数あった場合は、1行につき1つのXMLで、複数のXMLが返ります。(e4x的に言うと、XMLListという感じでした。)

この場合、私が直面した困った点は、以下の通りです。

ドキュメントルートのタグ（たとえば<root>...</root>）タグは自分でつけないといけませんでした。
また、要素と属性を混在したXMLにするには、EXPLICITモードとかを使わないといけなかったのですが、これが非常にわかりづらいものでした。
Viewを連結させた場合は、予想外の入れ子構造になっていたりしました。

なんとかあれこれ操作して、解決はできていたのですが、制限があって断念していたことも、いくつかありました。

[PATHモード利用]

さて、2005のドキュメントを見ると、PATHモードというものがあるのに、遅ればせながら気が付きました。

PATHモードを利用すると、わりと理解しやすいSQLで、XMLの出力をコントロールできるようになります。
EXPLICITモードというのが2000から使えたので、試してみたのですが、上記の通り、これが超理解に苦しみました。PATHモードを知った時、先にこっちを知っていれば...と、すくなからず後悔しました。

さて、SQLはこんな感じに指定しました。AS -> 要素名、@ -> 属性名になり、データは要素/属性のテキストノードとして格納されます。

SELECT (SELECT [Id] AS "@Id",
      [Name] AS "@Name"
      ISNULL([NickName], [Name]) AS "@NickName"
     FROM [Test].[DocumentType]
      WHERE IsEnabled = 1
     FOR XML PATH('Item'), TYPE) AS "DocumentType"
     FOR XML PATH(''), ROOT('root') -- root要素を追加

-- 実行結果

<root>
<DocumentType>
<Item Id="1" Name="見積り" NickName="みつもり" />
<Item Id="3" Name="交通費精算" NickName="こうつうひ" />
　　　　　　　　　　　：

今度は、root要素も指定できるし、要素と属性を混在させたりできます。

※ Namespace付きにする場合は、こんな感じで指定できました。

WITH XMLNAMESPACES (
   'http://blogs.passj.org/akiko' as Akiko)
　SELECT (SELECT [Id] AS "@Id"
      ,[Name] AS "@Name"
      ,ISNULL([NickName], [Name]) AS "@NickName"
FROM [Test].[DocumentType]
WHERE IsEnabled = 1
FOR XML PATH('Akiko:Item'), TYPE) AS "Akiko:DocumentType"
FOR XML PATH(''), ROOT('Akiko:root')

※WITH XMLNAMESPACES (....)がポイント。

結果はこんな感じになります。

<Akiko:root xmlns:Charon="http://blogs.passj.org/akiko">
<Akiko:DocumentType>
<Akiko:Item xmlns:Akiko="http://blogs.passj.org/akiko" Id="1" Name="見積り" NickName="みつもり" />
　　　　　:

</Akiko:DocumentType>
</Akiko:root>

もしかして、使い方を間違っていたり、まだまだ分っていないことがたくさんありそうですが、私には嬉しい機能でした。

そんなことを書いているうちに、もう2008....。いったいどうなるのでしょうか、楽しみです。

知らなかった：ストアドプロシージャのネスト。

現在作っているシステムでは、ビジネスロジックは、大部分をストアドプロシージャでコーディングしています。

通常のSQLからの利用や、SSIS経由のジョブからの実行、というだけでなく、SOAP化してSQL Server外からも実行するのが簡単で、再利用しやすいからです。（IISやNative XML Webサービスを使って。）

さて、そうこう進めているうちに、「1つのストアドの実行後、それだけで終わらず、処理結果を他のシステムと渡さないといけない」といった、状況が増えてきました。ビジネスロジックをアプリケーションサーバで組んでいれば、他システムとの連携はやりやすかったのかもしれませんが、上記の通り実装はストアドが中心。

処理結果を連携させる必要のある、もう一つのシステムにも、本来はDBの外から連携させた方が良いのですが、能力と手間の問題で、ストアドからストアドの呼び出し、という形で連携させてしまえば良いかな、と安易に考えてしまいました。（もう一つのシステムのデータソースも、同じSQL Serverのインスタンス上にあるので...）

ところが、これが大きな落とし穴になってしまいました(T_T)

「こっちのストアドから、そっちのストアドを呼び出せば良いのよね」と、もう1つのシステムの担当者と軽く話を進めたのですが、表面上は1つづつしかネストしないと思っていたストアド、お互い、既にその自身内に、いろんなストアドや関数をネストしていました。

また、ストアドの結果を受けて処理を分岐させるため、INSERT...EXECで結果を受け取っていたのですが、自分の担当分では正常に処理が終了するのに、さらに相手に自分の実装したストアドを実行してもらうと、以下のようなエラーが発生してしまい、正常に実行できません!

INSERT-EXEC ステートメント内では ROLLBACK ステートメントを使用できません。

また、それぞれのストアドには、Transaction処理（エラーが発生したら、Rollbackする）も設けていたのですが、何度修正しても、以下のようなエラーが発生してしまいました...。

EXECUTE 後のトランザクション数は、 COMMIT TRAN または ROLLBACK TRAN ステートメントに間違いがあることを示しています。以前の数 = 1、現在の数 = 0 です。

せっかく再利用できると思ってストアドを多用したのに、ほとんど同じコードを組み込み直さないといけないのかしら...と、だいぶ辟易していたころに、こんなサイトを見つけました。

[ How to Share Data Between Stored Procedures ] http://www.sommarskog.se/share_data.html#INSERTEXEC

ここに、以下のような一文が。

It can't nest. If some_sp tries to call some_other_sp with INSERT-EXEC, you will get an error message. Thus, you can only have one INSERT-EXEC active at a time. This is a restriction in SQL Server.

いつも参考にしていた本には、ストアドのネストの場合は、INSERT-EXECで結果を受け取れば良いという旨しか書かれていなかったので、こういう制限があるとは、分りませんでした....。（実際に、これって本当なんでしょうか？？）

また、ロールバックのエラーについては、こちらを呼んで、なんとか納得いたしました...。

[ Handling SQL Server Errors in Nested Procedures ] http://www.vfpconversion.com/Article.aspx?quickid=0305111

以前、アプリケーションサーバ上（J2EE/EJB)で、ビジネスロジックを組んだことがあるのですが、その時は、Commin/Rollback処理を含んだ1つのビジネスロジックを、さらに別のCommin/Rollback処理を含むビジネスロジックでラップした場合、トランザクションマネージャがうまく処理をしてくれていました。（一番上のビジネスロジックのトランザクションに合わせる）

そこでの理解が足りなかったのかもしれませんが、ストアドプロシージャもそういうことが可能と思って、安易にネストさせてしまっていました。これがいけないんでしょうね...。

また、本当は、他システムとの連携は、BASやらService Brokerを使ったりするほうが望ましいのですよね...。

いまごろですが、CTE。

部署の情報を格納したテーブルを作っています。

ここには、ParentIdという列に、1つ上の親の部署Idを格納しています。ここから、上下の関係を導出する...という具合に考えているのですが、この方法だと、どうしても再帰クエリになってしまいます。

Oracleでは、CONNECTというのを使うとうまくできそうなんですが、SQL Serverだとどうしていいのか分かりません...。

（階層は深くても4階層くらいまでなので、固定のSQLでも頑張ればいいのですが）

また、データはDBはRDBにフラットに定義しておいて、実際にアプリケーションに渡す場合は、XMLに加工して使おうと思っています。

色々探していたら、CTEというのが使えるんですね。

正しい方法じゃないかもしれませんが、とりあえず、やってみると....。

USE Common

GO
WITH Recursive (ParentId, Id, Code, Name, Level)
AS
(
-- Divisionの定義
    SELECT D.ParentId, D.Id, D.Code, D.Name, 
        0 AS Level
    FROM Division AS D
    WHERE ParentId IS NULL
	AND IsEnabled = 1
    UNION ALL
-- 再帰の定義
    SELECT D.ParentId, D.Id, D.Code, D.Name, 
        Level + 1
    FROM Division AS D
    INNER JOIN Recursive AS R
        ON D.ParentId = R.Id
	AND IsEnabled = 1
)
-- 再帰テーブルから取り出し
SELECT ParentId, Id, Code, Name, Level, replicate('-', (Level)* 10) + Code 
FROM Recursive

結果。個人的に、かなり感動しました(^^;

FOR XML, PATHモードを使えば、そこそこ希望しているものが作れるかもしれません...。