Gespeicherte Prozeduren mit Pass-Through-Abfragen

Für den Zugriff auf die Daten einer SQL Server-Datenbank gibt es mehrere Methoden. Die erste ist das Einbinden der Tabellen per ODBC. Sie greifen dann – oberflächlich betrachtet – genau wie auf lokale Daten zu. Die andere, performantere und auch für den Mehrbenutzerbetrieb effizientere Variante, ist der Zugriff über Pass-Through-Abfragen auf gespeicherte Prozeduren. Gespeicherte Prozeduren sind Skripte, die Anweisungen auf dem SQL Server ausgeben und die ihre Ergebnisse, also zum Beispiel gefundene Datensätze, zurückgeben können. Zugriff auf solche gespeicherten Prozeduren erhalten Sie über die Nutzung sogenannter Pass-Through-Abfragen. Wie Sie die gespeicherten Prozeduren und Pass-Through-Abfragen kombinieren, um Daten vom SQL Server in Ihre Access-Datenbank zu bekommen, zeigt dieser Beitrag.

Voraussetzungen

Für die Beispiele dieses Beitrags benötigen Sie die im Download befindliche Access-Datenbank, das SQL-Skript zum Erstellen des Backends auf dem SQL Server sowie eine Instanz des SQL Servers (das kann auch LocalDb oder die Express-Version sein) und das SQL Server Management Studio. Wie Sie die Datenbank mithilfe des SQL-Skripts aus dem Download erstellen, erfahren Sie im Beitrag SQL Server-Datenbanken kopieren (www.access-im-unternehmen.de/1153).

Gespeicherte Prozeduren

Gespeicherte Prozeduren, im Englischen Stored Procedures, sind eine der flexibelsten Objektarten im SQL Server. Sie können damit nicht nur Daten abfragen, sondern auch Daten manipulieren. Wenn Sie Daten abfragen, liefern gespeicherte Prozeduren die abgefragten Daten als Ergebnis zurück, wenn Sie Daten manipulieren, können Sie die gespeicherte Prozedur so programmieren, dass diese etwa die Anzahl der geänderten Datensätze zurückliefert. Sie können dabei auf den gesamten Sprachumfang von T-SQL zurückgreifen und nicht nur einfache Auswahlabfragen oder Aktionsabfragen definieren wir unter Access. Stattdessen können Sie sogar Strukturen wie Bedingungen oder Schleifen nutzen und in einer gespeicherten Prozedur mehrere Auswahl- oder Aktionsabfragen durchführen.

Pass-Through-Abfragen

Pass-Through-Abfragen sind eine Möglichkeit, von Access auf die Daten eines SQL Servers zuzugreifen. Sie können damit zum Beispiel einfach die Daten einer kompletten Tabelle zurückliefern, was dann in etwa dem Verknüpfen der Tabelle per ODBC entspricht – mit dem Unterschied, dass Sie über eine Pass-Through-Abfrage keine Daten ändern können. Die von Pass-Through-Abfragen gelieferten Ergebnisse sind also immer schreibgeschützt. Davon ab können Sie mit einer Pass-Through-Abfrage aber nicht nur Daten abfragen, sondern alle denkbaren SQL-Anweisungen an die Zieldatenbank schicken.

Dabei gibt es einen sehr wichtigen Punkt zu beachten: Pass-Through-Abfragen werden direkt an den jeweiligen SQL Server geschickt, sie müssen daher auch im SQL-Dialekt des jeweiligen Servers formuliert sein und nicht etwa in dem von Access/Jet verwendeten Dialekt.

Ein Beispiel: Während Sie in Access etwa das Sternchen (*) als Platzhalter für beliebige Zeichen verwenden, nutzen die meisten relationalen Datenbankmanagementsysteme das Prozent-Zeichen (%) als Platzhalter für beliebige Zeichen. Die Suche nach allen Artikeln, die mit A beginnen, würde also so lauten:

SELECT * FROM tblArtikel WHERE Artikelname LIKE 'A%'

Einer Pass-Through-Abfrage geben Sie über die Eigenschaften die Verbindungszeichenfolge mit, welche die Zieldatenbank für die Abfrage definiert.

Gespeicherte Prozeduren und Pass-Through-Abfragen

Wenn wir nun alle denkbaren SQL-Anweisungen über eine Pass-Through-Abfrage an den Server schicken können – warum sollten wir dann die Abfragen erst auf dem Server als gespeicherte Prozeduren definieren und diese dann von Access aus per Pass-Through-Abfrage aufrufen, statt diese direkt von Access aus an den SQL Server zu schicken? Die Antwort ist einfach: Die Performance einer Abfrage an den SQL Server richtet sich nicht nur nach der Menge der über das Netz zu transportierenden Daten (diese ist in beiden Fällen etwa gleich). Sie richtet sich auch danach, wie lange der SQL Server dafür braucht, diese Daten zusammenzustellen.

Und hier kommt der Abfrageoptimierer des SQL Servers ins Spiel: Wenn Sie eine Abfrage als gespeicherte Prozedur anlegen und diese ausführen, erstellt SQL Server automatisch einen Ausführungsplan, der in der Folge für weitere Aufrufe dieser Abfrage genutzt wird. Das gilt auch, wenn Sie für die Abfrage einen oder mehrere Parameter nutzen.

Wenn Sie jedoch eine Abfrage, deren Inhalt dem einer gespeicherten Prozedur gleichzusetzen ist, auf der Access-Seite formulieren und diese über eine Pass-Through-Abfrage an den SQL Server schicken, muss diese jedes Mal vom Abfrageoptimierer analysiert werden. Bei Abfragen, die nur einmalig genutzt werden, ergibt sich somit kein nennenswerter Unterschied, aber sobald Sie eine Abfrage mehr als einmal nutzen, haben Sie Performance-Vorteile, wenn Sie die Abfrage als gespeicherte Prozedur auf dem SQL Server speichern.

Wir werden uns also in der Regel auf die Kombination aus Pass-Through-Abfrage und gespeicherter Prozedur konzentrieren.

Gespeicherte Prozedur anlegen

Als Erstes legen wir eine einfache gespeicherte Prozedur auf unserer SQL Server-Datenbank Suedsturm_SQL an. Dabei belassen wir es für das erste Beispiel bei einer einfachen Abfrage, die alle Datensätze der Tabelle tblArtikel zurückliefern soll. Diese Abfrage legen wir im SQL Server Management Studio an.

Dazu öffnen wir SQL Server Management Studio und wechseln zur Datenbank Suedsturm_SQL. Hier navigieren wir zum Eintrag Suedsturm_SQL|Programmierbar­keit|Gespeicherte Prozeduren und wählen aus dem Kontextmenü dieses Eintrags den Befehl Gespeicherte Prozedur... aus (siehe Bild 1).

Bild 1: Neue gespeicherte Prozedur anlegen

Dies öffnet die Vorlage für gespeicherte Prozeduren, die es uns vereinfachen soll, schnell neue gespeicherte Prozeduren anzulegen (siehe Bild 2).

Bild 2: Vorlage für eine neue gespeicherte Prozedur

Hier sehen Sie gleich, dass die eigentliche Anweisung mit CREATE PROCEDURE beginnt. In diesem Abfragefenster geben wir also nicht den Code der gespeicherten Abfrage selbst ein, sondern den Code, den SQL Server zum Erstellen der gespeicherten Prozedur benötigt. Auf die gleiche Weise lautet der Code zum Verändern einer bestehenden gespeicherten Prozedur ALTER PROCEDURE. Die Vorlage ist für unsere Zwecke etwas überdimensioniert, sodass wir den enthaltenen Code etwas zusammenschrumpfen und schließlich folgendes SQL-Skript erhalten: