Programmieren Datenbank-Engine erweiterten gespeicherten Prozeduren

Gilt für: SQL Server

Funktionsweise erweiterter gespeicherter Prozeduren

Der Funktionsablauf einer erweiterten gespeicherten Prozedur kann folgendermaßen beschrieben werden:

1. Wenn ein Client eine erweiterte gespeicherte Prozedur ausführt, wird die Anforderung im TDS-Format (Tabular Data Stream) oder im SOAP-Format (Simple Object Access Protocol) von der Clientanwendung an SQL Server übertragen.

2. SQL Server sucht nach der DLL, die der erweiterten gespeicherten Prozedur zugeordnet ist, und lädt die DLL, wenn sie noch nicht geladen ist.

3. SQL Server ruft die angeforderte erweiterte gespeicherte Prozedur auf (als Funktion innerhalb der DLL implementiert).

4. Die erweiterte gespeicherte Prozedur übergibt über die API für erweiterte gespeicherte Prozeduren Resultsets und Rückgabeparameter an den Server zurück.

In der Vergangenheit wurde Open Data Services verwendet, um Serveranwendungen zu schreiben, z. B. Gateways zu anderen Datenbankumgebungen als SQL Server. SQL Server unterstützt nicht die veralteten Teile der Open Data Services-API. Der einzige Teil der ursprünglichen Open Data Services-API, die von SQL Server weiterhin unterstützt wird, sind die erweiterten gespeicherten Prozedurfunktionen, sodass die API in die API für erweiterte gespeicherte Prozedur umbenannt wurde.

Mit der Entstehung von verteilten Abfragen und der CLR-Integration wurde die Notwendigkeit für Erweiterte gespeicherte Prozedur-API-Anwendungen weitgehend ersetzt.

Wenn Sie über vorhandene Gatewayanwendungen verfügen, können Sie die opends60.dll mit SQL Server ausgelieferten Gatewayanwendungen nicht verwenden, um die Anwendungen auszuführen. Gateway-Anwendungen werden nicht mehr unterstützt.

Erweiterte gespeicherte Prozeduren im Vergleich zur CLR-Integration

Die CLR-Integration bietet eine robustere Alternative zum Schreiben serverseitiger Logik, die entweder schwer oder unmöglich war, in Transact-SQL zu schreiben. In früheren Versionen von SQL Server boten erweiterte gespeicherte Prozeduren (XPs) den einzigen Mechanismus, der für Datenbankanwendungsentwickler zum Schreiben dieses Codes verfügbar war.

Bei der CLR-Integration wird logik, die in Form von gespeicherten Prozeduren geschrieben wurde, häufig besser als Tabellenwertfunktionen ausgedrückt, wodurch die von der Funktion erstellten Ergebnisse in SELECT Anweisungen abgefragt werden können, indem sie sie in die FROM Klausel einbetten.

Weitere Informationen finden Sie in der Übersicht über die CLR-Integration.

Ausführungsmerkmale erweiterter gespeicherter Prozeduren

Die Ausführung einer erweiterten gespeicherten Prozedur weist folgende Merkmale auf:

• Die erweiterte gespeicherte Prozedurfunktion wird unter dem Sicherheitskontext von SQL Server ausgeführt.

• Die erweiterte gespeicherte Prozedurfunktion wird im Prozessbereich von SQL Server ausgeführt.

• Der mit der Ausführung der erweiterten gespeicherten Prozedur verknüpfte Thread ist derselbe wie derjenige, der für die Clientverbindung verwendet wird.

Nachdem die erweiterte gespeicherte Prozedur DLL geladen wurde, bleibt die DLL im Adressraum des Servers geladen, bis SQL Server beendet wird, oder der Administrator entlädt die DLL explizit mithilfe DBCC <DLL_name> (FREE)der DLL.

Die erweiterte gespeicherte Prozedur kann mithilfe der EXECUTE Anweisung aus Transact-SQL als gespeicherte Prozedur ausgeführt werden:

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

Parameter

@ retval

Ein Rückgabewert.

@ param1

Ein Eingabeparameter.

@ param2

Ein Eingabe-/Ausgabeparameter.

Senden von Resultsets an den Server mit der API für erweiterte gespeicherte Prozedur

Beim Senden eines Resultsets an SQL Server sollte die erweiterte gespeicherte Prozedur die entsprechende API wie folgt aufrufen:

• Die srv_sendmsg Funktion kann in beliebiger Reihenfolge vor oder nach allen Zeilen (falls vorhanden) aufgerufen srv_sendrowwerden. Alle Nachrichten müssen an den Client gesendet werden, bevor der Abschlussstatus gesendet srv_senddonewird.

• Die srv_sendrow Funktion wird einmal für jede Zeile aufgerufen, die an den Client gesendet wird. Alle Zeilen müssen an den Client gesendet werden, bevor Nachrichten, Statuswerte oder Abschlussstatus mit srv_sendmsg, dem srv_status Argument von srv_pfieldoder srv_senddone.

• Beim Senden einer Zeile, die nicht alle Spalten definiert srv_describe ist, löst die Anwendung eine Informationsfehlermeldung aus und kehrt zum Client zurück FAIL . In diesem Fall wird die Zeile nicht gesendet.

Erstellen erweiterter gespeicherter Prozeduren

Eine erweiterte gespeicherte Prozedur ist eine C/C++-Funktion mit einem Prototyp:

SRVRETCODE xp_extendedProcName ( SRVPROC *);

Die Verwendung des Präfixes xp_ ist optional. Namen erweiterter gespeicherter Prozeduren berücksichtigen die Groß-/Kleinschreibung, wenn auf Transact-SQL-Anweisungen verwiesen wird, unabhängig von der auf dem Server installierten Codepage/Sortierreihenfolge. Wenn Sie eine DLL erstellen:

• Wenn ein Einstiegspunkt erforderlich ist, schreiben Sie eine DllMain Funktion.

  Diese Funktion ist optional. Wenn Sie sie nicht im Quellcode bereitstellen, verknüpft der Compiler seine eigene Version, die nichts anderes zurückgibt TRUE. Wenn Sie eine DllMain Funktion bereitstellen, ruft das Betriebssystem diese Funktion auf, wenn ein Thread oder Prozess an die DLL angefügt oder getrennt wird.

• Alle von außerhalb der DLL aufgerufenen Funktionen (alle e-Funktionen einer erweiterten gespeicherten Prozedur) müssen exportiert werden.

  Sie können eine Funktion exportieren, indem Sie den EXPORTS Namen im Abschnitt einer .def Datei auflisten, oder Sie können dem Funktionsnamen im Quellcode __declspec(dllexport)eine Microsoft-Compilererweiterung voranstellen (__declspec() beginnt mit zwei Unterstrichen).

Diese Dateien sind zum Erstellen einer DLL einer erweiterten gespeicherten Prozedur erforderlich.

Um eine DLL für eine erweiterte gespeicherte Prozedur zu erstellen, erstellen Sie ein Projekt des Typs Dynamic Link Library. Weitere Informationen über das Erstellen einer DLL finden Sie in der Dokumentation zur Entwicklungsumgebung.

Alle DLLs für erweiterte gespeicherte Prozeduren sollten die folgende Funktion implementieren und exportieren:

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

__declspec(dllexport) ist eine microsoftspezifische Compilererweiterung. Wenn Ihr Compiler diese Direktive nicht unterstützt, sollten Sie diese Funktion in Ihrer DEF Datei unter dem EXPORTS Abschnitt exportieren.

Wenn SQL Server mit dem Ablaufverfolgungskennzeichnung -T260 gestartet wird oder ein Benutzer mit Systemadministratorberechtigungen ausgeführt DBCC TRACEON (260)wird und die erweiterte gespeicherte Prozedur DLL nicht unterstützt __GetXpVersion(), wird die folgende Warnmeldung in das Fehlerprotokoll gedruckt (__GetXpVersion() beginnt mit zwei Unterstrichen).

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

Wenn die erweiterte gespeicherte Prozedur DLL exportiert __GetXpVersion(), die von der Funktion zurückgegebene Version jedoch kleiner als die vom Server erforderliche Version ist, wird eine Warnmeldung angezeigt, die die von der Funktion zurückgegebene Version angibt, und die vom Server erwartete Version wird in das Fehlerprotokoll gedruckt. Wenn Sie diese Meldung erhalten, geben Sie einen falschen Wert zurück __GetXpVersion(), oder Sie kompilieren mit einer älteren Version von srv.h.

Lange ausgeführte erweiterte gespeicherte Prozeduren sollten regelmäßig aufgerufen werden srv_got_attention , damit die Prozedur sich selbst beenden kann, wenn die Verbindung beendet wird oder der Batch abgebrochen wird.

Um eine erweiterte gespeicherte Prozedur-DLL zu debuggen, kopieren Sie sie in das SQL Server-Verzeichnis \Binn . Um die ausführbare Datei für die Debugsitzung anzugeben, geben Sie den Pfad und Dateinamen der ausführbaren SQL Server-Datei ein (z. B C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\sqlservr.exe. ). Informationen zu sqlservr Argumenten finden Sie unter sqlservr Application.

Hinzufügen einer erweiterten gespeicherten Prozedur zu SQL Server

Eine DLL, die erweiterte gespeicherte Prozedurfunktionen enthält, dient als Erweiterung für SQL Server. Um die DLL zu installieren, kopieren Sie die Datei in ein Verzeichnis, z. B. die Datei, die die standardmäßigen SQL Server-DLL-Dateien enthält (C:\Program Files\Microsoft SQL Server\MSSQL16.0.<x>\MSSQL\Binn standardmäßig).

Nachdem die DLL der erweiterten gespeicherten Prozedur auf den Server kopiert wurde, muss ein SQL Server-Systemadministrator jede erweiterte gespeicherte Prozedurfunktion in der DLL bei SQL Server registrieren. Dazu wird die sp_addextendedproc gespeicherte Systemprozedur verwendet.

Der erste Parameter von sp_addextendedproc specifies the name of the function, and the second parameter specifies the name of the DLL in which function resides. Sie sollten den vollständigen Pfad der DLL angeben.

Der Name der im sp_addextendedproc-Parameter angegebenen Funktion muss genau (einschließlich Groß-/Kleinschreibung) dem Funktionsnamen in der DLL entsprechen. Beispielsweise registriert dieser Befehl eine Funktion xp_hello, , die sich in einer DLL mit dem Namen xp_hello.dll", als erweiterte gespeicherte SQL Server-Prozedur befindet:

sp_addextendedproc 'xp_hello', 'c:\Program Files\Microsoft SQL Server\MSSQL13.0.MSSQLSERVER\MSSQL\Binn\xp_hello.dll';

Wenn der name der in der Angegebenen sp_addextendedproc Funktion nicht exakt mit dem Funktionsnamen in der DLL übereinstimmt, wird der neue Name in SQL Server registriert, aber der Name kann nicht verwendet werden. Obwohl sie beispielsweise xp_Hello als erweiterte gespeicherte SQL Server-Prozedur xp_hello.dllregistriert ist, kann SQL Server die Funktion in der DLL nicht finden, wenn Sie xp_Hello die Funktion später aufrufen.

-- 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;

Dies ist die Fehlermeldung:

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.).

Wenn der Name der in sp_addextendedproc der Funktion angegebenen Funktion exakt mit dem Funktionsnamen in der DLL übereinstimmt und bei der Sortierung der SQL Server-Instanz die Groß-/Kleinschreibung nicht beachtet wird, kann der Benutzer die erweiterte gespeicherte Prozedur mit einer beliebigen Kombination aus Klein- und Großbuchstaben des Namens aufrufen.

-- 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;

Wenn bei der Sortierung der SQL Server-Instanz die Groß-/Kleinschreibung beachtet wird, kann SQL Server die erweiterte gespeicherte Prozedur nicht aufrufen, wenn die Prozedur mit einem anderen Fall aufgerufen wird. Dies gilt auch, wenn sie mit genau demselben Namen und der Sortierung wie die Funktion in der DLL registriert wurde.

-- 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;

Dies ist die Fehlermeldung:

Server: Msg 2812, Level 16, State 62, Line 1

Sie müssen SQL Server nicht beenden und neu starten.

Abfrage erweiterte gespeicherte Prozeduren, die in SQL Server installiert sind

Ein authentifizierter SQL Server-Benutzer kann die aktuell definierten erweiterten gespeicherten Prozeduren und den Namen der DLL anzeigen, zu der jedes gehört, indem die sp_helpextendedproc Systemprozedur ausgeführt wird. Im folgenden Beispiel wird beispielsweise die DLL zurückgegeben, zu xp_hello der gehört:

sp_helpextendedproc 'xp_hello';

Wenn sp_helpextendedproc ohne Angabe einer erweiterten gespeicherten Prozedur ausgeführt wird, werden alle erweiterten gespeicherten Prozeduren und ihre DLLs angezeigt.

Entfernen einer erweiterten gespeicherten Prozedur aus SQL Server

Um jede erweiterte gespeicherte Prozedurfunktion in einer benutzerdefinierten DLL für erweiterte gespeicherte Prozeduren abzulegen, muss ein SQL Server-Systemadministrator die sp_dropextendedproc gespeicherte Systemprozedur ausführen und den Namen der Funktion und den Namen der DLL angeben, in der sich diese Funktion befindet. Mit diesem Befehl wird beispielsweise die Funktion xp_helloentfernt, die sich in einer DLL mit dem Namen sql Server befindet xp_hello.dll, :

sp_dropextendedproc 'xp_hello';

sp_dropextendedproc systemweite gespeicherte Prozeduren werden nicht abgelegt. Stattdessen sollte der Systemadministrator die Berechtigung für die erweiterte gespeicherte EXECUTE Prozedur für die öffentliche Rolle verweigern.

Entladen einer erweiterten gespeicherten Prozedur-DLL

SQL Server lädt eine erweiterte gespeicherte Prozedur-DLL, sobald ein Aufruf an eine der Funktionen der DLL erfolgt. Die DLL bleibt geladen, bis der Server heruntergefahren wird oder bis der Systemadministrator die DBCC Anweisung zum Entladen verwendet. Mit diesem Befehl wird beispielsweise der xp_hello.dllSystemadministrator entladen, sodass er eine neuere Version dieser Datei in das Verzeichnis kopieren kann, ohne den Server herunterzufahren:

DBCC xp_hello(FREE);

Zugehöriger Inhalt

• ClR-Integration (Common Language Runtime)
• CLR-Tabellenwertfunktionen
• Datenbank-Engine – Programmierung der erweiterten gespeicherten Prozeduren
• Abfragen von in SQL Server installierten erweiterten gespeicherten Prozeduren
• srv_got_attention (API für erweiterte gespeicherte Prozeduren)
• sp_addextendedproc (Transact-SQL)
• sp_dropextendedproc (Transact-SQL)
• sp_helpextendedproc (Transact-SQL)
• DBCC dllname (FREE) (Transact-SQL)