SQLServer からリンクサーバーでつないだOracle に OpenQuery を発行 その２

SQLServer からリンクサーバーで ORACLE につなぐのは、結構みなさんやりたいようですね。

以前に書いた「SQLServer からリンクサーバーでつないだOracle に OpenQuery を発行」に結構アクセスをいただいています。

この時の例では、条件に数値を入れていますのであまり問題ないのですが、文字列を条件に入れる場合、クォーテーション「'」の数で悩むときがあります。

条件が数値の場合は、
SELECT * FROM OPENQUERY(OraLink, 'SELECT * FROM hoge Where ID = 1')
これでいいんですけど、

条件が文字の場合は、
SELECT * FROM OPENQUERY(OraLink,
               'SELECT * FROM hoge Where name like  ''hagehage%'' ')

ということになります。
SQLServer がクォーテーテョンを２個並べると１個分と判断するので、
実際にORACLE　に投げられるＳＱＬ文は、
SELECT * FROM hoge Where name like  'hagehage%'
ということになります。

全角の濁点

今回ははまりました。

全角で「ゴ」などの濁る音は、１文字ですが、半角だど「コ」と「゛」の２文字です。
（ここでは、すべて全角で書いています。見づらいので、以下「゛」を「濁」とします。）

ところが、全角なのに「コ」と「濁」との２文字で入力してあるものが混じっていました。
多分これは昔に半角で入力してあったものを、関数等で全角に無理やり移植した残骸だと思います。

この文字列から、「濁」だけを除去したい、というか、
数字が混在している文字列から数字だけを抜き出す処理を作っていたので、「濁」も普通に除去できていると思っていましたが、「濁」が混じると、除去できてませんでした。

Select @aaa = Replace('１２３コ濁ウ','濁','')

普通に書いたこれでは除去できないいんです。

前から１文字ずつ取得して、文字列の長さ回数分ループで回して、

０～９以外が来れば除去するという処理を書いても除去できません。

（'１２３コ濁ウ'をループさせた結果は「１２３コ濁」が返りました。）

この「濁」を照合するためには照合順序を変更しなければなりません。

Select @aaa = Replace('１２３コ゛ウ','濁','' COLLATE JAPANESE_BIN)

「COLLATE JAPANESE_BIN」でバイナリーで照合をしてくれるので、除去できます。

因みに指定しない場合（デフォルト）は

「Japanese_CI_AS」

です。

これは、大文字小文字、かな、全角半角を区別しないという意味です。

SQLServer での Truncate Table 使用上の注意

SQLServer でテーブルの構造を残して、データだけ削除する際には、
Delete From T_hogehoge
ですが、
TRUNCATE TABLE T_hogehoge
のほうが圧倒的に高速に削除できます。

今まで、これを多用していました。

別途、Index付ビュー という、これも高速化するためのもので、
通常のビューはIndexをつけることができませんが、
ちょっと細工をすることで、Index付ビューにすることができて、
そのビューに対する検索が高速になります。

ところが、困ったことが判明しました。

「TRUNCATE TABLE は Index付ビューで参照しているテーブルに対して実行でき
ない。」

これを知らずに、Index付ビューを設定する前に書いたストアドを流していまし
たが、
途中で、Index付ビューを作ったために、その後の処理が全部落ちてました。

しかも、自動実行的なストアドだったので、実行されているように見えてて、
ここ半年の処理が・・・(T-T)

他のユーザーが同じデータに対して同時に変更を試みているので、プロセスが停止しました。？？？

SQLServer にAccess でリンクを張っている場合で、特定の条件にあてはまると、
「他のユーザーが同じデータに対して同時に変更を試みているので、プロセスが停止しました。」というエラーが表示され、更新ができなくなります。

これは、SQLServer のテーブルに「Bit」型のフィールドがあって、そのフィールドに「Null」が入力されてしまった場合に発生します。

SQLServer の「Bit」型フィールドに、Access からリンクを張ると、「Yes/No」型として扱われます。

Access では「Yes/No」型にはNullがあり得ますが、SQLServer 側の「Bit」型では、Null許容にしていても、Nullは許されません。

なので、Access から追加クエリー等でBit型のフィールドにNullを送ってしまうと、そのレコードはもうAccessから編集できなくなって、表題のエラーが表示されます。

困ったことに、このエラー表示を見て、この現象を思いつくことが難しいです。
なので、同じ過ちをしても、なかなかすぐにこれを思い出すことができず、ネットを彷徨うことになります。

解決は簡単で、Bit型のフィールドを「0」か「1」のどちらかで埋めれば以後編集することができるようになります。

SQLServer のリンクサーバー上のテーブルリストを取得

SQLServer で Oracle へのリンクサーバーを作れば、SSMS を使って、比較的簡単にOracle 上のデータにアクセスできるようになります。

ただ、リンクサーバー上にたくさんのテーブルがある場合、そのすべてのテーブルに接続するビューをちまちまと手作業で作るのは、結構邪魔くさい。
そうなると、Oracle 上のテーブルやビューの一覧が欲しくなります。

もちろん、リンクサーバーを見れば一覧は見れますが、一覧データとして扱いたいです。

そこで、SQLServer 上のストアドとして、「SP_Tables_EX」があります。

使用例（リンクサーバー名を「hoge」とする。）
テーブル一覧の取得
EXEC SP_Tables_ex @table_server = 'hoge',@table_type = 'table'

ビュー一覧の取得
EXEC SP_Tables_ex @table_server = 'hoge',@table_type = 'View'



これで一覧を取得すれば、リンクサーバー上のすべてのテーブルに接続するビューなどを作るスクリプトを書くことができます。

Windows server 2008 r2 上で動作する 32ビット版 Access から ORACLE ODBC を使いたい

64ビット版のＯＳを使うと色々不具合があるもんですね。

今回は、Windows 2008 r2 （「マシンＡ」と呼びます。）上で動作している 32ビット版の　Access を使って、別のサーバー（「マシンＢ］と呼びます。）にある Oracle に接続したい。

このマシンＡには、さらに別のサーバー（「マシンＣ」と呼びます。）で動作する Oracle ベースのシステムにＶＢ系で作成したアプリからアクセスするシステムが動作しており、64ビットのOracle Client がインストールされています。

この環境で、マシンＡ上で動作している Access を使って、マシンＢの Oracle に接続しないといけなくなり、普通に Access からODBC接続をしようとしたら、ドライバーが見つかりません。

マシンＡの管理ツールから ODBCad32.exe を起動したら、ちゃんと使えるようになってます。
ただ、これは、64ビット版なんですね。

syswow64下にある ODBCad32.exe を起動すると、当たり前と言えば当たり前ですが、ドライバーはありません。

前回、マシンA上で動作するSQLServer 64 ビットから Access のMDBファイルにアクセスするための、ODBCドライバーは、64ビット版と 32ビット版の共存ができましたが、
今回はほぼお手上げです。

Access を 32ビット版ではなく、64ビット版にすれば、全て解消かな？

SQLServer からリンクサーバーでつないだOracle に OpenQuery を発行

2015/04/18一部修正

SQLServer からリンクサーバーを使ってOracle に接続すると、データのやり取りが簡単になる上に、SSMSを使って、クエリを作成したり、ストアドを作ったりするのが楽になります。

単に、私がオラクルが苦手なだけなんですけど。

しかし、普通につないで、そのオブジェクトに対して、クエリを発行すると、インデックスが効かないので異常に遅いです。

そこで、アクセスでいうところの、パススルーのような方法があります。

SQLServer では OpenQuery というそうです。

SELECT * FROM OPENQUERY(OraLink, 'SELECT * FROM hoge Where ID = 1')

緑字の部分がリンクサーバー名で、赤字の部分がオラクル用のSQL文です。

赤字の部分が、直接オラクル側で評価されますので、非常に高速です。