雪国SEのひとりごと

愚痴ったり、メモったり、その他色々。人生は永い。焦る事は無い。人生前向きに考えると、きっといいことがあるよ。
【重要】当ブログの記事にて生じたいかなる損害について保証いたしません。

Japanese_CI_AS

照合順序のトラブルを解決4

 以前にも書いたネタであるが、解決策が見つかったので記述。本来の解決策では無いが、システム検証には充分使えるでしょう。

 社内開発環境と客先環境でデータベースが違う。データベースソフトウェアはどちらも同じSQL Server 2000なのだけど、唯一違うのが照合順序

 客先はJapanese_BINで構築されており手順書通りのインストールでそれで正解なんだけど、社内は何故かJapanese_CI_ASで構築されています。

 SQL Server 2000のシステムテーブル(SQL Server 2000をインストールすると自動生成されるテーブル)の照合順序は、SQL Serverデフォルトの値。つまりは、客先はJapanese_BIN、社内はJapanese_CI_ASとなるのだ。

 前置きが長くなりましたけど、ある処理を行うと、

equal to 操作での照合順序の競合を解決できません

というエラーメッセージが表示されて先に進む事が出来なかったのだが、これを解決できたのでそのお話。

 システムではエラーログを出力しているので、それを頼りにデバッグ開始。処理を追うと、プログラム側ではなくてストアドプロシージャでの不具合だということが判明した。そのストアドプロシージャ内で作る仮想テーブルが原因のようである。

 各ユーザーテーブルの照合順序は「Japanese_BIN」。ストアドプロシージャで作成される仮想テーブルの照合順序は、SQL Server 2000の規定値、即ち、客先ではJapanese_BIN、社内では「Japanese_CI_AS」。
ユーザーテーブルと仮想テーブルで照合順序が違う。それらを比較したり、何らかの編集をする処理でエラーとなるのですよ。

 根本的な解決は、SQL Server 2000の再構築。つまりは再インストールを行えばよいのですけど、既存のデータベースのバックアップやらが大変なので、どうにかして現状維持で回避できないか考えた。

 以前に、Japanese_CI_AS_Escapeという仮のデータベースを作り(照合順序はJapanese_BIN)、それをSQL Server 2000 のデフォルトデータベースとしたのだけど、根本的な解決策にはならなかったのですね。

 話を戻します。ストアドプロシージャで仮想テーブルを作る処理にとある記述を追加すれば解決する事が判明した。

 各フィールド定義している行に「COLLATE JAPANESE_BIN」と追加するのだ。こうすることで、そこはJapanese_BINであることを明示できたわけで、これでしっかりと文字列比較ができるのだ。
 ただ、このやり方の痛いところは、ストアドプロシージャを直したという事。データベースを丸ごと復旧したり、現地のデータベースをテストとして入れると、当然ながら現地にはCOLLATE JAPANESE_BINの文は入っていないわけで、全てが元に戻ってしまう。なので、そのストアドプロシージャをどこかに対比しておく必要があるのですわ。

今後の作業に、わずれずに追加しておきましょう。

検索でヒット率が多いもの

SQL Server 2000 Japanese_CI_AS Japanese_BIN でヒットしていますね。
(ヒットしたからと言って閲覧されているかどうかは不明ですが)

どういった意図を持って調べているのかはわかりませんが,少なくとも仕事上・業務上で情報を得るために検索しているのでしょう。

ということで,私が携わった案件でのトラブル回避の内容を含めて照合順序の事を書いてみます。

Japanese_CI_ASは,SQL Server をインストールするときの標準設定でして,大文字小文字を区別しない物です。
つまりは,「'a' = 'A'」。MS Access 等がこれですね。大文字小文字を区別してない。
一方,Japanese_BIN は大文字小文字を区別します。だって,aのバイナリコードとAのバイナリコードは違いますから,よって,「'a' <> 'A'」。ODBC接続を使い,Accessを使ってJapanese_BINのデータベースに接続すると,大文字小文字を区別しますという警告メッセージが表示されます。

どちらが親切か,便利かは一概には言えません。だってそれぞれのシステムで目的に応じた照合順序を指定して稼働しているのだから。

で,以前の私のブログに記述したのは,master等の,SQL Serverシステムが使っているデータベースの照合順序がJapanese_CI_ASで,ユーザー作成データベースがJapanese_BINで,こういった環境で,SELECT句に,Case文を用いて等号不等号を使って値を比較していると不具合が発生するとの事でした。
(環境セットアップ手順書には,しっかりと「Japanese_BIN」とするように明記されているので,開発環境を作った輩の責任です)
照合順序云々というエラーですけど,要約すると,「アルファベットの『A』とひらがなの『あ』は,どちらが偉いか」。という,決着のつかないエラーをしているわけです。
決着がつかない,つまりはどちらが偉いのか甲乙つけられない,比較できないとの事でエラーとなります。

使うデータベース毎にユーザーを切り分け,そのユーザーのデフォルトデータベースを割り当て,直接そのユーザーテーブルを見れば良いのですが,私が担当したシステムで使っているログインIDが「sa」。「sa」というIDは,SQLServerでの管理者として設定されている特殊なID。このIDのデフォルトデータベースがmasterとなっている。

プログラム側で作成するSQLは,テーブルまでフルパスが区切ってあります。
例) UserDB.dbo.T_UserList など。
直接指定しているように見えますが,これ実はいったんmasterテーブルを経由してからユーザーテーブルに行っているわけですね。なぜなら,接続する際にsaユーザーを使っていて,そのユーザーのデフォルトデータベースがmasterです。なので,masterを経由する時点で照合順序がJapanese_CI_ASとなってしまうのです。

一方,ユーザーテーブルの照合順序はJapanese_BIN。照合順序がシステムデータベースとユーザーデータベースで違っていても,単純な抽出なら問題無い事を確認済み。UpdateもInsertもDeleteも大丈夫。単純な処理ならですが。

ストアド・プロシージャを使った処理は怪しいかな。私が担当したシステムには,幸いにもストアド・プロシージャを使った処理がありませんでしたが,それを使っている他システムでは何らかの不具合が発生していると聞いています。

これを避けるため,一時的・暫定的な対応としてなのですが,照合順序をJapanese_BINとしたデータベースを作成(仮にJ_BINとする)しました。テーブルその他はシステムが自動生成するものだけ。

つまりは空の状態。そして,saユーザーのデフォルトデータベースを「J_BIN」と設定。こうすることで,上述してあるような等号不等号を使ったSELECT句でもエラーが回避された。
ただ,ただである。これはあくまでも暫定対応。データベース構築失敗したのなら,再構築するのが本来の正しい方法(当然バックアップは必須)。

SQL Serverの再インストールで,インストール時に照合順序を間違わなければ無問題。
SQL Serverをアンインストールせずに照合順序を変更できるのですが,既存のユーザーテーブルが消える(正しくはリンクが消える)し,変更するにはインストールディスクが必要になるので,上述したけど再インストールの方が,余計な手順や余計な事を考えずに済むのではるかに楽です。

SQL Server 2000 の照合順序1

今の開発環境には,SQL Server 2000 が4台あります。
とあるSQLを実行したのですが,4台中1台のみSQL実行エラーとなる。

何故だ!何故なんだ!SQLは同じだし,テーブル構造も同じ。なのにたった1台だけエラーとなってしまう。

そのエラーとなるサーバ。
テーブルを参照する際に,「hogehoge.dbo.hogeTBL」とフルパスを設定している。
クエリアナライザで,データベースを「hogehoge」と指定してSQLを実行するとエラーとはならないが,「master」と指定してSQLを実行するとエラーとなる。

原因を探ると,どうやらそれはSQL Serverの「サーバー照合順序」が関係するのではないかとの結論に達した。

4台のうち3台は「Japanese_BIN」,残りの1台は「Japanese_CI_AS」。

その1台の「master」のサーバー照合順序は「Japanese_CI_AS」,「hogehoge」のサーバー照合順序は「Japanese_BIN」。
つまりは,直接データベース「hogehoge」を指定すれば「Japanese_BIN」となるが,「master」のままだと「Japanese_CI_AS」→「Japanese_BIN」となるのであqsws@@dそいれあr@じゃせふじこ

まて,今まで他の案件や改修で,こういった状態でテストしてきたんだよね。何故不具合が出なかったの?

今回,たまたまSQLのSELECT句内に不等号を使う式があり,それでエラーとなったわけだが,つまりは今までそういった処理が無かったってことなんだね。

本番環境が「Japanese_BIN」なら今回の不具合は発生しない。
本番環境をまだ確認したわけではないが,仮に「Japanese_CI_AS」だったら事態は深刻。上長に相談しなければならない。
→本番環境は「Japanese_BIN」でした。一安心。

データベース「master」の再構築をすれば良いようだが,ユーザ情報等が消えるようだ。
と,他の方々も使っているこのサーバー。使えなくなることは避けたい。
ということで,とりあえずその場しのぎではあるけど回避方法。
Japanese_BINのデータベースを新規作成し,通常ログインするユーザーのデフォルトデータベースをその新規作成したデータベースとする。
こうすることで,Japanese_BIN→Japanese_BINと照合順序は変更されないので無問題で処理される・・・・・・。と思う。

担当の方には,「あくまでもその場しのぎの方法。根本的な解決ではない。」事を伝えておいた。
今の作業が落ち着いたら,サーバー再構築だな。OSから。
Amazon
Profile

ぴろたん

Monthly Archives
livedoor 天気
Access Counter
  • 今日:
  • 昨日:
  • 累計:

訪問ありがとうございます。

ASPアクセス解析
  • ライブドアブログ