OracleからSqlServerへ接続する

OracleからSqlServerへ接続する方法をまとめ。基本的にはODBCデータソースとDBLinkを使用する。イメージ的には、 SqlServer -> ODBCデータソース -> DBLink -> Oracle という形になる。

１．ODBCデータソースの登録
コントロールパネル > 管理ツール > データ ソース (ODBC) を選択。
ここでデータソースの追加ボタンを押し、SqlServerかSqlServerNativeClientのどちらかでデータソースを作成する。
接続するサーバーは サーバー名(IPアドレス)\インスタンス名 が基本だが、サーバー名だけでも良いようだ。SqlServerでインスタンスを作成すると自動的に作成されるようなので、SqlServerを立てている側のサーバーのデータソース設定を見ると正確。

他のログイン設定は、適宜設定。


２．Oracle Database Gatewayの登録
ORACLE_HOME\hs\admin　のフォルダにDatabaseGatewayの設定ファイルがある。
ファイル名は init<SID>.ora とする。SIDは任意の名称。dg4・・・とするのが慣例？のようだ(dgはDatabaseGatewayの略と思われる)。

内容は以下の通り。

HS_FDS_CONNECT_INFO = <odbc data_source_name>
HS_FDS_TRACE_LEVEL = <trace_level>

<odbc data_source_name>は先ほど作成したデータソース名、<trace_level>はOFFかONを設定。ONにしておくとoracle_home:[tg4rdb.log].にログを書き出してくれるので、ONにしておくのを推奨。


３．リスナーへの登録
OracleのリスナーにGatewayを登録する。ORACLE_HOME\network\admin にあるlistener.oraにGateway用の以下の記述を追記。

SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC= 
         (SID_NAME=gateway_sid)
         (ORACLE_HOME=oracle_home_directory)
         (PROGRAM=dg4odbc)
      )
   )

gateway_sidは２で設定したSID(ファイル名の一部)。oracle_home_directoryはoracle_homeの値となる。

この設定を終えたのち、リスナーの再起動を行う。
※一度リスナーの再起動に失敗したことがあった。プロセスを見てみるとODBCデータソースがリスナーが起動するのに必要なポートを占有していたようなので、これを切ったところうまく立ち上がった（普通に起きる出来事なのかは定かでない・・・）。


４．接続名の登録
listener.oraと同フォルダにあるtnsnames.oraで定義されているOracleの接続名に追記を行う。

SQL_SRV=
   (DESCRIPTION=
    (ADDRESS= (PROTOCOL=TCP)(HOST=odbc_data_source_host)(PORT=1521))
    (CONNECT_DATA=(SID=gateway_sid))
    (HS=OK)
)

ホスト名が、相手先のSqlServerのホストでなく、ODBCデータソースのホスト(要するに自サーバー)である点に注意。


５．DBLinkの登録
ここまでくればあと一歩である。
以下のコマンドでDBlinkを作成する。なお、作成にはCREATE DATABASE LINK関連の権限が必要なため注意（こちら参考）。

CREATE PUBLIC DATABASE LINK dblink_name CONNECT TO user_name IDENTIFIED BY password USING 'service_name';

サービス名は文字列としてシングルクォーテーションでくくらないといけない点に注意。PUBLICをつけるかどうかは場合によりけり。

これでDBLINKの作成が完了したので、以下のSQL文で動作確認を行う。

select 'X' from dual@dblink_name

これで出力が返ってくれば完成！である。


※一度相手先のSqlServerがとまっていたことがあり、このときOracle側のリスナーも起動が失敗した。再現実験ができていないのだが、もしかしたらリスナー起動時にGatewayの生死判定があり、相手先が止まっていた場合自分も起動しないという迷惑判定があるかもしれないので要注意。


＜参考＞
Configuring Oracle Database Gateway for ODBC

D Heterogeneous Services Initialization Parameters

SqlServerにSQLのみで画像(バイナリ)データを投入する

データの初期セットとしてＤＢに画像やファイルのデータを入れたいときの方法。Accessだかフォームだかのツールを使う例が多いが、正直そんなのやってられないのでＳＱＬだけで対処する方法です。

CREATE TABLE myTable(Id nvarchar(max), Document varbinary(max)) 
INSERT INTO myTable(Id,Document) 
SELECT 1,img.BulkColumn
FROM (SELECT * FROM OPENROWSET(BULK N'D:\temp\1.jpg',SINGLE_BLOB) AS i ) img


注意点としては、指定するパスはDBサーバー上のものでないといけない、という点(ローカルのパスを指定してもファイルがないといわれる)。

また、実行には一括処理の行える権限(sysadminかbuiladmin？)が必要。


参考
http://weblogs.sqlteam.com/peterl/archive/2007/09/26/Insert-binary-data-like-images-into-SQL-Server-without-front-end.aspx

SqlServer SELECT文からテーブルを作る ( テーブル作成クエリ )

SELECT文からテーブルを作成する機能が(Accessのテーブル作成クエリに該当)、なんとSqlServerにもある。
データをどこかしらから持ってきて、とりあえずテーブルに入れたい場合などに重宝すると思います。

SELECT 'JOBS'  AS NAME , 0.000 AS SARALY INTO EMP_TABLE


データをまじめに設定しておくとテーブル型も結構正確になります（上記の場合、数値は小数点以下桁数３桁がきっちり設定される）。

Oracle SqlServer 関数/データ型変換表

Oracle/Sqlserver分について、関数・データ型について変換表を作成してみました。これが必要なくなる日を祈る(一刻も早く業界団体を作成し統一求む・・・)。


各種DB方言変換表
※変換表の追加や他のDBへの変換の追記など歓迎です


参考サイト
Oracle SQLServer 関数対比表

VisualStudioからSqlServerへ接続する

開発機のクライアントにVisualStudioが入っており、DBサーバーにSQLServerが入っている、という環境でクライアントのVisualStudioからDBサーバー上のSQLServerデータベースへ接続する方法をまとめました。

・準備
・DBサーバーにpingが通るか確認する。
例えばDBサーバーが「DBSERVER01」であった場合そこにpingを打って通るかどうか確認しておく
・サービスが開始しているか確認する。
DBサーバー上でSQLServerのサービスが稼動しているか確認する

・サーバー側設定
①SqlServer構成の設定
SqlServer Configuration Manager(SqlServerxxxx　＞　構成ツールから起動)を起動し、SqlServerネットワークの構成　＞　(インスタンス名)のプロトコルで、TCP/IPが無効になっていれば有効にする(※なお、後述するが名前付きパイプはこの時点では無効にしておいたほうがいい)。
この際、TCP/IPで右クリック＞プロパティから、IPAllのセクションでTCPポート1433を設定しておく。

運がよければこれだけで接続可能になる。VisualStudioのサーバーエクスプローラーから、サーバー名、ユーザー/パスワード、データベース名を指定し、テスト接続でOＫが出ればとりあえず安心(サーバー側は・・・)

②WindowsFireWallの設定解除
うまくいかない場合、WindowsFireWallの設定を見る。有効になっている場合、SqlServer用のポート1433を開ける必要がある。
Windowsファイアウォールから例外のタブへ行き、ポートの追加から1433を登録する(名前は何でも良い)。追加した名前にチェックを入れ、再度トライ。
(可能なら再起動したほうが良いかもしれない)

③SQL Server Browserを起動する（別名の設定を使用している場合に必要？）
いろいろなサイトで起動させるべきと書いてあるが、必要なかった(2008R2では少なくとも)。そのものの内容を見ると、別名を使用している場合に必要なのではないか。。。という気がする。
名前付きパイプの設定を有効にする場合、セットで有効化するとよいと思われる(未検証)。ただ、この場合別名でアクセスしないとエラーになるので注意。

④データベースがリモートアクセス可能になっているか確認する
デフォルトチェックが入っているので、ここがオフになっていることは余りないが、、、
サーバーのプロパティ＞接続から「このサーバーへのリモートアクセスを許可する」にチェックが入っていることを確認する。


・クライアント側設定
基本的にサーバー側に接続できたらＯＫなことが多いが、ここでもヤマがあることがある。
Oracleなど他DBサーバー同様、クライアント用の設定がクライアントにインストールされている必要があるのだ。
具体的には下記参照。

Microsoft.SqlServer.Management.Sdk.Sfcエラー

なお、ダウンロード先のFeature Packのサイトは一見いつもの「ダウンロード」を押しても何もインストールされない(テキストだがhtmのようなファイルが落ちてくる)。ナメとんのか、という気持ちを抑えて下のほうに行くと各種パッケージをダウンロードできるようになっているのでご注意を。

必要なのは下記モジュール。SQLServer以外のものもしこたまあって検索するのが激しく面倒だが、以下テキストは実際のサイトの語尾から取ったので検索に使えると思う。


・Native Client
・SQL Server システム CLR 型
・共有管理オブジェクト


これで晴れてVisualStudioからアクセスできるようになったはずだ。

SqlServer データベースを作成する

SqlServerはインストールできた。で、その後データベースを作成するには・・・？の手順をまとめました。

・前提
以下の要件を満たすデータベースを作成します。
・データベース名はSQLTEST
・このデータベースへのアクセスに使用するユーザーはsql
・スキーマもsqlとする(sql.testtableといったようにオブジェクトを管理する)

・作成
①データベースを作成する
システム管理者権限(saユーザー)でデータベースSQLTESTを作成します。
これは新しいデータベースの作成で難なく可能

②ログインを作成する
セキュリティ＞ログインから、データベースアクセス用ユーザーsqlを作成する。

③スキーマを作成する
データベースSQLTESTで、セキュリティ＞スキーマからスキーマsqlを作成する。
※SQLServerでは、Oracleと異なりユーザーとスキーマはイコールではないので別々に作る必要がある(これが良いか悪いかは・・・)

④ユーザーを作成する
データベースSQLTESTで、セキュリティ＞ユーザーからユーザーsqlを作成する。
ここで、規定のスキーマに③で作成したスキーマを指定、ユーザーが所有するスキーマで③で作成したスキーマにチェック
※SQLServerでは、ログイン(認証情報)とデータベースユーザーは別なのだ。

⑤ユーザーに権限を付与する
①で作成したデータベース(SQLTEST)で右クリック、データベースのプロパティ>権限を選択し必要な権限を割り当てる。

⑥ログインの設定の見直し
ログインは、作ったままだと既定のユーザー/スキーマがdboになっている(ログインのプロパティ、ユーザーマッピングで確認)。
ここで、④で作成したユーザー(sql)を設定しておく。


以上で終了となる。なお、ここで作成したユーザーsqlはシステム管理者/データベース所有者ではないため、このユーザーでテーブルなどを作る時逐一メッセージが出る(権限を持っているかは関係ない)。

実際はsaユーザーでテーブルを作ればよいのだが、テーブルなどのオブジェクト作成もsqlで行う場合このメッセージはかなり邪魔になる。
そのため、こうした場合はsqlユーザーのデータベースロールのメンバーシップでdbownerを割り当てる(ただ、セキュリティ上言うまでもなくよろしくないので、この場合はデータベース開発用に使用するユーザーsqlに加え、システム側から使うselect用ユーザー(sqlselect)などを作成した方が良いだろう)