適用対象:
SQL Server
Important
この機能は、将来のバージョンの SQL Server で削除される予定です。 新しい開発作業ではこの機能を使用しないでください。現在この機能を使用しているアプリケーションを変更することを計画してください。 代わりに CLR統合 を使いましょう。
拡張ストアドプロシージャの仕組み
拡張ストアドプロシージャが動作するプロセスは以下の通りです:
クライアントが拡張ストアドプロシージャを実行すると、リクエストはクライアントアプリケーションからSQL Serverへ表形式のデータストリーム(TDS)またはシンプルオブジェクトアクセスプロトコル(SOAP)形式で送信されます。
SQL Serverは拡張ストアドプロシージャに関連付けられたDLLを検索し、まだ読み込まれていなければそのDLLを読み込みます。
SQL Serverは要求された拡張ストアドプロシージャ(DLL内で実装された関数として実装)を呼び出します。
拡張ストアドプロシージャは、拡張ストアドプロシージャAPIを通じて結果セットとパラメータをサーバーに返します。
かつては、Open Data Servicesは非SQL Serverデータベース環境へのゲートウェイなどサーバーアプリケーションの作成に使われていました。 SQL ServerはOpen Data Services APIの旧バージョンをサポートしていません。 元のOpen Data Services APIでSQL Serverでサポートされているのは拡張ストアドプロシージャ関数のみであり、そのためAPIは拡張ストアドプロシージャAPIに改名されました。
分散クエリとCLR統合の登場により、拡張ストアドプロシージャAPIアプリケーションの必要性はほぼ失われました。
既存のゲートウェイアプリケーションがある場合、SQL Serverに付属する opends60.dll を使ってアプリケーションを実行することはできません。 ゲートウェイアプリケーションはもはやサポートされていません。
拡張ストアドプロシージャとCLR統合の比較
CLR統合は、Transact-SQLで表現が困難または不可能だったサーバーサイドのロジックを書くよりも、より堅牢な代替手段を提供します。 SQL Serverの初期リリースでは、拡張ストアドプロシージャ(XP)がデータベースアプリケーション開発者がそのようなコードを書く唯一の手段を提供していました。
CLR統合により、かつてストアドプロシージャの形で書かれていたロジックは、FROM節に埋め込むことでSELECT文でクエリされるテーブルバリュー関数として表現されることが多いです。
詳細については、 CLR統合の概要をご覧ください。
拡張ストアドプロシージャの実行特性
拡張ストアドプロシージャの実行には以下の特徴があります:
拡張ストアドプロシージャ関数はSQL Serverのセキュリティコンテキストの下で実行されます。
拡張ストアドプロシージャ関数はSQL Serverのプロセス空間で動作します。
拡張ストアドプロシージャの実行に関連付けられるスレッドは、クライアント接続で使用されるスレッドと同じです。
Important
拡張ストアドプロシージャを追加し、他のユーザーに実行権限を付与する前に、システム管理者は各拡張ストアドプロシージャに有害または悪意のあるコードが含まれていないかを徹底的に確認する必要があります。
拡張ストアドプロシージャのDLLがロードされた後も、SQL Serverが停止するか、管理者が DBCC <DLL_name> (FREE)を使って明示的にDLLをアンロードするまで、DLLはサーバーのアドレス空間にロードされたままです。
拡張ストアドプロシージャは、Transact-SQL から EXECUTE 文を用いてストアドプロシージャとして実行できます。
EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT;
パラメーター
@ レトヴァル
リターンバリューです。
@ パラム1
入力パラメーター。
@ パラム2
入出力パラメータです。
注意事項
拡張ストアドプロシージャはパフォーマンス向上とSQL Serverの機能拡張を提供します。 しかし、拡張ストアドプロシージャであるDLLとSQL Serverは同じアドレス空間を共有するため、問題のあるプロシージャはSQL Serverの機能に悪影響を及ぼすことがあります。 拡張ストアドプロシージャDLLによる例外はSQL Serverで処理されますが、SQL Serverのデータ領域を損傷させる可能性があります。 セキュリティ上の念頭として、拡張ストアドプロシージャをSQL Serverに追加できるのはSQL Serverのシステム管理者のみです。 これらの手順は設置前に十分にテストされるべきです。
拡張ストアドプロシージャAPIで結果セットをサーバーに送信します
結果セットをSQL Serverに送信する際、拡張ストアドプロシージャは以下の通りに適切なAPIを呼び出すべきです。
srv_sendmsg関数は、すべての行(もし行があれば)がsrv_sendrowを持つ前後の順序で呼び出すことができます。 完了状態がsrv_senddoneで送信される前に、すべてのメッセージはクライアントに送信されなければなりません。srv_sendrow関数はクライアントに送信される各行ごとに1回呼び出されます。 すべての行は、srv_sendmsg、srv_pfieldのsrv_status引数、またはsrv_senddoneでメッセージ、ステータス値、完了ステータスを送信する前にクライアントに送信しなければなりません。すべての列が
srv_describeで定義されていない行を送信すると、アプリケーションは情報エラーメッセージを出し、クライアントにFAILを返します。 この場合、その行は送信されません。
拡張ストアドプロシージャの作成
拡張ストアドプロシージャとは、プロトタイプを持つC/C++関数のことです:
SRVRETCODE xp_extendedProcName (SRVPROC *);
接頭辞 xp_ の使用は任意です。 拡張ストアドプロシージャ名は、サーバーにインストールされているコードページやソート順に関係なく、Transact-SQL 文で参照される際に大文字小文字を区別します。 DLLを作るとき:
エントリーポイントが必要な場合は、
DllMain関数を書きます。この関数は省略可能です。 ソースコードで提供しなければ、コンパイラは独自のバージョンをリンクし、返すだけで何も
TRUEしません。DllMain関数を提供すると、スレッドやプロセスがDLLに接続または切り離されたときにオペレーティングシステムがこの関数を呼び出します。DLLの外部から呼び出されるすべての関数(拡張ストアドプロシージャのE関数)はエクスポートしなければなりません。
関数名を
.defファイルのEXPORTSセクションに記載することでエクスポートできますし、ソースコードの関数名の前にMicrosoftコンパイラ拡張子__declspec()__declspec(dllexport)(2つの下線で始まる)を付けて書き出すこともできます。
これらのファイルは拡張ストアドプロシージャDLLを作成するために必要です。
| File | Description |
|---|---|
srv.h |
拡張ストアドプロシージャAPIヘッダーファイル |
opends60.lib |
インポートライブラリ opends60.dll |
拡張ストアドプロシージャDLLを作成するには、Dynamic Link Libraryというタイプのプロジェクトを作成します。 DLLの作成に関する詳細は、開発環境のドキュメントをご覧ください。
すべての拡張ストアドプロシージャDLLは、以下の関数を実装しエクスポートする必要があります:
__declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}
__declspec(dllexport) はMicrosoft専用のコンパイラ拡張です。 もしコンパイラがこの指令をサポートしていない場合は、 DEF ファイルの EXPORTS セクションでこの関数をエクスポートしてください。
SQL Serverがトレースフラグ -T260 で起動される場合や、システム管理者権限を持つユーザーが DBCC TRACEON (260)実行し、拡張ストアドプロシージャのDLLが __GetXpVersion()をサポートしていない場合、以下の警告メッセージがエラーログに印刷されます(__GetXpVersion() は2つのアンダースコアで始まります)。
Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().
拡張ストアドプロシージャDLLが __GetXpVersion()をエクスポートしても、関数が返すバージョンがサーバーが要求するバージョンより小さい場合、関数が返すバージョンとサーバーが期待するバージョンを示す警告メッセージがエラーログに印刷されます。 このメッセージが表示される場合、 __GetXpVersion()からの誤った値を返しているか、古いバージョンの srv.hでコンパイルしている可能性があります。
注
SetErrorMode、Win32関数は拡張ストアドプロシージャで呼び出されるべきではありません。
長時間実行される拡張ストアドプロシージャは、接続が終了したりバッチが中止された際にプロシージャが自ら終了できるように srv_got_attention 定期的に呼び出すべきです。
拡張ストアドプロシージャDLLをデバッグするには、SQL Serverの \Binn ディレクトリにコピーしてください。 デバッグセッションの実行ファイルを指定するには、SQL Server実行ファイルのパスとファイル名(例: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe)を入力します。
sqlservr引数の詳細については、sqlservrアプリケーションを参照してください。
SQL Serverに拡張ストアドプロシージャを追加する
拡張ストアドプロシージャ関数を含むDLLは、SQL Serverの拡張として機能します。 DLLをインストールするには、標準のSQL Server DLLファイル(デフォルトでC:\Program Files\Microsoft SQL Server\MSSQL16.0.<x>\MSSQL\Binn )を含むディレクトリなどにファイルをコピーしてください。
拡張ストアドプロシージャのDLLがサーバーにコピーされた後、SQL Serverのシステム管理者はDLL内の拡張ストアドプロシージャ関数をSQL Serverに登録しなければなりません。 これは sp_addextendedproc システムのストアドプロシージャを使って行われます。
Important
システム管理者は拡張ストアドプロシージャを徹底的に確認し、有害または悪意のあるコードが含まれていないかを確認してから、サーバーに追加し他のユーザーに実行権限を付与する必要があります。 すべてのユーザー入力を検証します。 ユーザーの入力を検証する前に連結しないでください。 検証していないユーザー入力から作成されたコマンドは、絶対に実行しないでください。
sp_addextendedprocの最初のパラメータは関数の名前を指定し、2番目のパラメータはその関数が存在するDLLの名前を指定します。 DLLの完全な経路を指定するべきです。
注
完全なパスに登録されていない既存のDLLは、SQL Server 2005(9.x)以降のバージョンにアップグレードした後は動作しません。 問題を修正するには、 sp_dropextendedproc を使ってDLLを登録解除し、その後完全なパスを指定する sp_addextendedproc, で再登録します。
sp_addextendedprocで指定された関数名は、場合を含めてDLL内の関数名と全く同じでなければなりません。 例えば、このコマンドはxp_hello.dllという名前のdllにある関数xp_hello,SQL Server拡張ストアドプロシージャとして登録します。
sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL13.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';
sp_addextendedprocで指定された関数名がDLLの関数名と完全に一致しない場合、新しい名前はSQL Serverに登録されますが、その名前は使用できません。 例えば、 xp_Hello は xp_hello.dllにあるSQL Server拡張ストアドプロシージャとして登録されていますが、後で xp_Hello を使って関数を呼び出すと、SQL ServerはDLL内の関数を見つけられません。
-- Register the function (xp_hello) with an initial upper case
sp_addextendedproc 'xp_Hello', 'c:\xp_hello.dll';
-- Use the newly registered name to call the function
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;
エラー メッセージを次に示します。
Server: Msg 17750, Level 16, State 1, Procedure xp_Hello, Line 1
Could not load the DLL xp_hello.dll, or one of the DLLs it references. Reason: 127(The specified procedure could not be found.).
sp_addextendedprocで指定された関数名がDLLの関数名と正確に一致し、SQL Serverインスタンスの照合が大文字を区別しない場合、ユーザーは名前の小文字と大文字の組み合わせを使って拡張ストアドプロシージャを呼び出すことができます。
-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';
-- The following example succeeds in calling xp_hello
DECLARE @txt VARCHAR(33);
EXEC xp_Hello @txt OUTPUT;
DECLARE @txt VARCHAR(33);
EXEC xp_HelLO @txt OUTPUT;
DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;
SQL Serverインスタンスの照合が大文字を区別する場合、異なるケースで呼び出される拡張ストアドプロシージャはSQL Serverは呼び出せません。 これは、DLLの関数と全く同じ名前とコレーションで登録されていても同じです。
-- Register the function (xp_hello)
sp_addextendedproc 'xp_hello', 'c:\xp_hello.dll';
-- The following example results in an error
DECLARE @txt VARCHAR(33);
EXEC xp_HELLO @txt OUTPUT;
エラー メッセージを次に示します。
Server: Msg 2812, Level 16, State 62, Line 1
SQL Serverを停止して再起動する必要はありません。
SQL Serverにインストールされた拡張ストアドプロシージャのクエリ
SQL Server認証済みユーザーは、 sp_helpextendedproc システム手続きを実行することで、現在定義されている拡張ストアドプロシージャおよびそれぞれが属するDLL名を表示できます。 例えば、以下の例は xp_hello が属するDLLを返します。
sp_helpextendedproc 'xp_hello';
sp_helpextendedprocが拡張ストアドプロシージャを指定せずに実行された場合、すべての拡張ストアドプロシージャとそのDLLが表示されます。
SQL Serverから拡張ストアドプロシージャを削除する
ユーザー定義の拡張ストアドプロシージャDLLで各拡張ストアドプロシージャ関数を破棄するには、SQL Serverシステム管理者が sp_dropextendedproc システムストアドプロシージャを実行し、関数名とその関数が存在するDLL名を指定します。 例えば、このコマンドはSQL Serverのxp_hello.dll,というDLLにある関数xp_helloを削除します。
sp_dropextendedproc 'xp_hello';
sp_dropextendedproc システム拡張ストアドプロシージャをドロップしません。 代わりに、システム管理者は拡張ストアドプロシージャのパブリックロールへの許可をEXECUTE拒否すべきです。
拡張ストアドプロシージャのDLLをアンロードします
SQL ServerはDLLの関数のいずれかに呼び出しがなされると、拡張ストアドプロシージャのDLLを読み込みます。 DLLはサーバーがシャットダウンされるか、システム管理者が DBCC 文を使ってDLLをアンロードするまで読み込まれたままです。 例えば、このコマンドは xp_hello.dllをアンロードし、システム管理者がサーバーをシャットダウンせずに新しいバージョンのファイルをディレクトリにコピーできるようにします。
DBCC xp_hello(FREE);