こんにちは。CData リードエンジニアの杉本です。
今日はお客様から問い合わせがあったリンクサーバー上経由でのストアドプロシージャ実行方法を紹介します。
なお、リンクサーバー・リンクサーバーとCData ODBC Driverを接続するために使用するCData SQL Gatewayに関しては、以下の記事を参照してみてください。
背景
リンクサーバーはざっくり説明すると、SQL Serverインターフェースをベースにしながら、他のOLE DB経由で取得したデータソースに対してクエリすることができる、仮想DB機能のことです。
このリンクサーバーとCData ODBC Driverを組み合わせることで、SalesforceやKintoneのデータをSQL Serverのデータベースのように扱えるようになり、ローカルのDBとのJOIN結果や、データ取り込み・出力が可能になります。
しかしながら、接続したことがある方であればご存知かと思いますが、以下のようにリンクサーバーではストアドプロシージャに関するスキーマ情報が取得されず、そのまま実行することができません。
CData ODBC Driverでは、各種サービススペシフィックな機能、添付ファイルのダウンロードやCSV出力機能などをストアドプロシージャで実装していることもあり、この機能を呼び出すことが必須ということが少なくありません。
もちろん、ODBC DirectにPowreShellなどで実行してしまうことも一つの手段ですが、リンクサーバーの特徴である複数データソースの統合をうまく活かせなくなってしまいます。
解決策
そんなストアドプロシージャですが、少し裏技的なアプローチで解決が可能です。
それは、OPEN QUERYという、パススルークエリを実行するための構文でストアドプロシージャを渡すという方法です。
通常はINSERTやUPDATEなどの実行結果を参照するためのものですが、これにストアドプロシージャを渡すことで
例えば Salesforce ODBC Driver で提供されている、添付ファイルをダウンロードするためのストアドプロシージャである「Download Attachment」を
以下のように実行できます。
SELECT * FROM OPENQUERY([LINK SERVER NAME],'Exec DownloadAttachment ObjectId = "0012800001D4lPRAAZ", LocalPath = "C:\\Testdata\\Salesforce"')
すると、ストアドプロシージャがODBC Driverにパススルーされ、Download Attachmentが実行、ファイルが正常にダウンロードされました。
もちろん、UploadAttachmentなどのストアドプロシージャも同様に実行可能です。
是非お試しください。
注意点
上記デモ環境ではローカルホストにSQL Serverを入れて、SSMSからはローカル接続しているのでシンプルなのですが、もしサーバー上のSQL ServerにSSMSで接続している場合は少し注意が必要です。
今回のストアドプロシージャのようにフォルダパスを渡して実行した場合、実際にこの処理が行われるのはサーバー側のSQL Serverであり、サーバーのフォルダパスでアクセスを行うようになります。
そのため、ローカルマシンのフォルダパスを指定してもファイルのダウンロードは行なえません。
もし、ローカルからもアクセスできるような場所にファイルをダウンロードしたい場合は、別途ローカルからもサーバーからもアクセス可能なネットワークで共有されているフォルダパスを参照する必要があります。
そういった点に注意しながら、是非色々と試してもらえればと思います。