{"id":55001172,"date":"2019-02-01T00:00:00","date_gmt":"2020-05-13T21:05:29","guid":{"rendered":"http:\/\/access-im-unternehmen.aix-dev.de\/aiu\/?p=1172"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-30T00:00:00","slug":"Gespeicherte_Prozeduren_mit_PassThroughAbfragen","status":"publish","type":"post","link":"https:\/\/access-im-unternehmen.de\/Gespeicherte_Prozeduren_mit_PassThroughAbfragen\/","title":{"rendered":"Gespeicherte Prozeduren mit Pass-Through-Abfragen"},"content":{"rendered":"

\"\"<\/p>\n

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.<\/b><\/p>\n

Voraussetzungen<\/h2>\n

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 <\/b>(www.access-im-unternehmen.de\/1153<\/b>).<\/p>\n

Gespeicherte Prozeduren<\/h2>\n

Gespeicherte Prozeduren, im Englischen Stored Procedures<\/b>, 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. <\/p>\n

Pass-Through-Abfragen<\/h2>\n

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.<\/p>\n

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.<\/p>\n

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

SELECT * FROM tblArtikel WHERE Artikelname LIKE ''''A%''''<\/pre>\n
Einer Pass-Through-Abfrage geben Sie über die Eigenschaften die Verbindungszeichenfolge mit, welche die Zieldatenbank für die Abfrage definiert.<\/p>\n
Gespeicherte Prozeduren und Pass-Through-Abfragen<\/h2>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Wir werden uns also in der Regel auf die Kombination aus Pass-Through-Abfrage und gespeicherter Prozedur konzentrieren.<\/p>\n
Gespeicherte Prozedur anlegen<\/h2>\n
Als Erstes legen wir eine einfache gespeicherte Prozedur auf unserer SQL Server-Datenbank Suedsturm_SQL <\/b>an. Dabei belassen wir es für das erste Beispiel bei einer einfachen Abfrage, die alle Datensätze der Tabelle tblArtikel<\/b> zurückliefern soll. Diese Abfrage legen wir im SQL Server Management Studio an.<\/p>\n
Dazu öffnen wir SQL Server Management Studio und wechseln zur Datenbank Suedsturm_SQL<\/b>. Hier navigieren wir zum Eintrag Suedsturm_SQL|Programmierbar-keit|Gespeicherte Prozeduren <\/b>und wählen aus dem Kontextmenü dieses Eintrags den Befehl Gespeicherte Prozedur… <\/b>aus (siehe Bild 1).<\/p>\n
\"Neue<\/p>\n
Bild 1: Neue gespeicherte Prozedur anlegen<\/span><\/b><\/p>\n
Dies öffnet die Vorlage für gespeicherte Prozeduren, die es uns vereinfachen soll, schnell neue gespeicherte Prozeduren anzulegen (siehe Bild 2).<\/p>\n
\"Vorlage<\/p>\n
Bild 2: Vorlage für eine neue gespeicherte Prozedur<\/span><\/b><\/p>\n
Hier sehen Sie gleich, dass die eigentliche Anweisung mit CREATE PROCEDURE <\/b>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<\/b>. Die Vorlage ist für unsere Zwecke etwas überdimensioniert, sodass wir den enthaltenen Code etwas zusammenschrumpfen und schließlich folgendes SQL-Skript erhalten:<\/p>\n
CREATE PROCEDURE dbo.spArtikelAlle \r\nAS\r\nSET NOCOUNT ON;\r\nSELECT * FROM tblArtikel;<\/pre>\n
<\/p>\n
Mit einem Klick auf die Taste F5 <\/b>führen Sie die Abfrage zum Erstellen der gespeicherten Prozedur aus. Dies legt einen neuen Eintrag im Objekt-Explorer unter Pro-gram-mier-bar-keit|Ge-speicherte Prozeduren <\/b>an – siehe Bild 3. Wenn Sie die gespeicherte Prozedur anpassen können, rufen Sie den Kontextmenü-Befehl ändern <\/b>auf.<\/p>\n
\"Die<\/p>\n
Bild 3: Die neue gespeicherte Prozedur im Objekt-Explorer<\/span><\/b><\/p>\n
Namen für gespeicherte Prozeduren<\/h2>\n
Gespeicherte Prozeduren erhalten normalerweise das Präfix sp<\/b>, also zum Beispiel spArtikelAlle<\/b>. Die Variante mit dem Unterstrich, also etwa sp_ArtikelAlle<\/b>, sollten Sie nicht verwenden, da SQL Server dann beim Aufruf erst die gespeicherten Systemprozeduren durchsucht und nicht direkt in der Zieldatenbank. Dem Namen für die gespeicherte Prozedur stellen wir als Präfix das Standardschema voran, hier dbo.spArtikelAlle<\/b>. Wenn Sie mit benutzerdefinierten Schemas arbeiten, können Sie auch dieses hier angeben (mehr zu solchen Schemas im Beitrag SQL Server: Sicherheit mit Schema<\/b>, www.access-im-unternehmen.de\/1179<\/b>).<\/p>\n
Gespeicherte Prozedur ausführen<\/h2>\n
Um die gespeicherte Prozedur auszuführen, wählen Sie den Kontextmenü-Eintrag Gespeicherte Prozedur ausführen… <\/b>aus. Als Erstes erscheint ein Dialog, der die Eingabe von Parametern für die gespeicherte Prozedur erlaubt. Da wir keine Parameter für diese gespeicherte Prozedur definiert haben, ist dieser Dialog allerdings unnötig und Sie können ihn gleich mit OK <\/b>bestätigen.<\/p>\n
Anschließend liefert SQL Server Management Studio das Ergebnis der Abfrage samt eines kleinen, durch den Aufruf des Kontextmenü-Befehls erstellten Skripts im mittleren Bereich (siehe Bild 4).<\/p>\n
\"Ausführen<\/p>\n
Bild 4: Ausführen der gespeicherten Prozedur<\/span><\/b><\/p>\n
Das bekommen Sie auch etwas übersichtlicher hin, indem Sie einfach den Befehl EXEC dbo.ArtikelAlle <\/b>in einer neuen Abfrage ausführen (siehe Bild 5). Damit erhalten wir auch gleich den Befehl, den wir in unserer gleich zu erstellenden Pass-Through-Abfrage verwenden wollen.<\/p>\n
\"Ausführen<\/p>\n
Bild 5: Ausführen der gespeicherten Prozedur per EXEC<\/span><\/b><\/p>\n
Ge-speicherte Prozedur per Pass-Through-Abfrage<\/h2>\n
Mit der soeben erstellten gespeicherten Prozedur wollen wir nun eine Pass-Through-Abfrage in Access füttern und damit die gewünschten Daten in Access anzeigen.<\/p>\n
Dazu gibt es in Access 2016 noch nicht einmal mehr einen passenden Assistenten – wir müssen also mit dem herkömmlichen Abfrageentwurf auskommen.<\/p>\n
Nachdem Sie also über den Ribbon-Eintrag Er-stel-len|Ab-fra-gen|Ab-frageentwurf <\/b>eine neue Abfrage erstellt haben, schließen Sie zunächst den automatisch erscheinenden Dialog Tabelle anzeigen<\/b>. Oben im Ribbon finden Sie den Befehl Ent-wurf|Ab-fragetyp|Pass-Through<\/b>, mit dem Sie die Abfrage in eine Pass-Through-Abfrage umwandeln können. Auf den ersten Blick ist die einzige änderung, dass der Abfrageentwurf nun in der SQL-Ansicht angezeigt wird (siehe Bild 6).<\/p>\n
\"Umwandeln<\/p>\n
Bild 6: Umwandeln der Abfrage in eine Pass-Through-Abfrage<\/span><\/b><\/p>\n
Es gibt aber noch einen weiteren Unterschied: Das Eigenschaftsfenster hat sich nämlich ebenfalls geändert (siehe Bild 7).  Einige Eigenschaften wie Standardansicht<\/b>, Alle Felder ausgeben<\/b>, Spitzenwerte<\/b>, Keine Duplikate<\/b>, Eindeutige Datensätze <\/b>und noch weitere sind weggefallen. Dafür sind die folgenden Eigenschaften hinzugekommen:<\/p>\n
\"Eigenschaften<\/p>\n
Bild 7: Eigenschaften einer Pass-Through-Abfrage<\/span><\/b><\/p>\n