拡張ストアド プロシージャデータベース エンジンプログラミング

適用対象: SQL Server

拡張ストアド プロシージャのしくみ

拡張ストアド プロシージャが動作するプロセスは次のとおりです。

1. クライアントが拡張ストアド プロシージャを実行すると、要求は表形式のデータ ストリーム (TDS) または簡易オブジェクト アクセス プロトコル (SOAP) 形式でクライアント アプリケーションから SQL Server に送信されます。

2. SQL Server は、拡張ストアド プロシージャに関連付けられている DLL を検索し、まだ読み込まれていない場合は DLL を読み込みます。

3. SQL Server は、要求された拡張ストアド プロシージャを呼び出します (DLL 内の関数として実装されます)。

4. 拡張ストアド プロシージャは拡張ストアド プロシージャ API を介してサーバーに結果セットを渡し、パラメーターを返します。

以前は、SQL Server 以外のデータベース環境とのゲートウェイなど、サーバー アプリケーションの記述には Open Data Services が使用されていましたが、 SQL Server では、Open Data Services API の古い部分はサポートされていません。 SQL Server で引き続きサポートされている元の Open Data Services API の唯一の部分は拡張ストアド プロシージャ関数であるため、API の名前が拡張ストアド プロシージャ API に変更されました。

分散クエリと CLR 統合の登場により、拡張ストアド プロシージャ API アプリケーションのニーズは大きく置き換えられました。

既存のゲートウェイ アプリケーションがある場合は、SQL Server に付属する opends60.dll を使用してアプリケーションを実行することはできません。 ゲートウェイ アプリケーションはサポート対象外になりました。

拡張ストアド プロシージャと CLR 統合

CLR 統合を使用すると、Transact-SQL で表現するのが難しいか、書き込み不可能であったサーバー側ロジックを記述するより堅牢な代替手段が提供されます。 SQL Server の以前のリリースでは、拡張ストアド プロシージャ (XP) によって、データベース アプリケーション開発者がこのようなコードを記述できる唯一のメカニズムが提供されていました。

CLR 統合では、ストアド プロシージャの形式で記述されていたロジックは、多くの場合、テーブル値関数として表現される方が適切です。これにより、関数によって構築された結果を FROM 句に埋め込むことで、SELECTステートメントでクエリを実行できます。

詳細については、 CLR 統合の概要を参照してください。

拡張ストアド プロシージャの実行特性

拡張ストアド プロシージャの実行には次の 3 つの特性があります。

• 拡張ストアド プロシージャ関数は、SQL Server のセキュリティ コンテキストで実行されます。

• 拡張ストアド プロシージャ関数は、SQL Server のプロセス空間で実行されます。

• 拡張ストアド プロシージャの実行に関連付けられているスレッドは、クライアント接続に使用するスレッドと同じです。

拡張ストアド プロシージャ DLL が読み込まれた後、SQL Server が停止するか、管理者が DBCC <DLL_name> (FREE) を使用して DLL を明示的にアンロードするまで、DLL はサーバーのアドレス空間に読み込まれたままになります。

拡張ストアド プロシージャは、 EXECUTE ステートメントを使用して、Transact-SQL からストアド プロシージャとして実行できます。

EXECUTE @retval = xp_extendedProcName @param1, @param2 OUTPUT;

パラメーター

@ retval

戻り値。

@ param1

入力パラメーター。

@ param2

入力/出力パラメーター。

拡張ストアド プロシージャ 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 からデタッチするときに、この関数を呼び出します。

• DLL 外部から呼び出す関数 (拡張ストアド プロシージャ関数) はすべて、エクスポートする必要があります。

  .def ファイルの EXPORTS セクションにその名前を一覧表示することで関数をエクスポートできます。または、ソース コード内の関数名の前に、Microsoft コンパイラ拡張機能である __declspec(dllexport) (2 つのアンダースコアで始まる__declspec()) を付けることができます。

拡張ストアド プロシージャ DLL を作成するには、次のファイルが必要です。

拡張ストアド プロシージャ DLL を作成するには、ダイナミック リンク ライブラリ プロジェクトを作成します。 DLL の作成に関する詳細については、開発環境のドキュメントを参照してください。

拡張ストアド プロシージャ DLL はすべて、次の関数を実装してエクスポートする必要があります。

__declspec(dllexport) ULONG __GetXpVersion()
{
   return ODS_VERSION;
}

__declspec(dllexport) は Microsoft 固有のコンパイラ拡張機能です。 コンパイラがこのディレクティブをサポートしていない場合は、この関数を EXPORTS セクションのDEF ファイルにエクスポートする必要があります。

トレース フラグ -T260 で SQL Server を起動した場合、またはシステム管理者特権を持つユーザーが DBCC TRACEON (260)実行され、拡張ストアド プロシージャ DLL が __GetXpVersion()をサポートしていない場合は、次の警告メッセージがエラー ログに出力されます (__GetXpVersion() は 2 つのアンダースコアで始まります)。

Error 8131: Extended stored procedure DLL '%' does not export __GetXpVersion().

拡張ストアド プロシージャ DLL が __GetXpVersion()エクスポートするが、関数によって返されるバージョンがサーバーで必要なバージョンより小さい場合は、関数によって返されたバージョンとサーバーで予期されるバージョンを示す警告メッセージがエラー ログに出力されます。 このメッセージが表示された場合、 __GetXpVersion()から正しくない値が返されるか、古いバージョンの srv.hでコンパイルされています。

実行時間の長い拡張ストアド プロシージャは、接続が強制終了された場合、またはバッチが中止された場合にプロシージャ自体を終了できるように、 srv_got_attention を定期的に呼び出す必要があります。

拡張ストアド プロシージャ DLL をデバッグするには、それを SQL Server \Binn ディレクトリにコピーします。 デバッグ セッションの実行可能ファイルを指定するには、SQL Server 実行可能ファイルのパスとファイル名 (たとえば、 C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe) を入力します。 sqlservr引数の詳細については、「sqlservr Application」を参照してください。

拡張ストアド プロシージャを 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 システム ストアド プロシージャを使用して行われます。

sp_addextendedprocの最初のパラメーターは関数の名前を指定し、2 番目のパラメーターは、その関数が存在する DLL の名前を指定します。 DLL の完全なパスを指定する必要があります。

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 システム管理者は、関数の名前と、その関数が存在する DLL の名前を指定して、 sp_dropextendedproc システム ストアド プロシージャを実行する必要があります。 たとえば、次のコマンドは、xp_hello.dll,という名前の DLL にある関数xp_helloを SQL Server から削除します。

sp_dropextendedproc 'xp_hello';

sp_dropextendedproc では、システム拡張ストアド プロシージャは削除されません。 代わりに、システム管理者は、public ロールに対する拡張ストアド プロシージャに対するEXECUTEアクセス許可を拒否する必要があります。

拡張ストアド プロシージャ DLL をアンロードする

SQL Server は、DLL のいずれかの関数に対する呼び出しが行われるとすぐに、拡張ストアド プロシージャ DLL を読み込みます。 DLL は、サーバーがシャットダウンされるまで、またはシステム管理者が DBCC ステートメントを使用してアンロードするまで読み込まれたままになります。 たとえば、次のコマンドを実行すると、 xp_hello.dllがアンロードされ、システム管理者はサーバーをシャットダウンせずに、このファイルの新しいバージョンをディレクトリにコピーできます。

DBCC xp_hello(FREE);

関連するコンテンツ

• 共通言語ランタイム (CLR) 統合
• CLR テーブル値関数
• データベース エンジン拡張ストアド プロシージャ - プログラミング
• SQL Server にインストールされた拡張ストアド プロシージャの照会
• srv_got_attention (拡張ストアド プロシージャ API)
• sp_addextendedproc (Transact-SQL)
• sp_dropextendedproc (Transact-SQL)
• sp_helpextendedproc (Transact-SQL)
• DBCC dllname (FREE) (Transact-SQL)