SQL Server 2005からリンクテーブルでOracleに接続する

SQL Serverにリンクサーバーを設定することで，SQL ServerからOralceにアクセスできるようになります。その手順をまとめてみました。SQL Server は2005です。

サーバーにOracleのクライアントをインストールする

SQL Serverが稼働しているサーバーにOracleのクライアントをインストールします。インストール後SQLサーバーを再起動します。

リンクサーバーの作成

1. SQL Server上にリンクテーブルを作成します。SQL Server Management Studio(以下SSMS)を開きます。
   
2. サーバーオブジェクトのリンクサーバーを右クリックして，コンテキストメニューの新しいリンクサーバーを実行します。
   

全般ページは次のように設定します。

リンクサーバー

SQL Server側から指定する際の別名です。

サーバーの種類

その他のデータソースを指定します。

プロバイダ

Microsoft OLE DB Provider for Oracleを指定しました。私の環境ではオラクルが提供するOracle Provider for OLE DBを使うとうまく動作しないみたいでした。

次にセキュリティページ

「このセキュリティコンテキストを使用する」を選択して，リモートログインとパスワードを入力します。OKボタンをクリックするとリンクサーバーが作成されます。

リンクサーバーのテーブルを見る

リンクサーバーのテーブルをSELECT文で指定する時には，テーブル名を

<リンクサーバー名>.<カタログ>.<スキーマ>.<オブジェクト名>

のように指定しなければなりません。カタログは指定する必要がないので，次のようになります。

TEST..SCHEMA_A.TABLE

上記は，TESTというリンクサーバーのSCHEMA_AスキーマにあるTABLEテーブルを指定する構文です。この方法で次のようにSELECT文を記述します。

SELECT
  FIELD1,
  FIELD2,
  FIELD3
FROM
  TEST..SCHEMA_A.TABLE

注意すべき点は，オブジェクトにOracleのシノニムを指定しても取れないことです。本来はデータを取ってこられるはずですが，リンクテーブルからの場合は，Oracleで定義されたシノニム名ではなく本来のテーブル名で取ってくる必要があるようです。オブジェクトにビューを指定することは可能です。しかし処理速度が遅いように思います。

 シノニムの作成

TEST..SCHEMA_A.TABLE　という表記方法はとても長ったらしいと感じます。そこで，SQL Server上でシノニムを作成することができます。

CREATE SYNONYM ORA_TABLE FOR TEST..SCHEMA_A.TABLE

とすると，それ以降そのデータベースではORA_TABLEという名前でリンクテーブルを利用できます。
シノニムを作成するともう一つ良いことがあります。それはA5:SQLにて入力補完機能を利用できるようになることです。リンクテーブルは入力補完の対象ではありませんが，シノニムは対象となりますので，A5:SQLでの開発がとてもやりやすくなります。A5:SQLのVersion 2.3以降のクエリデザイナでも利用できます。

Access2003のADPプロジェクトで参照する

リンクしたOracleのテーブルやシノニムはAccess2003のデータベースウィンドウには表示されません。これらをAccessのADPプロジェクトで参照するには，ビューを作ってやりましょう。

CREATE VIEW viwOraTalbe
AS
SELECT
  FIELD1,
  FIELD2,
  FIELD3
FROM
  TEST..SCHEMA_A.TABLE

このようにビューを作ってしまえば，ADPプロジェクトのデータベースウィンドウのクエリの画面に表示されるようになり，Accessからも簡単に利用できます。