DataRow, DataTable oder PSObject? – Was Invoke-DbaQuery mit dem Parameter -As zurückgibt

Im Slack der dbatools-Community wird immer mal wieder die Frage gestellt, wie denn mit Invoke-DbaQuery mehrteilige Skripte oder Befehle mit mehr als einer Ergebnismenge verarbeitet werden können. Daher hierzu ein paar Beispiele.

Zwei Werkzeuge: Get-Member und GetType()

Doch vorab möchte ich ein paar Werkzeuge vorstellen und Tipps geben, die jede:r Nutzer:in von dbatools kennen sollte.

Ganz generell: Speichert die Rückgabe der Befehle in Variablen ab. Denn anderenfalls landet die Ausgabe der Befehle einfach auf der Konsole und zwar in genau der Art und Weise, die PowerShell gerade für richtig hält. Dabei kann es durchaus passieren, dass nicht alle Informationen für die/den Nutzer:in sichtbar sind. Diese Informationen sind dann für die/den Nutzer:in verloren.

Und wenn ihr die Ausgabe eines Befehls in einer Variablen gespeichert habt, kontrolliert besser noch einmal, ob dort wirklich die Speicherstruktur enthalten ist, die ihr erwartet. Wie? Hiermit:

$myVariable | Get-Member 
$myVariable | gm         # gm ist der Alias und spart jede Menge Tastaturanschläge  

Get-Member nimmt hier Objekte über die Pipeline entgegen und zeigt euch nicht nur den Datentyp der einzelnen Objekte, sondern auch alle Eigenschaften und Methoden. Aber Achtung: Ist $myVariable ein Array oder eine Collection, so erhaltet ihr nur die Informationen über die enthaltenen Objekte. Wenn alle Objekte vom gleichen Typ sind, bekommt ihr nur eine Ausgabe und könnt daraus nicht schließen, dass $myVariable direkt ein Objekt dieses Typs enthält. Daher hier eine weitere Möglichkeit:

$myVariable.GetType() 
$myVariable.GetType().FullName  

GetType() ist eine Methode, die in jeder Klasse enthalten ist und die ich auch gerne in Debug-Meldungen verwende, um mir den tatsächlichen Datentyp anzuzeigen. Hiermit könnt ihr sehr gut feststellen, ob es sich bei $myVariable um ein einzelnes Objekt oder ein Array handelt.

Ein Hinweis, bevor der erste Befehl gegen eine Instanz läuft: Seit Version 2.0 bauen die dbatools die Verbindung standardmäßig verschlüsselt auf und vertrauen dabei nur noch Zertifikaten einer anerkannten Zertifizierungsstelle. Gegen eine Testinstanz, die noch das selbstsignierte Zertifikat aus der Installation verwendet, brechen alle folgenden Beispiele deshalb mit der Meldung „Die Zertifikatkette wurde von einer nicht vertrauenswürdigen Zertifizierungsstelle ausgestellt“ ab. Wie sich das einstellen lässt, habe ich in „Version 2.1.0 der dbatools – Ein Update mit Hindernissen“ beschrieben.

Dazu ein Beispiel:

Import-Module dbatools 

$database = Get-DbaDatabase -SqlInstance SRV1 -Database master 
$database | Get-Member           # TypeName: Microsoft.SqlServer.Management.Smo.Database 
$database.GetType().FullName     # Microsoft.SqlServer.Management.Smo.Database = Die Variable enthält ein Datenbank-Objekt 

$database = Get-DbaDatabase -SqlInstance SRV1 -Database master, tempdb 
$database | Get-Member           # TypeName: Microsoft.SqlServer.Management.Smo.Database 
$database.GetType().FullName     # System.Object[] = Es ist ein Array von Objekten 
$database.Count                  # 2 = Das Array enthält zwei Objekte 
$database[0].GetType().FullName  # Microsoft.SqlServer.Management.Smo.Database = Das erste Element ist ein Datenbank-Objekt  

Wenn ihr nicht sicher seid, ob die Variable ein einzelnes Objekt oder aber ein Array von Objekten enthält, dann könnt ihr in beiden Fällen die foreach-Schleife nutzen, um innerhalb der Schleife auf jeden Fall einzelne Objekte zu haben. Schaut doch mal in den Quellcode der dbatools, das machen wir dort ständig. 

foreach ($db in $database) { 
    $db.GetType().FullName  # Microsoft.SqlServer.Management.Smo.Database = Die Variable $db enthält ein Datenbank-Objekt 
}  

Eine Prozedur, vier Ergebnismengen

Soweit zur Vorbereitung, kommen wir jetzt zu Invoke-DbaQuery. Als eine Prozedur, die mehr als eine Ergebnismenge liefert, werde ich hier sp_BlitzFirst aus dem First Responder Kit von Brent Ozar nutzen. Falls Sie das Kit nicht sowieso installiert haben, dann können Sie die benötigten Prozeduren direkt mit einem dbatools-Befehl installieren: 

Install-DbaFirstResponderKit -SqlInstance SRV1 -OnlyScript sp_BlitzFirst.sql, sp_Blitz.sql, sp_ineachdb.sql  

Ich verwende hier den Parameter -OnlyScript, weil ich euch so auch gleich zeigen kann, welche Skripte aus dem Paket ihr euch von GitHub herunterladen müsst, wenn ihr Install-DbaFirstResponderKit wegen fehlendem Internetzugang nicht nutzen könnt. Neben sp_BlitzFirst brauchen wir weiter unten noch sp_Blitz, und das setzt seit Version 8.34 des Kits vom Juli 2026 die Prozedur sp_ineachdb voraus: Fehlt sie, bricht jeder Aufruf von sp_Blitz mit der Meldung ab, dass die Prozedur dbo.sp_ineachdb nicht gefunden wurde. Welche Werte der Parameter annimmt, gibt das First Responder Kit vor – die Liste wandert mit dem Kit und steht in der Hilfe zum Befehl.

Ich möchte hier die Ausgabe von sp_BlitzFirst @SinceStartup = 1 verwenden, die mit diesem Parameter vier Ergebnismengen zurückliefert, ohne ihn dagegen nur eine. Geprüft habe ich das mit Version 8.34 des Kits; wie viele Ergebnismengen eine fremde Prozedur liefert, kann sich mit jeder neuen Version ändern. Probiert zum Vergleich diesen Befehl doch auch im SQL Server Management Studio aus.

$blitzFirstResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_BlitzFirst @SinceStartup = 1' 
$blitzFirstResult | Get-Member           # System.Data.DataRow 
$blitzFirstResult.GetType().FullName     # System.Object[] 
$blitzFirstResult[0].GetType().FullName  # System.Data.DataRow  

Wir bekommen also nur die erste Tabelle in Form eines Arrays von Zeilen.

Um sich die Daten ähnlich der Ansicht im SQL Server Management Studio anzeigen zu lassen, verwendet bitte Out-GridView:

$blitzFirstResult | Out-GridView

Out-GridView gibt es nur unter Windows. Unter Linux und macOS übernimmt Out-ConsoleGridView aus dem Modul Microsoft.PowerShell.ConsoleGuiTools dieselbe Aufgabe, allerdings im Terminal statt in einem eigenen Fenster.

DataRow, DataTable und DataSet

Der Schlüssel zur Anzeige der weiteren Ergebnismengen liegt im Parameter -As, den ich im Folgenden verwenden werde. 

$blitzFirstResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_BlitzFirst @SinceStartup = 1' -As DataRow 
$blitzFirstResult | Get-Member           # System.Data.DataRow 
$blitzFirstResult.GetType().FullName     # System.Object[] 
$blitzFirstResult[0].GetType().FullName  # System.Data.DataRow  

Das ist der Standard, wir bekommen also das gleiche Ergebnis. 

$blitzFirstResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_BlitzFirst @SinceStartup = 1' -As DataTable 
$blitzFirstResult | Get-Member           # System.Data.DataTable 
$blitzFirstResult.GetType().FullName     # System.Object[] 
$blitzFirstResult[0].GetType().FullName  # System.Data.DataTable 
$blitzFirstResult.Count                  # 4  

Jetzt haben wir ein Array aus vier Elementen, das sind die vier Ergebnismengen, die wir auch im SQL Server Management Studio sehen. 

$blitzFirstResultTable1 = $blitzFirstResult[0] 
$blitzFirstResultTable1 | Get-Member       # System.Data.DataRow 
$blitzFirstResultTable1.GetType().FullName # System.Data.DataTable 

Die Variable $blitzFirstResultTable1 enthält also ein Element vom Typ DataTable. Wird dieses Objekt über eine Pipeline an andere Befehle übergeben, so wird es dabei in einzelne Objekte vom Typ DataRow zerlegt.

Das können wir wieder zur Anzeige mit Out-GridView nutzen:

$blitzFirstResultTable1 | Out-GridView

Auch eine foreach-Schleife zerlegt die Tabelle in einzelne Zeilen:

foreach ($row in $blitzFirstResultTable1) { 
    $row.GetType().FullName  # System.Data.DataRow 
} 

Es gibt da noch eine Option, die allerdings eher selten verwendet wird: 

$blitzFirstResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_BlitzFirst @SinceStartup = 1' -As DataSet 
$blitzFirstResult | Get-Member           # System.Data.DataSet 
$blitzFirstResult.GetType().FullName     # System.Data.DataSet 

Hier haben wir ein einzelnes Objekt vom Typ DataSet, das auch durch eine Pipeline nicht zerlegt wird. Wie kommen wir hier an die einzelnen Tabellen? Diese sind über die Eigenschaft Tables erreichbar. 

$blitzFirstResult.Tables.GetType().FullName  # System.Data.DataTableCollection 
$blitzFirstResult.Tables.Count               # Da sind unsere vier Tabellen 
$blitzFirstResultTable1 = $blitzFirstResult.Tables[0] 
$blitzFirstResultTable1 | Get-Member         # System.Data.DataRow 
$blitzFirstResultTable1.GetType().FullName   # System.Data.DataTable  

PSObject und PSObjectArray: das Thema NULL

Das waren die Optionen, die mit Data* beginnen. Aber es gibt noch PSObject und PSObjectArray, was machen die?

Vorweg: Es geht um das Thema NULL. Da die bisher genutzte Ausgabe aber keine NULL-Werte enthält, müssen wir jetzt mit der Ausgabe von sp_Blitz arbeiten. Führen wir zunächst die Prozedur aus und werfen einen Blick auf das Ergebnis:

$blitzResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_Blitz' -As DataRow 
$blitzResult | Out-GridView  

Die Spalte DatabaseName ist teilweise leer, daher nehmen wir doch die: 

$emptyDatabase = $blitzResult | Where-Object -Property Priority -EQ -Value 0 | Select-Object -ExpandProperty DatabaseName 
$emptyDatabase.GetType().FullName  # System.DBNull 
$null -eq $emptyDatabase           # False  

Wir haben hier also nicht etwa $null, wie wir das in der PowerShell-Welt erwarten würden, sondern wir haben hier ein Objekt der Klasse System.DBNull.

Führen wir nun die gleiche Abfrage mit der Option PSObject aus:

$blitzResult = Invoke-DbaQuery -SqlInstance SRV1 -Query 'sp_Blitz' -As PSObject 
$emptyDatabase = $blitzResult | Where-Object -Property Priority -EQ -Value 0 | Select-Object -ExpandProperty DatabaseName 
$emptyDatabase.GetType().FullName  # Fehler: You cannot call a method on a null-valued expression. 
$null -eq $emptyDatabase           # True  

Hier haben wir jetzt das gewohnte $null, daher schlägt auch die Methode GetType fehl.

Und von welchem Typ sind die einzelnen Zeilen?

$blitzResult[0].GetType().FullName  # System.Management.Automation.PSCustomObject 

Es handelt sich also nicht mehr um Zeilen, sondern um Objekte. Für die weitere Arbeit spielt das (fast) keine Rolle, hat aber zwei handfeste Vorteile: Leere Werte lassen sich wie überall in PowerShell mit $null vergleichen, und die Objekte tragen keinen Ballast mit sich. Eine DataRow schleppt bei ConvertTo-Json die Eigenschaften RowError, RowState, Table, ItemArray und HasErrors mit, ein PSCustomObject nur seine Spalten.

Und was ist mit PSObjectArray? Dazu zuerst ein Unterschied, der leicht übersehen wird: Anders als DataRow beschränkt sich PSObject nicht auf die erste Ergebnismenge, sondern liefert die Zeilen aller Ergebnismengen hintereinander in einem flachen Array, ohne Trennung zwischen den Ergebnismengen. Bei sp_BlitzFirst @SinceStartup = 1 sind das die Zeilen aller vier Tabellen. PSObjectArray arbeitet vergleichbar zu DataTable und liefert ein Array mit so vielen Elementen, wie es (nicht-leere) Ergebnismengen gibt. Leere Ergebnismengen fehlen darin also, anders als im DataSet, und die folgenden rücken auf. Jedes Element dieses Arrays enthält wiederum ein Array mit den einzelnen Zeilen der Ergebnismenge als PSCustomObject. Mit einer Ausnahme: Hat eine Ergebnismenge nur eine Zeile, steht an ihrer Stelle kein Array, sondern das einzelne PSCustomObject, denn PowerShell packt ein einzelnes Objekt nicht von sich aus in ein Array. Bei sp_BlitzFirst betrifft das die vierte Ergebnismenge, und genau dafür lohnt sich der Blick mit GetType() vom Anfang dieses Textes.

SingleValue

Bleibt der sechste Wert, und der fällt aus der Reihe: SingleValue. Bei allen bisherigen Optionen ging es darum, an mehr heranzukommen – an die zweite Ergebnismenge, an die einzelnen Zeilen. Hier geht es um das Gegenteil. Manche Abfragen liefern genau einen Wert, und dann ist der Weg über Tabelle und Zeile nur ein Umweg.

$dbCount = Invoke-DbaQuery -SqlInstance SRV1 -Query 'SELECT COUNT(*) FROM sys.databases' -As SingleValue 
$dbCount.GetType().FullName  # System.Int32 
$dbCount + 1                 # eine Zahl, mit der sich sofort weiterrechnen lässt 

Ohne -As SingleValue kommt dieselbe Abfrage als Zeile zurück und damit ich den Wert überhaupt herausgreifen kann, braucht die Spalte zusätzlich einen Namen: 

$row = Invoke-DbaQuery -SqlInstance SRV1 -Query 'SELECT COUNT(*) AS DatabaseCount FROM sys.databases' 
$row.GetType().FullName                # System.Data.DataRow 
$row.DatabaseCount.GetType().FullName  # System.Int32  

Beide Wege führen zum selben Wert, aber der erste sagt bereits in der Zeile, was gemeint ist. Man würde erwarten, dass SingleValue genau das liefert, was der Name sagt: einen einzelnen Wert, die erste Spalte der ersten Zeile. Ganz so ist es nicht und weil es gerade um NULL und um den Unterschied zwischen einzelnem Objekt und Array ging, lohnt sich hier noch einmal der genaue Blick: 

$names = Invoke-DbaQuery -SqlInstance SRV1 -Query 'SELECT name FROM sys.databases ORDER BY database_id' -As SingleValue 
$names.GetType().FullName  # System.Object[] = die erste Spalte aller Zeilen, ohne Warnung 
$empty = Invoke-DbaQuery -SqlInstance SRV1 -Query 'SELECT CAST(NULL AS nvarchar(10))' -As SingleValue 
$empty.GetType().FullName  # System.DBNull = wie bei DataRow, nicht $null wie bei PSObject 
$none = Invoke-DbaQuery -SqlInstance SRV1 -Query 'SELECT name FROM sys.databases WHERE 1 = 0' -As SingleValue 
$null -eq $none            # True = keine Zeile, kein Wert  

SingleValue liefert also die erste Spalte aller Zeilen der ersten Ergebnismenge: bei genau einer Zeile den Wert selbst, bei mehreren Zeilen ein Array, bei keiner Zeile $null und ein NULL kommt als System.DBNull an. Weitere Spalten und weitere Ergebnismengen verwirft der Parameter ohne jede Warnung. Der Name ist damit eine Zusage, die ihr gebt und keine, die der Befehl prüft: Wer SingleValue setzt, erklärt, dass die Abfrage genau einen Wert liefert. Typische Fälle sind COUNT(*), SELECT @@SERVERNAME oder das Auslesen einer einzelnen Konfiguration. 

Fazit

Der Parameter -As entscheidet, welche Struktur Invoke-DbaQuery liefert: DataRow für die Zeilen der ersten Ergebnismenge, DataTable und DataSet für alle Ergebnismengen, PSObject und PSObjectArray für PowerShell-Objekte ohne DBNull, SingleValue für den einen Wert, den ihr versprochen habt. Wer sich mit Get-Member und GetType() vergewissert, was in der Variablen steckt, erlebt mit keiner der sechs Varianten eine Überraschung.

Wer wissen möchte, was Invoke-DbaQuery dabei im Inneren tut – wie aus der Zeichenkette „SRV1“ eine Verbindung wird und warum sich mehrere Aufrufe dieselbe Verbindung teilen –, findet das in meinem Artikel „dbatools im Detail: Was passiert beim Aufruf von Invoke-DbaQuery?“. Fragen und Anregungen gerne als Kommentar zu diesem Beitrag.

Wenn ihr eure SQL-Server-Automatisierung mit dbatools aufbauen oder ausbauen möchtet, sprecht uns an. Wir helfen gerne.

Zur englischen Version

Seminarempfehlung